How do you perform a left outer join using linq extension methods

Question

Assuming I have a left outer join as such:

from f in Foo join b in Bar on f.Foo_Id equals b.Foo_Id into g from result in g.DefaultIfEmpty() select new { Foo = f, Bar = result } 

How would I express the same task using extension methods? E.g.

Foo.GroupJoin(Bar, f => f.Foo_Id, b => b.Foo_Id, (f,b) => ???)     .Select(???) 

Answer 1

For a (left outer) join of a table Bar with a table Foo on Foo.Foo_Id = Bar.Foo_Id in lambda notation:

var qry = Foo.GroupJoin(           Bar,            foo => foo.Foo_Id,           bar => bar.Foo_Id,           (x,y) => new { Foo = x, Bars = y })        .SelectMany(            x => x.Bars.DefaultIfEmpty(),            (x,y) => new { Foo=x.Foo, Bar=y}); 

Answer 2

Since this seems to be the de facto SO question for left outer joins using the method (extension) syntax, I thought I would add an alternative to the currently selected answer that (in my experience at least) has been more commonly what I'm after

// Option 1: Expecting either 0 or 1 matches from the "Right" // table (Bars in this case): var qry = Foos.GroupJoin(           Bars,           foo => foo.Foo_Id,           bar => bar.Foo_Id,           (f,bs) => new { Foo = f, Bar = bs.SingleOrDefault() });  // Option 2: Expecting either 0 or more matches from the "Right" table // (courtesy of currently selected answer): var qry = Foos.GroupJoin(                   Bars,                    foo => foo.Foo_Id,                   bar => bar.Foo_Id,                   (f,bs) => new { Foo = f, Bars = bs })               .SelectMany(                   fooBars => fooBars.Bars.DefaultIfEmpty(),                   (x,y) => new { Foo = x.Foo, Bar = y }); 

To display the difference using a simple data set (assuming we're joining on the values themselves):

List<int> tableA = new List<int> { 1, 2, 3 }; List<int?> tableB = new List<int?> { 3, 4, 5 };  // Result using both Option 1 and 2. Option 1 would be a better choice // if we didn't expect multiple matches in tableB. { A = 1, B = null } { A = 2, B = null } { A = 3, B = 3    }  List<int> tableA = new List<int> { 1, 2, 3 }; List<int?> tableB = new List<int?> { 3, 3, 4 };  // Result using Option 1 would be that an exception gets thrown on // SingleOrDefault(), but if we use FirstOrDefault() instead to illustrate: { A = 1, B = null } { A = 2, B = null } { A = 3, B = 3    } // Misleading, we had multiple matches.                     // Which 3 should get selected (not arbitrarily the first)?.  // Result using Option 2: { A = 1, B = null } { A = 2, B = null } { A = 3, B = 3    } { A = 3, B = 3    }     

Option 2 is true to the typical left outer join definition, but as I mentioned earlier is often unnecessarily complex depending on the data set.