A bookmark: EF queries for Parents who have 1:0..1 relationships to their children

Tuesday, September 30 2008 - Data Access

I saw this thread  early this summer and have spent hours looking for it since. Since i just came across it, I thought I would make a blog bookmark to it.

Multiplicity of 0..1 and SQL eager loading problem

Here's what the thread is about. When you query an entity that has a parent, for example an OrderDetail, that query will go over to Order so that EF can build the related EntityKeys. This is because when OrderDetail is materialized, it will need to populate OrderDetail.OrderReference.EntityKey.

If you query Orders and you don't eager load the details, there is no reason to touch OrderDetails in the query.

But what if  you have a 1: 0..1 relationship between the "parent" and "child" instead of 1:*?

The child property is no longer an EntityCollection, but an EntityReference. Therefore the EntityKey for that EntityReference needs to be constructed. So even if you don't include the children in the query, the store query will still have to seek out the child record to create it's EntityKey if it does exist.

I have such a relationship in my model.

 

onetozeroorone

 

If I query only for Contacts: 

From c In context.Contacts Where c.FirstName = "Robert"

You can see the left outer join in the SQL query that is used to build the CustomerReference.EntityKeys.

SELECT 1 AS [C1], [Extent1].[ContactID] AS [ContactID], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[Title] AS [Title], [Extent1].[AddDate] AS [AddDate], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[ContactID] AS [ContactID1] FROM[dbo].[Contact] AS [Extent1]LEFT OUTER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[ContactID] = [Extent2].[ContactID]WHERE [Extent1].[FirstName] = 'Robert'

The problem for Sean was that he was querying Contact but Contact has 1:0..1 relationships with 52 other entities. So he was getting 52 outer join  and he reported that he was getting 2800 extra columns. Imagine his surprise! I'm only getting the extra ContactID column. I wonder if he meant 2800 extra pieces of data? Either way, it's something to be aware of and in the thread Diego Vega suggests a way to avoid this which is to query with NoTracking so that the relationship info (CustomerReference.EntityKEy) is not needed.

The same query with MergeOption set to NoTracking is:

SELECT [Extent1].[ContactID] AS [ContactID], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[Title] AS [Title], [Extent1].[AddDate] AS [AddDate], [Extent1].[ModifiedDate] AS [ModifiedDate]FROM [dbo].[Contact] AS [Extent1]WHERE N'Robert' = [Extent1].[FirstName]

If you need to do tracking or relationships, then attach the entities after the fact.

Now if only I can remember what I was looking for in the forums before I happened upon this long lost thread!

Tagged as:

#1 emmanuel on 2.15.2009 at 3:34 PM

Hello Julie,That seems strange to me. Why would EF take an inner join with table Contact to discover ContactID while it already exists in Customer table as a foreign key? The query speaks for itself:SELECT [Extent1].[ContactID] AS [ContactID]...[Extent2].[ContactID] AS [ContactID1] ...LEFT OUTER JOIN [dbo].[Customers] AS [Extent2] ON [Extent1].[ContactID] = [Extent2].[ContactID]...Thus always, given the foreign key constraint, ContactID = ContactID1!In other words, EntityReference.EntityKey's represent foreign keys, and as such, they should be completely defined within the child (ie 'foreign') table.I would like your opinion on this. I will also post this question on the forum.Thank you.

#2 Julia Lerman on 2.16.2009 at 9:18 AM

Hi Emmanuel,As I understand it, EF doesn't literally rely on the FK when gathering the info from the data store. Remember that it is using the model to define the nav. property. Therefore, in the database, it uses the foreign key to find the related data but then gets the primary key of that related data. It's possible that the primary key of the related data is a composite key and therefore, the FK would not fully represent the related entity (e.g., the customer that is related to an order).julie

#3 emmanuel liossis on 2.18.2009 at 8:01 PM

Hello Julie,Thank you very much for your answer. It still puzzles me though. As far as i know, a FK integrally points to a PK by definition. A join would be required when such a FK constraint doesn't exist, so a check for PK existence is necessary. But EF could use the store model in EDM to figure out the FKs and PKs in order to avoid this expensive measure.Thank you again,-emmanuel

#4 Julia Lerman on 2.18.2009 at 8:10 PM

I'm hoping that someone on the team will be able to provide a more thorough explanation on the forum thread.

Leave a Comment