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

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


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 Data Provider Performance Issues
    ... selects with a single inner join perforn OK. ... When I run the query from SQL ... Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQl Data Provider Performance Issues
    ... selects with a single inner join perforn OK. ... When I run the query from SQL Query ... Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQl Data Provider Performance Issues
    ... Can you provide a complete sample app ... selects with a single inner join perforn OK. ... When I run the query from SQL ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: sysindexes
    ... collation, but you should be able to use it for your column change as well. ... SQL Server MVP ... DECLARE @sql VARCHAR ... INNER JOIN information_schema.table_constraints tc ...
    (microsoft.public.sqlserver.server)
  • 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)