New Posts New Posts RSS Feed: OfType being ignored in some cases
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

OfType being ignored in some cases
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Topic: OfType being ignored in some cases
    Posted: 19-Nov-2010 at 5:12pm
The second query is a relational query for the HealthCareOrganization Addresses navigation property.  DevForce will generally load a navigation property lazily upon first use, and load it in its entirety.  So you're exactly right, all hco.Addresses are being retrieved from the datasource, and the OfType (and Take) are then executed against cache.  This is by design.
 
If you do need filtered relational data, you can write simple properties which execute a standard EntityQuery and will filter on the database.  Something like this:
 
public partial class HealthCareOrganization {
  public PostalAddress PostalAddress {
     get {
        return this.EntityManager.GetQuery<Address>().OfType<PostalAddress>().FirstOrDefault(a=> a.Id == ???);
     }
  }
}
 
Back to Top
gshelton View Drop Down
Newbie
Newbie
Avatar

Joined: 17-Sep-2010
Location: Nashville, TN
Posts: 4 		Post Options Post Options   Quote gshelton Quote  Post ReplyReply Direct Link To This Post Posted: 19-Nov-2010 at 12:52pm

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
 
 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down