Re: SQL to Linq - Left, Right and Inner Joins

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.

Vanderghast, Access MVP



By the way, a natural inner join between {a, a, b} and { a, a, c} returns
4 rows, not 5. My mistake.



"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xxxxxxxxx> wrote in message
news:uJjrL1a3IHA.1808@xxxxxxxxxxxxxxxxxxxxxxx
Keep in mind that:
var q = from a in ctx.A
from b in ctx.B
...

results in a cross-join which could be very inefficient.

FB

Michel Walsh wrote:
In fact, you don't do SQL-joins at all, not directly. What you do is
using a for-each based representation of the universe which will be
TRANSLATED to an expression tree which will be TRANSLATED to an SQL
statement. At least, for me, that helps to see the process as being a
double translation, and to STOP thinking in SQL sets, while using LINQ,
but to then think in terms of for-each sequences.

Side note about the supplied URL: an SQL join is not represented
correctly by a Venn diagram since, like an electronic Op-Amp, an SQL join
can selectively 'amplify' the number of rows from the original sets. If
you make an inner (equi) join between { a, a, b} and { a, a, c}, you end
up with 5 rows, something the Venn diagram misses. The Venn diagrams are
only applicable if there is no 'duplicated' elements involved in common
to both sets ( { a, a, b} and { c, d, d} are ok for a Venn diagram,
since no dup appears in common to both sets).

So, for the sequence thing:
-------------
LINQ: from a in b

is to be compare to

foreach( a in b)

and will generate SQL like "FROM b AS a"
------------------

The sequence:

from a in b
from c in d

is a kind of imbricated:

foreach( a in b)
{
foreach ( c in d)

}

and could generate the SQL part:

FROM b AS a, d AS c
---------------------------



While SQL joins explanation (can) start from a cross join, LINQ joins
start form a group join.

from a in b
join
c in d
on
a.field equals c.otherField
into newSet

The second 'table' is introduced with the keyword join rather than with
the keyword from, as previously, (probably to allow) the on keyword
introduces the relation, always an equality (equals is a keyword).

There is no direct SQL equivalence for that. Also note that "a" variable
must be at the left of the word equals while "c" variable must be at the
right of that keyword. Literature sometimes speaks of left and right side
(of equals), but that has nothing to do with SQL LEFT JOIN or SQL RIGHT
JOIN.

The result is somehow described as: for each value from b, **link** to it
all the values from d satisfying the relation field = otherField. There
is no direct SQL statement for that since it is not targeting a
'rectangular' representation of the data.



-------------------------------
To get the SQL INNER JOIN, removes the

into newSet

-------------------------------
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.


------------------------------

There is no LINQ-Right outer join, or LINQ-full outer join.


--------------------------
You can use sub-query, here, in the SQL-select clause:


LINQ:
from a in b
select new { ..., someName = { from c in d ... select something} }

SQL:
SELECT... (SELECT something FROM d AS c ... ) AS someName

-------------------------

We can cascade the queries:

var query1 = from ... ;
var query2 = from query1 ... ;

which can be used too if you need complex outer join, for your SQL
statement.
------------------------------

For non-equi join, I suggest you start with a cross join and add the
required where clause. That won't do much for outer non equi join,
though.

-------------------------------


You can use direct to SQL with ExecuteQuery<T> method accepting the SQL
statement as a string.

-------------------------------

There is much more to it.

-------------------------------


Note that I didn't include a SELECT clause in many of the examples: you
have to, in order to make a valid statement.



Hoping it may help,
Vanderghast, Access MVP




"shapper" <mdmoura@xxxxxxxxx> wrote in message
news:1f6a18e9-998a-4ff1-95f4-b1a0b90da256@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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




--
------------------------------------------------------------------------
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#)
------------------------------------------------------------------------


.

Relevant Pages

  • Re: SQL to Linq - Left, Right and Inner Joins
    ... recognize an inner join and thus, limit the effective amount of data transferred through the wire. ... 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. ... 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. ... At least, for me, that helps to see the process as being a double translation, and to STOP thinking in SQL sets, while using LINQ, but to then think in terms of for-each sequences. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQL to Linq - Left, Right and Inner Joins
    ... Remember that SQL is not about HOW TO ... is NO PROBLEM using a CROSS JOIN to do it, FOLLOWED by a WHERE clause. ... normally put in the ON clause of the INNER join. ...
    (microsoft.public.dotnet.languages.csharp)
  • Need some help with simple search query
    ... It is doable via stored procedure. ... NOW Put your main SELECT CLAUSE IN ONE Variable WITHOUT ... >FROM CliDrAppts.dbo.tblCliAppts CA INNER JOIN ... NOW Execute Dymanic SQL. ...
    (microsoft.public.sqlserver.programming)
  • Re: Sql 2005 with Access 2003 sort order issue
    ... You resolved my issue and increased my SQL knowledge. ... followed by SQL-Server; especially when you add a TOP 100 PERCENT clause. ... The TOP clause is usually used in conjonction with an Order By to retrieve ... FROM dbo.Tbl_Drums INNER JOIN ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
Loading