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.
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
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' =
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!