LINQ2SQL - More Questions on when a Cross join with where clause is emitted instead of Inner Join

This is a two part question and for education purposes rather than开发者_StackOverflow trying to find a solution to a problem.

I've seen this already and realize that it's very similar to my question

LINQ2SQL - Cross join emitted when I want inner join

But I am hoping for more information from you LINQ and SQL gurus as to why the cross join is created instead of inner join in LINQ2SQL. Additionally, 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? From what I understand, this means that the performance of the queries are the same.

I've created a small example that runs two LINQ expressions on my database that generates these two different SQL queries.

For those who don't want to bother, here's my example db diagram: http://dl.dropbox.com/u/13256/Screen%20shot%202011-03-16%20at%2011.41.56%20AM.png

Here are the two queries: Cross Join with Where Clause

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;

Inner Join

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

And here is the test program if you'd like to see for yourself.

Thanks for everyone's help.

Mustafa

They are the same thing, so it does not matter which LINQ2SQL emits.

An inner join is logically equivalent to a cross join with a where clause filter equivalent to the on of the inner join clause.

That's why Sql Server generates the same query plan.

To be clear, the inner join:

Select f1 
From T1 inner join T2 on T1.k = T2.k
where T1.f2 like 'X%'

Is the same as the cross join:

Select f1 
From T1 cross join T2 
where T1.k = T2.k
and T1.f2 like 'X%'

is the same as old-style SQL:

Select f1 
From T1, T2 
where T1.k = T2.k
and T1.f2 like 'X%'

Lets say you have a datacontext called MyDataContext.

using(MyDataContext db = new MyDataContext())
{
  var q = db.Items.Where(x=> x.Categories.Name == "myCategory").Select(x=> x);
}

This is a very simple example, but you didn't need to write out a join or a subquery in TSQL syntax. (I hate writing TSQL).