Simple Linq query has duplicated join against same table?

Question

(From an example in the new Entity Framework book by Julia Lerman.) I have a database with two tables, Contact and Address. The Contact table has a ContactID (int), as well as first name, last name, etc. The Address table has a ContactID, as well as city, state, zip, etc.

Here is a simple LINQ query:

var addressGraphQuery = from a in context.Addresses.Include("Contact")
                        orderby a.Contact.LastName, a.Contact.FirstName
                        select a;

From SQL Profiler, I see the following:

SELECT 
    [Extent1].[addressID] AS [addressID], 
    [Extent1].[City] AS [City], 
    [Extent1].[StateProvince] AS [StateProvince], 
    -- etc
    [Extent3].[ContactID] AS [ContactID1], 
    [Extent3].[FirstName] AS [FirstName], 
    [Extent3].[LastName] AS [LastName], 
    -- etc
FROM   [dbo].[Address] AS [Extent1]
INNER JOIN [dbo].[Contact] AS [Extent2] ON [Extent1].[ContactID] = [Extent2].[ContactID]
LEFT OUTER JOIN [dbo].[Contact] AS [Extent3] ON [Extent1].[ContactID] = [Extent3].[ContactID]
ORDER BY [Extent2].[LastName] ASC, [Extent3].[FirstName] ASC

It joins against the Contact table twice! Why? Is there an easy way to prevent this?

---

The mystery deepens. The joins disappear when I remove the orderby's. The joins do not disappear when I set context.ContextOptions.LazyLoadingEnabled = false.

There is also a similar question here:

Too Many Left Outer Joins in Entity Framework 4?

I'll see if there's a Higher Power I can ask...

Answer 1

Thank you for reporting this issue. Yes, it is not expected to see both joins. This is a bug that has been fixed in the current (not yet released) bits. In the current bits the same query produces:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[ContactId] AS [ContactId],
...
[Extent2].[Id] AS [Id1],
..
[Extent2].[FirstName] AS [FirstName],
[Extent2].[LastName] AS [LastName]
FROM  [dbo].[Address] AS [Extent1]
INNER JOIN [dbo].[Contact] AS [Extent2] ON [Extent1].[ContactId] = [Extent2].[Id] ORDER BY [Extent2].[LastName] ASC, [Extent2].[FirstName] ASC

I bit of playing shows that even on 4.0 this only happens when the relationship is 1:many, 0..1:many seems fine.

Thanks,

Kati Iceva
Entity Framework Developer
Microsoft

Answer 2

Hobbes, I don't know why you are getting both the inner and outer join. Include does weird stuff to queries. I've put your question to the team to see if this is expected (as bad as it is) and if there's a way to alleviate it. The query in question is just an example to demonstrate that you can use entity ref navigation properties in a query if you get in a bind.