Left join in Linq?

Question

There are a lot of questions on SO already about Left joins in Linq, and the ones I've looked at all use the join keyword to achieve the desired end.

This does not make sense to me. Let's say I have the tables Customer and Invoice, linked by a foreign key CustomerID on Invoice. Now I want to run a report containing customer info, plus any invoices. SQL would be:

select c.*, i.*
  from Customer c
  left join Invoice i on c.ID = i.CustomerID

From what I've seen of the answers on SO, people are mostly suggesting:

var q = from c in Customers
        join i in Invoices.DefaultIfEmpty() on c.ID equals i.CustomerID 
        select new { c, i };

I really don't understand how this can be the only way. The relationship between Customer and Invoice is already defined by the LinqToSQL classes; why should I have to repeat it for the join clause? If I wanted an inner join it would simply have been:

var q = from c in Customers
        from i in c.Invoices
        select new { c, i };

without specifying the joined fields!

I tried:

var q = from c in Customers
        from i in c.Invoices.DefaultIfEmpty()
        select new { c, i };

but that just gave me the same result as if it were an inner join.

Is there not a better way of doing this?

Answer 1

While the relationship is already defined (both in the database and in the .dbml markup) the runtime cannot automatically determine if it should use that relationship.

What if there are two relationships in the object model (Person has Parents and Children, both relationships to other Person instances). While cases could be special cased, this would make the system more complex (so more bugs). Remember in SQL you would repeat the specification of the relationship.

Remember indexes and keys are an implementation detail and not part of the relational algebra that underlies the relation model.

If you want a LEFT OUTER JOIN then you need to use "into":

from c in Customers
join i in Invoices on i.CustomerId equals c.CustomerId into inv
...

and inv will have type IEnumerable<Invoivce>, possibly with no instances.

Answer 2

What are you talking about? That from i in c.Invoice.DefaultIfEmpty() is exactly a left join.

        List<string> strings = new List<string>() { "Foo", "" };

        var q = from s in strings
                from c in s.DefaultIfEmpty()
                select new { s, c };

        foreach (var x in q)
        {
            Console.WriteLine("ValueOfStringIs|{0}| ValueOfCharIs|{1}|",
                x.s,
                (int)x.c);
        }

This test produces:

ValueOfStringIs|Foo| ValueOfCharIs|70|
ValueOfStringIs|Foo| ValueOfCharIs|111|
ValueOfStringIs|Foo| ValueOfCharIs|111|
ValueOfStringIs|| ValueOfCharIs|0|