title: LINQ2SQL - More Questions on when a Cross join with where clause is emitted instead of Inner Join
body: <p>This is a two part question and for education purposes rather than trying to find a solution to a problem.</p>
  
  <p>I've seen this already and realize that it's very similar to my question</p>
  
  <p><a href="https://stackoverflow.com/questions/1363478/linq2sql-cross-join-emitted-when-i-want-inner-join">LINQ2SQL - Cross join emitted when I want inner join</a></p>
  
  <p>But I am hoping for more information from you LINQ and SQL gurus as to <strong>why the cross join is created instead of inner join in LINQ2SQL</strong>. Additionally, <strong>can someone explain how SQL Server decides on the execution plan (or link to further information) since both of these queries generate the same plan</strong>? From what I understand, this means that the performance of the queries are the same.</p>
  
  <p>I've created a small example that runs two LINQ expressions on my database that generates these two different SQL queries.</p>
  
  <p>For those who don't want to bother, here's my example db diagram: 
  <a href="http://dl.dropbox.com/u/13256/Screen%20shot%202011-03-16%20at%2011.41.56%20AM.png" rel="nofollow noreferrer">http://dl.dropbox.com/u/13256/Screen%20shot%202011-03-16%20at%2011.41.56%20AM.png</a></p>
  
  <p>Here are the two queries: 
  <strong>Cross Join with Where Clause</strong></p>
  
  <pre><code>var q = from item in context.Items
  join i_mem in context.Memberships on new { item_id = item.ID, user_id = 
  current_user_id.Value } equals new { item_id = i_mem.RelatedItemID, user_id = 
  i_mem.RelatedUserID } into sq_i_m
  from im in sq_i_m.DefaultIfEmpty()
  join i_cat in context.Categories on item.RelatedCategoryID equals i_cat.ID 
  into sq_i_cat
  from proj in sq_i_cat
  select item;
  </code></pre>
  
  <p><strong>Inner Join</strong></p>
  
  <pre><code>from item in context.Items
  join i_mem in context.Memberships on
  new { item_id = item.ID, user_id = current_user_id.Value }
  equals
  new { item_id = i_mem.RelatedItemID, user_id = i_mem.RelatedUserID }
  into sq_i_m
  from im in sq_i_m.DefaultIfEmpty()
  join i_cat in context.Categories on item.RelatedCategoryID equals i_cat.ID
  select item
  </code></pre>
  
  <p>And <a href="http://dl.dropbox.com/u/13256/linqTest.zip" rel="nofollow noreferrer">here</a> is the test program if you'd like to see for yourself.</p>
  
  <p>Thanks for everyone's help.</p>
  
  <p>Mustafa</p>