One thing I've constantly wondered about with ORMs is why they use different mental models than SQL, the thing they're almost always abstracting. We hire for SQL proficiency, but then immediately throw it away with complex edge case stuff. You know, like LEFT OUTER JOINs. /sarcasm

From MSDN:

The first step in producing a left outer join of two collections is to perform an inner join by using a group join....

The second step is to include each element of the first (left) collection in the result set even if that element has no matches in the right collection. This is accomplished by calling DefaultIfEmpty on each sequence of matching elements from the group join.

Yes, you heard that right. To perform a left outer join, you perform an inner join into a join table, then fill that join table in with default (null?) values if there's no inner join match.

Wth? Their example:

var query = from person in people
    join pet in pets on person equals pet.Owner into gj
    from subpet in gj.DefaultIfEmpty()
    select new { 
        person.FirstName, 
        PetName = (subpet == null ? String.Empty : subpet.Name) 
    };

I'm tempted just to write a view and use that instead. This is stupid. The syntax really should be...

var query = from person in people
    left outer join pet in pets 
        on person equals pet.Owner
    select new { 
        person.FirstName, 
        PetName = subpet.Name ?? string.Empty
    };

I don't get it. I wonder how hard it'd be to write an extension that does that...

Labels: ef, noteToSelf, SQL, SQL Server