Join Order Affects SQL Output for Linq-to-Entity Self-Referencing Group (Left) Joins RRS feed

  • Question

  • In EF 4.0+, LEFT JOIN syntax is a little different and presents a crazy quirk:

    var query = from c1 in db.Category 
            join c2 in db.Category on c1.CategoryID equals c2.ParentCategoryID  
            into ChildCategory 
            from cc in ChildCategory.DefaultIfEmpty() 
            select new CategoryObject  
                CategoryID = c1.CategoryID,  
                ChildName = cc.CategoryName 

    If you capture the execution of this query in SQL Server Profiler, you will see that it does indeed perform a LEFT OUTER JOIN.  HOWEVER, if you have multiple LEFT JOIN ("Group Join") clauses in your Linq-to-Entity query, I have found that the self-join clause MAY actually execute as in INNER JOIN - EVEN IF THE ABOVE SYNTAX IS USED!

    The resolution to that?  As crazy and, according to MS, wrong as it sounds, I resolved this by changing the order of the join clauses.  If the self-referencing LEFT JOIN clause was the 1st Linq Group Join, SQL Profiler reported an INNER JOIN.  If the self-referencing LEFT JOIN clause, with the EXACT same syntax, was the LAST Linq Group Join, SQL Profiler reported an LEFT JOIN.

    • Moved by Mike FengModerator Thursday, August 2, 2012 8:59 AM EF (From:.NET Base Class Library)
    Wednesday, August 1, 2012 4:16 PM

All replies

  • Hi Ffud,

    Welcome to the MSDN Forum.

    I have moved this thread to a dedicated forum for better support.

    Best regards,

    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 2, 2012 9:02 AM
  • what does that mean? How can I see the results of this discussion? A link or name of the forum it was moved to would be nice.
    Thursday, August 2, 2012 2:16 PM
  • Hi,

    Are you able to provide me with some SQL to create the tables and the two queries that are showing this behaviour?

    That way I can be sure that I am attempting to reproduce it correctly.

    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Thursday, August 2, 2012 8:54 PM