7

(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...

Community
  • 1
  • 1
Hobbes
  • 136
  • 1
  • 4
  • I've merged all three accounts you created. Please register, it'll save me (and other mods) some time. Also, for future reference, comments are for commenting and answers are for answering. Check the [faq](http://stackoverflow.com/faq) and welcome to SO. –  Jan 01 '11 at 22:41

3 Answers3

8

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

Chris Pratt
  • 207,690
  • 31
  • 326
  • 382
Kati Iceva
  • 81
  • 1
  • I've just tried this in the latest version that appears in NuGet and I'm still seeing the query as described by Hobbes. Is there any update on this? – James Crowley Sep 20 '11 at 16:36
  • 1
    Actually, this has been fixed in the latest June CTP - http://blogs.msdn.com/b/adonet/archive/2011/06/30/announcing-the-microsoft-entity-framework-june-2011-ctp.aspx - (but not yet in the NuGet releases) – James Crowley Sep 20 '11 at 22:07
  • 1
    Is this fixed in the 4.3.1 release or only fixed in the EF5? – Seph Apr 12 '12 at 05:23
  • I have the same issue with 1:1 or 1:0..1 joins. Initially it seem to have no impact but eventually the data grew to cause issues. By adding a silly `.Where(x => x.table1 != null && x.table2 != null)` condition it caused the dup joins to get inserted into a subquery that SQL was able to better deal with and performance returned. Very odd. – Donald Byrd Jun 10 '13 at 23:00
  • @Kati lceva , This bug is still present in EF 6.1.3 – ahmad valipour Apr 18 '17 at 11:31
  • Also present in 6.2.0 – theLaw Jan 10 '19 at 15:39
1

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.

Julie Lerman
  • 4,517
  • 2
  • 20
  • 20
0

I tend to use the lambda syntax so I could be wrong but I think you'll find you want your second part of the order by clause to in fact be a "then by".

E.g.

var query = context.Addresses.Include("Contact")
                   .OrderBy(a => a.Contact.LastName)
                   .ThenBy(a => a.Contact.FirstName)
Reddog
  • 14,009
  • 3
  • 47
  • 61
  • Hmmmmm, actually, I could be wrong! See: http://stackoverflow.com/questions/298725/linq-multiple-order-by – Reddog Dec 18 '10 at 04:25
  • Yup, you're wrong =) ThenBy() is supposed to be equivalent to a second sort order in Linq's orderby. – Hobbes Dec 18 '10 at 04:40
  • No, i think @Reddog is right. If you look at the SQL, it has `ORDER BY [Extent2].[LastName] ASC, [Extent3].[FirstName] ASC`. Ordering by two different queries (Extent2, Extent3), when only one is required. It's the order by. I think this answer is right. (i think) – RPM1984 Dec 18 '10 at 04:47
  • Check out the "ThenBy - Simple" example at http://msdn.microsoft.com/en-us/vcsharp/aa336756. It uses orderby with two expressions, separated by a comma. This syntax is equivalent to OrderBy().ThenBy(). – Hobbes Dec 18 '10 at 04:55
  • 1
    For completeness sake, I tried OrderBy().ThenBy(), and there was still a duplicated join =( – Hobbes Dec 18 '10 at 05:02
  • fair enough. i've heard problems with orderby and include. if you just do the include without the orderby's, does it product 1 query? or likewise, just the orderby without the include. i think what is happening is a.Contact (in the orderby) is lazy loading the join, and the include is doing the LOJ. since your not using Take (aka TOP), the orderby is on the "results" anyway - so you may as well materialize your query first (.ToList), and THEN do the order by. – RPM1984 Dec 18 '10 at 06:21