Outer Joins in Linq | The Coffeehouse

JoshRoss
A righteous infliction of retribution manifested by an appropriate agent.

Does anyone know why outer joins in linq are so crazy?

Example:

Dim z = From c In db.Customers
Group Join o In db.Orders
On c.CustID Equals o.CustID
Into j
From jo In j.DefaultIfEmpty()
Select New With {c, jo}

There has to be a good story behind this.

figuerres
???

what about this is "so crazy" ?

JoshRoss
A righteous infliction of retribution manifested by an appropriate agent.

In reply to figuerres

#Nov 8th @ 8:23 AM

The inner join is very strait forward:

from x join y on x.id equals y.id

select new(x,y)

For a left join, why not:

from x leftJoin y on x.id equals y.id

select new(x,y)

Also, IMHO, the new leftJoin should automagically convert y into a nullable of y....

Continuing on...

This seems super odd, like the taste you get after brushing your teeth and drinking orange juice:

from x group join y on x.id equals y.id

into group

from z in group.DefaultIfEmpty

select new(x,z)

joechung

In reply to JoshRoss

#Nov 8th @ 11:22 AM

If you're going to do left outer join, you should do right outer join and full outer join, too.

The syntax should translate to API calls so, as an exercise, write the API that a left join would become after translation and then, once you've done that, map your syntax to that API in a way that a compiler could implement.

I would definitely post your suggestion in the "Ideas for C# 5.0" thread in TechOff if you haven't already.

contextfree

I'd imagine it's because SQL outer join semantics don't make much sense, and they figured that when you use them you're probably really trying to simulate a group join, so in a language with composite object types why not just support that directly instead?

JoshRoss
A righteous infliction of retribution manifested by an appropriate agent.

In reply to contextfree

#Nov 8th @ 6:54 PM

I fully understand the SQL join semantics, and could easily draw the venn diagrams for each case, with any number of tables. What I do not yet understand is the concept of the group join, outside of how it can simulate a left outer join in linq. Since you seem to have a good idea how this works, and I am a visual learner, would you mind drawing me a picture? Or at least point me in the right direction?

JoshRoss
A righteous infliction of retribution manifested by an appropriate agent.

In reply to joechung

#Nov 8th @ 3:40 PM

I wonder how I should do this? Do you think, I should write an extension method that would act on an iEnumerable, and use another iEnumerable and a predicate. The predicate would be for equality and the function would return an iEnumerable. iQueriables are tricky. However, this would present a great opportunity to play with the new extension statements.

contextfree

In reply to JoshRoss

#Nov 8th @ 8:43 PM

I don't really have time to give you a full answer (or draw a picture -- sorry), but basically, when you do a left outer join in SQL between two tables A and B, what you really want in return is something that lets you answer the question "for each element (or for a given element) in A, what is the set of elements of B associated with it (using such and such criteria for association) ?", right? And you want to be able to get an answer even if that answer is "the empty set". But since a SQL query must return a table, and since (IIRC) individual cells in standard SQL can't have set or collection types, outer joins awkwardly simulate this by doing an inner join but adding extra rows, with null values* for all the columns of B, wherever there's an A with no associated Bs. This is usable, but doesn't really make any logical sense -- a set of Bs with no members is not the same as a B whose columns are all empty (or unknown)! Since .NET has its object system with collection types, etc., it doesn't have to do this -- it can just take each A, and directly associate it with a collection of corresponding Bs, which may be empty if no such Bs exist.

* (which in SQL are problematic in themselves, since SQL can't make up its mind whether they mean "there is nothing here" or "there is something here but I don't know what it is")

spivonious

In reply to contextfree

#Nov 8th @ 9:32 PM

I do outer joins when I want to get child rows from an "optional" table, so returning a row of all nulls makes perfect sense to me.

JoshRoss
A righteous infliction of retribution manifested by an appropriate agent.

In reply to contextfree

#Nov 8th @ 9:32 PM

Paraphrasing Orwell, all nulls are equal, except some more than others. I would disagree about SQL not having collection types. As soon as you put a constraint on a table, that table effectively has a collection type.

I may have lost something in translation with the "there is something here but I don't know what it is" statement. The nothing here bit, can be resolved via coalesce, separating null values from missing values.