|
I'm using TPH inheritance and while the results I'm getting are as expected, in some cases the query sent to SQL Server isn't filtering based on the entity I specified in the OfType method. I assume the filtering is happening on the client side which, of course, isn't perferred since it is returning more records than necessary.
Query 1 & 3 show examples of OfType working as expected whereas Query 2 demonstrates the problem.
Query #1 - SQL generated as expected
HealthcareOrganization hco = _manager.Organizations.OfType<HealthcareOrganization>().Take(1).First();
SELECT [Limit1].[C1] AS [C1], [Limit1].[OrganizationID] AS [OrganizationID], [Limit1].[FileAs] AS [FileAs], [Limit1].[FullName] AS [FullName], [Limit1].[CreatedOn] AS [CreatedOn], [Limit1].[ModifiedOn] AS [ModifiedOn], [Limit1].[HealthcareGroupID] AS [HealthcareGroupID] FROM ( SELECT TOP (1) [Extent1].[OrganizationID] AS [OrganizationID], [Extent1].[FileAs] AS [FileAs], [Extent1].[FullName] AS [FullName], [Extent1].[HealthcareGroupID] AS [HealthcareGroupID], [Extent1].[CreatedOn] AS [CreatedOn], [Extent1].[ModifiedOn] AS [ModifiedOn], '0X0X' AS [C1] FROM [dbo].[Organization] AS [Extent1] WHERE [Extent1].[OrganizationDiscriminator] = 'HO' ) AS [Limit1]
This worked as expected because the OrganizationDiscriminator HO was used in the query.
Query #2 - However, when I want to query a specific Address Type (PostalAddress, ElectronicAddress or PhoneNumber), the query sent to SQL Server doesn't seem to take into account the entity specified in the OfType<>() method.
PostalAddress pa = hco.Addresses.OfType<PostalAddress>().Take(1).First();
SELECT [Extent2].[AddressDiscriminator] AS [AddressDiscriminator], [Extent2].[AddressID] AS [AddressID], [Extent2].[DestinationType] AS [DestinationType], [Extent2].[Notes] AS [Notes], [Extent2].[PriorityOrder] AS [PriorityOrder], [Extent2].[CreatedOn] AS [CreatedOn], [Extent2].[ModifiedOn] AS [ModifiedOn], [Extent2].[OrganizationID] AS [OrganizationID], [Extent2].[Line1] AS [Line1], [Extent2].[Line2] AS [Line2], [Extent2].[Line3] AS [Line3], [Extent2].[City] AS [City], [Extent2].[State] AS [State], [Extent2].[ZipCode] AS [ZipCode], [Extent2].[Protocol] AS [Protocol], [Extent2].[Target] AS [Target], [Extent2].[Number] AS [Number], [Extent2].[Extension] AS [Extension] FROM [dbo].[Organization] AS [Extent1] INNER JOIN [dbo].[Address] AS [Extent2] ON ([Extent2].[AddressDiscriminator] IN ('PN','PA','EA')) AND ([Extent1].[OrganizationID] = [Extent2].[OrganizationID]) WHERE ([Extent1].[OrganizationDiscriminator] = 'HO') AND ('CO.NSUB' = [Extent1].[OrganizationID])
I would have expected it to use the AddressDiscriminator PA to filter the query, but instead it specified all 3 discriminators in the IN clause. This causes SQL Server to return all Addresses for the specified HealthcareOrganization. Of course, filtering out the PhoneNumber (PN) and ElectronicAddresses (EA) are happening somewhere - presumably on the client-side, but this isn't the desired behavior. I'd prefer SQL Server only fetch the type I specified.
Query #3 - If I go after the Addresses directly from the EntityManager, instead of through the hco object/HealthcareOrganization entity, the query sent to SQL Server works as expected.
PostalAddress pa2 = _manager.Addresses.OfType<PostalAddress>().Take(1).First();
SELECT [Limit1].[C1] AS [C1], [Limit1].[AddressID] AS [AddressID], [Limit1].[DestinationType] AS [DestinationType], [Limit1].[Notes] AS [Notes], [Limit1].[PriorityOrder] AS [PriorityOrder], [Limit1].[CreatedOn] AS [CreatedOn], [Limit1].[ModifiedOn] AS [ModifiedOn], [Limit1].[OrganizationID] AS [OrganizationID], [Limit1].[Line1] AS [Line1], [Limit1].[Line2] AS [Line2], [Limit1].[Line3] AS [Line3], [Limit1].[City] AS [City], [Limit1].[State] AS [State], [Limit1].[ZipCode] AS [ZipCode] FROM ( SELECT TOP (1) [Extent1].[AddressID] AS [AddressID], [Extent1].[DestinationType] AS [DestinationType], [Extent1].[OrganizationID] AS [OrganizationID], [Extent1].[Line1] AS [Line1], [Extent1].[Line2] AS [Line2], [Extent1].[Line3] AS [Line3], [Extent1].[City] AS [City], [Extent1].[State] AS [State], [Extent1].[ZipCode] AS [ZipCode], [Extent1].[Notes] AS [Notes], [Extent1].[PriorityOrder] AS [PriorityOrder], [Extent1].[CreatedOn] AS [CreatedOn], [Extent1].[ModifiedOn] AS [ModifiedOn], '0X0X' AS [C1] FROM [dbo].[Address] AS [Extent1] WHERE [Extent1].[AddressDiscriminator] = 'PA' ) AS [Limit1]
This worked as expected because the AddressDiscriminator PA was used in the query.
The queries listed above where captured from the DebugLog.xml.
Please help. I'd like to continue using TPH, but I'm stumped about what I'm doing wrong.
Thanks,
Greg
Details on my EDM setup...
I have an Organization entity that is abstract and is the base entity for an AuxiliaryOrganization, ServiceOrganization and HealthcareOrganization entities.
Organization.OrganiationID
Organization.Discriminator
Organization.HealthcareGroupID (mapped to HealthcareOrganization)
Organization.FileAs
Organization.Notes
I also have an Address entity that is abstract and is the base entity for PostalAddress, PhoneNumber and ElectronicAddress entities.
Address.AddressID
Address.OrganizationID
Address.Discriminator (PA for PostalAddress, PN for PhoneNumber and EA for ElectronicAddress)
Address.Line1 (mapped to PostalAddress)
Address.Line2 (mapped to PostalAddress)
Address.ZipCode (mapped to PostalAddress)
Address.Number (mapped to PhoneNumber)
Address.Extension (mapped to PhoneNumber)
Address.Protocol (mapped to ElectronicAddress)
Address.Target (mapped to ElectronicAddress)
Address.Notes
|