linq-to-sql joins with multiple from clauses syntax vs. traditional join syntax

Question

What the difference between writing a join using 2 from clauses and a where like this:

var SomeQuery = from a in MyDC.Table1
                from b in MyDC.Table2
                where a.SomeCol1 == SomeParameter && a.SomeCol2 === b.SomeCol1

and writing a join using the join operator.

This is for a join on 2 tables but of course, sometimes, we need to join even more tables and we need to combine other from clauses with where if we choose the syntax above.

I know both syntax queries return the same data but I was wondering if there's a performance difference, or another kind of difference, that would conclusively favor one syntax over the other.

Thanks for your suggestions.

score 23 · Accepted Answer · edited May 23 '17 at 11:45

This question is actually answered pretty good in these two.

INNER JOIN ON vs WHERE clause

INNER JOIN vs multiple table names in "FROM"

I've included two examples on how three different LINQ expressions will be translated into SQL.

Implicit join:

from prod in Articles
from kat in MainGroups
where kat.MainGroupNo == prod.MainGroupNo
select new { kat.Name, prod.ArticleNo }

Will be translated into

SELECT [t1].[Name], [t0].[ArticleNo]
FROM [dbo].[Article] AS [t0], [dbo].[MainGroup] AS [t1]
WHERE [t1].[MainGroupNo] = [t0].[MainGroupNo]

Inner join:

from prod in Articles
join kat in MainGroups on prod.MainGroupNo equals kat.MainGroupNo
select new { kat.Name, prod.ArticleNo }

Will be translated into

SELECT [t1].[Name], [t0].[ArticleNo]
FROM [dbo].[Article] AS [t0]
INNER JOIN [dbo].[MainGroup] AS [t1] ON [t0].[MainGroupNo] = [t1].[MainGroupNo]

Left outer join:

from prod in Articles
join g1 in MainGroups on prod.MainGroupNo equals g1.MainGroupNo into prodGroup
from kat in prodGroup.DefaultIfEmpty()
select new { kat.Name, prod.ArticleNo }

Will be translated into

SELECT [t1].[Name] AS [Name], [t0].[ArticleNo]
FROM [dbo].[Article] AS [t0]
LEFT OUTER JOIN [dbo].[MainGroup] AS [t1] ON [t0].[MainGroupNo] = [t1].[MainGroupNo]

If you want to test how your expressions will be translated into SQL, I recommend that you try LINQPad. It's an awesome tool for figuring out this kind of stuff.

So what's better? 2 or more from clauses or 2 or more join clauses? — frenchie, Jul 31 '12 at 23:35
According to http://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line the join clause seems preferable. — Ole Melhus, Jul 31 '12 at 23:40

score -3 · Answer 2 · answered Dec 18 '13 at 21:25

-3

var result = from a in DB.classA
from b in DB.classB
where a.id.Equals(b.id)
select new{a.b};

answered Dec 18 '13 at 21:25

gilix

545
10
14

This does not answer the question, nor does it provide any explanation. – Dave Oct 21 '19 at 08:33

linq-to-sql joins with multiple from clauses syntax vs. traditional join syntax

2 Answers2