I've seen multiple from clauses in a LINQ query a few times now, but have not figured out how that corresponds to a TSQL statement.
var r = from p in products
from d in departments
from c in something
select p;
What does that do?
Does that translate to SQL as SELECT * FROM products, departments, something?
Also, the select in this case is supposed to always be a SelectMany. how do I know when a select actually is a SelectMany?
That will translate into code conceptually like this:
var r = products.SelectMany(p => departments, (p, d) = new { p, d })
.SelectMany(z => something, (z, c) => new { z, c })
.Select(zz => zz.z.p);
Basically each from after the first one adds an extra call to SelectMany. Actually, in this case the compiler will notice that after the last from clause there's just a select, and it will actually translate to this:
var r = products.SelectMany(p => departments, (p, d) = new { p, d })
.SelectMany(z => something, (z, c) => z.p);
... but I tend to think of the "logical" form first.
Note that in this case you haven't used the earlier range variables within the from clauses, but you can:
var query = from person in people
from friend in person.Friends
select person.Name + " is friends with " + friend.Name;
How that corresponds to a TSQL statement?
It depends on the way how you use your from clauses. If you use:
var query = from d in context.Departments
from e in context.Employees
select d;
Your query will be CROSS JOIN because there is no relation between product and department used. So something like:
SELECT d.* FROM Departments AS d
CROSS JOIN Employees AS e
Obviously you should avoid cross joins. If you instead use navigation property in second from:
var query = from d in context.Departments
from e in d.Employees
select d;
you will use either INNER or LEFT JOIN (EF will decide which one to use based on your multiplicity mapping in the relation). So something like:
SELECT d.* FROM Departments AS d
LEFT OUTER JOIN Employees AS e ON d.Id = e.DepartmentId
In this case it doesn't make any sense to use select (it's not semantically correct, you must use SelectMany)
