SQL to Linq - Left, Right and Inner Joins

[shapper]

Hello,

I am used to SQL but I am starting to use LINQ.

How can I create Left, Right and Inner joins in LINQ? How to
distinguish the different joins?

Here is a great SQL example:
http://www.codinghorror.com/blog/archives/000976.html

Thanks,
Miguel

[Jon Skeet [C# MVP]]

Frans Bouma [C# MVP] <pe******************@xs4all.nlwrote:

Michel Walsh wrote:

If used alone, yes, but with a WHERE clause, MS SQL Server optimizer should
recognize an (implicit) inner join and thus, limit the effective amount of
data transferred through the wire.

That's not what makes it inefficient. The cross-join execution path is
often less ideal as the cross-join action by itself is first handled,
and THEN the where clause is handled to filter out rows you don't want.

I'd certainly hope that the query optimiser would do the job there
though. For instance, if the where clause is on an indexed column I'd
be *hugely* surprised if the inner join were fully evaluated by SQL
server before looking at the index.

It's not like LINQ passes the join in one SQL statement and then
applies the where clause separately: the query optimiser has the full
query to work with.

--
Jon Skeet - <sk***@pobox.com>
Web site: http://www.pobox.com/~skeet
Blog: http://www.msmvps.com/jon_skeet
C# in Depth: http://csharpindepth.com

[Andrus]

To get the SQL LEFT outer join, we can expand the LINQ group join, but for

"a" values having no match, we introduce nulls on the unpreserved side,
like this:

from a in b
join
c in d
on
a.field equals c.otherField
into newSet
from x in newSet.DefaultIfEmpty()

newSet can be any name you want which do not collide with keyword or other
variables.

That is a little bit as saying: if a row in b has disappeared,
re-introduce it supplying null (default) for the unpreserved side, and
now, the 'alias' to use to refer to the unpreserved side is x.

This looks to complicated. A better and simpler way for existing realtions
is to use chained properties

Northwind db = CreateDB();
var query = from e in db.Employees
select new {
Name = e.FirstName,
ReportsTo = e.ParentEmployee.FirstName
};
var list = query.ToList();

which generates left join clause automatically.

Andrus.

[Frans Bouma [C# MVP]]

Andrus wrote:

>To get the SQL LEFT outer join, we can expand the LINQ group join, but
for "a" values having no match, we introduce nulls on the unpreserved
side, like this:

from a in b
join
c in d
on
a.field equals c.otherField
into newSet
from x in newSet.DefaultIfEmpty()

newSet can be any name you want which do not collide with keyword or
other variables.

That is a little bit as saying: if a row in b has disappeared,
re-introduce it supplying null (default) for the unpreserved side, and
now, the 'alias' to use to refer to the unpreserved side is x.

This looks to complicated. A better and simpler way for existing
realtions is to use chained properties

Northwind db = CreateDB();
var query = from e in db.Employees
select new {
Name = e.FirstName,
ReportsTo = e.ParentEmployee.FirstName
};
var list = query.ToList();

which generates left join clause automatically.

Only with nullable FKs

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Michel Walsh]

In MS SQL Server, the two queries:
-----------------
SELECT au_fname, au_ord
FROM authors, titleauthor
WHERE authors.au_id=titleauthor.au_id
------------------

and

---------------------
SELECT au_fname, au_ord
FROM authors INNER JOIN titleauthor
ON authors.au_id=titleauthor.au_id
-----------------------

produce, both, the SAME query plan. Remember that SQL is not about HOW TO
get the result, but about to DESCRIBE the result you want. The HOW TO, the
imperative part, is ideally left to the database.
So, yes, if the query plan would use a cross join to solve the problem, that
would be inefficient, but as writing the description of what I want, there
is NO PROBLEM using a CROSS JOIN to do it, FOLLOWED by a WHERE clause.
When I said that SQL joins are probably easier to understand by STARTING
with cross joins, I meant as a mean to understand them, not HOW the database
should (would) do it. In fact, I am far from the first using that approach,
indeed, I 'borrow' the approach from Joe Celko. (The outer joins are then
explained by the re-injection of each row, from the preserved side, which
would have disappear.)
LINQ can thus allow easy non-equi-join if you start by describing a cross
join, and the use the WHERE clause to describe the condition that you would
normally put in the ON clause of the INNER join. The problem is for OUTER
NON EQUI join, only. In some cases, you can still make use of sub-queries.

Vanderghast, Access MVP

"Frans Bouma [C# MVP]" <pe******************@xs4all.nlwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

Michel Walsh wrote:

>If used alone, yes, but with a WHERE clause, MS SQL Server optimizer
should recognize an (implicit) inner join and thus, limit the effective
amount of data transferred through the wire.

That's not what makes it inefficient. The cross-join execution path is
often less ideal as the cross-join action by itself is first handled, and
THEN the where clause is handled to filter out rows you don't want. As a
cross-join can spawn millions of tuples, which are often not needed, the
where clause will run slower than an ON clause in an inner-join set
operation.

It's not the data that's sent over the wire, it's the query execution
itself which makes it less ideal. That's also why I find it a big design
flaw in linq that the only operator in the linq's join clause is an
'equals' operator, which makes the production of left/right joins awkward,
yet these operations are often required. The alternative is a nested from
but that leads to lesser ideal queries than a left-join + accompanying on
clause.

FB

>>

[Michel Walsh]

"Frans Bouma [C# MVP]" <pe******************@xs4all.nlwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
(...)

Btw, The cross joins in MS code often rely on a T-SQL specific statement
'CROSS APPLY'

(...)
Hum, are you sure? CROSS APPLY is allowing an ON clause based on the
evaluation of a 'function' returning one or multiple rows (the rows are
thus supplied by the function rather than by a db.table). Furthermore, only
with MS SQL Server 2005. It did not exist before 2005, while CROSS JOIN
existed long time before. So CROSS JOIN can hardly be said to be based on
CROSS APPLY.
You were probably having something else in mind.

Vanderghast, Access MVP

[Michel Walsh]

There is only one table! There is no join, so no outer join either :-)

You probably pasted the wrong code snippet.
Vanderghast, Access MVP
"Andrus" <ko********@hot.eewrote in message
news:OX**************@TK2MSFTNGP05.phx.gbl...

>To get the SQL LEFT outer join, we can expand the LINQ group join, but
for "a" values having no match, we introduce nulls on the unpreserved
side, like this:

from a in b
join
c in d
on
a.field equals c.otherField
into newSet
from x in newSet.DefaultIfEmpty()

newSet can be any name you want which do not collide with keyword or
other variables.

That is a little bit as saying: if a row in b has disappeared,
re-introduce it supplying null (default) for the unpreserved side, and
now, the 'alias' to use to refer to the unpreserved side is x.

This looks to complicated. A better and simpler way for existing realtions
is to use chained properties

Northwind db = CreateDB();
var query = from e in db.Employees
select new {
Name = e.FirstName,
ReportsTo = e.ParentEmployee.FirstName
};
var list = query.ToList();

which generates left join clause automatically.

Andrus.

[Andrus]

There are two tables !

e.ParentEmployee !

There is join, left outer join in case of nullable fk as Frans responds.

I pasted correct snippet.

Andrus.

There is only one table! There is no join, so no outer join either :-)

You probably pasted the wrong code snippet.

[Michel Walsh]

I only see one FROM clause, one table, aliased as e:
var query = from e in db.Employees
select new {
Name = e.FirstName,
ReportsTo = e.ParentEmployee.FirstName}

and e.ParentEmployee.FirstName sounds like part of an undefined relation (at
least, undefined in the code snippet). Surely you will agree that a join CAN
occur over tables which have no predefined relation between them.

Vanderghast, Access MVP

"Andrus" <ko********@hot.eewrote in message
news:OB**************@TK2MSFTNGP03.phx.gbl...

There are two tables !

e.ParentEmployee !

There is join, left outer join in case of nullable fk as Frans responds.

I pasted correct snippet.

Andrus.

>There is only one table! There is no join, so no outer join either :-)

You probably pasted the wrong code snippet.

[Michel Walsh]

Ah, just saw Frans Bouma's post.
Vanderghast, Access MVP