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.

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!