LINQ to Entities joining on instance rather than id generates nasty SQL

Question

Can anyone explain why join by entity rather than id generates some really ugly sql when actually conceptually its doing what you'd think was the same thing? e.g.

By id

from companyDirector in CompanyDirectors
join contactAddress in ContactAddresses
  on companyDirector.ContactAddress.Id equals contactAddress.Id
select new {companyDirector, contactAddress}

Generates

FROM  [COMPANY] AS [Extent1]
    INNER JOIN [ADDRESS] AS [Extent2] ON [Extent1].[CONTACT_ADDRESS_ID] = [Extent2].[CONTACT_ADDRESS_ID]

By instance

from companyDirector in CompanyDirectors
join contactAddress in ContactAddresses
  on companyDirector.ContactAddress equals contactAddress
select new {companyDirector, contactAddress}

generates

FROM  [COMPANY] AS [Extent1]
INNER JOIN [ADDRESS] AS [Extent2] ON  EXISTS (SELECT 
    1 AS [C1]
    FROM    ( SELECT 1 AS X ) AS [SingleRowTable1]
    LEFT OUTER JOIN  (SELECT 
        [Extent3].[CONTACT_ADDRESS_ID] AS [CONTACT_ADDRESS_ID]
        FROM [ADDRESS] AS [Extent3]
        WHERE [Extent1].[CONTACT_ADDRESS_ID] = [Extent3].[CONTACT_ADDRESS_ID] ) AS [Project1] ON 1 = 1
    LEFT OUTER JOIN  (SELECT 
        [Extent4].[CONTACT_ADDRESS_ID] AS [CONTACT_ADDRESS_ID]
        FROM [ADDRESS] AS [Extent4]
        WHERE [Extent1].[CONTACT_ADDRESS_ID] = [Extent4].[CONTACT_ADDRESS_ID] ) AS [Project2] ON 1 = 1
    WHERE [Project1].[CONTACT_ADDRESS_ID] = [Extent2].[CONTACT_ADDRESS_ID]
)

That looks pretty inefficient to me, forcing you into the id route. Why is it doing the left join twice, never mind once??

Answer 1

I can't say what is in the minds or the code of the ADO.NET team. That said, I see two possible issues:

1. Possibly, the Id field in the underlying table in ContractAddresses, or possibly just in the entity model, could not be defined as a primary key. I somewhat doubt this is the problem, but it's worth double-checking.
2. The equals keyword may not have a good way actually to compare equality between the two objects in the join. In a quick web search, I did not find exactly what the equals uses for comparison, but this MSDN how-to leads me to believe that the Equals and GetHashCode methods are involved (even if composite keys are not involved). If you are just using the default object.Equals inherited method, the Linq provider has to figure out the reference equality somehow, which I imagine could lead to some strange results.

I do like the solution by @Craig Stuntz in his comment, though. Also, you might want to get an execution plan for the longer query to see if it's really as bad as it looks; the query optimizer might do a better job than the code would indicate.