What is the difference between using union in Linq and ANSI join syntax for old style?

Question

This question follows from the question I asked yesterday about why using a connection request on my entities created an awfully complex SQL. It seemed that the execution of the request was as follows:

var query = from ev in genesisContext.Events join pe in genesisContext.People_Event_Link on ev equals pe.Event where pe.P_ID == key select ev; 

Terrible SQL was produced, which took 18 seconds to work in the database, while combining objects using the where clause (like the syntax before ANSI SQL) took less than a second to run and produced the same result

  var query = from pe in genesisContext.People_Event_Link from ev in genesisContext.Events where pe.P_ID == key && pe.Event == ev select ev; 

I googled all over, but still don't understand why the second one produces different SQL for the first. Can someone please explain the difference to me? When to use the join keyword

---

This is the SQL that was created when I used Join in my query and took 18 seconds to run:

 SELECT 1 AS [C1], [Extent1].[E_ID] AS [E_ID], [Extent1].[E_START_DATE] AS [E_START_DATE], [Extent1].[E_END_DATE] AS [E_END_DATE], [Extent1].[E_COMMENTS] AS [E_COMMENTS], [Extent1].[E_DATE_ADDED] AS [E_DATE_ADDED], [Extent1].[E_RECORDED_BY] AS [E_RECORDED_BY], [Extent1].[E_DATE_UPDATED] AS [E_DATE_UPDATED], [Extent1].[E_UPDATED_BY] AS [E_UPDATED_BY], [Extent1].[ET_ID] AS [ET_ID], [Extent1].[L_ID] AS [L_ID] FROM [dbo].[Events] AS [Extent1] INNER JOIN [dbo].[People_Event_Link] AS [Extent2] ON EXISTS (SELECT 1 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT [Extent3].[E_ID] AS [E_ID] FROM [dbo].[Events] AS [Extent3] WHERE [Extent2].[E_ID] = [Extent3].[E_ID] ) AS [Project1] ON 1 = 1 LEFT OUTER JOIN (SELECT [Extent4].[E_ID] AS [E_ID] FROM [dbo].[Events] AS [Extent4] WHERE [Extent2].[E_ID] = [Extent4].[E_ID] ) AS [Project2] ON 1 = 1 WHERE ([Extent1].[E_ID] = [Project1].[E_ID]) OR (([Extent1].[E_ID] IS NULL) AND ([Project2].[E_ID] IS NULL)) ) WHERE [Extent2].[P_ID] = 291 

---

This is SQL that was created using the ANSI style syntax (and is pretty close to what I would write if I wrote SQL myself):

 SELECT * FROM Events AS E INNER JOIN People_Event_Link AS PE ON E.E_ID=PE.E_ID INNER JOIN PEOPLE AS P ON P.P_ID=PE.P_ID WHERE P.P_ID = 291 

Answer 1

None of the above queries are completely "correct." In EF, it is usually correct to use relationship properties instead of one of the above. For example, if you have a Person object with one to many relation to PhoneNumbers in the Person.PhoneNumbers property, you can write:

 var q = from p in Context.Person from pn in p.PhoneNumbers select pn; 

EF will build a connection for you.

In terms of the above question, the reason the generated SQL is different is because the expression trees are different, although they give equivalent results. Expression trees map to SQL, and you, of course, know that you can write different SQLs that produce the same results, but with different performance. The mapping is designed to create decent SQL when you write a “regular” EF query.

But the mapping is not so smart as to accept a very unconventional request and optimize it. In the first query, you indicate that the objects should be equivalent. In the second, you indicate that the ID property should be equivalent. My sample request above says "just get the details for this entry." EF is designed to work with the way I show, mostly, but also does a good job of scalar equivalence.