How do you use linq extension techniques to accomplish a left outer join?

Problem

Assuming I had a left outer join, here’s what I’d do:

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 }

What extension methods would I use to express the same task? E.g.

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

Asked by LaserJesus

Solution #1

To attach a table Bar to a table Foo on Foo on the (left outer) side. Foo Id equals Bar. In lambda notation, Foo Id is:

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});

Answered by Marc Gravell

Solution #2

Since this appears to be the de facto SO question for left outer joins using the method (extension) syntax, I though I’d provide an option to the currently picked response that (at least in my experience) has been more typically what I’m looking for.

// 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 });

Using a basic data set to show the difference (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 normal left outer join specification, although it is often overly complex depending on the data set, as I described previously.

Answered by Ocelot20

Solution #3

To join two data sets, the Group Join technique is not required.

Inner Join:

var qry = Foos.SelectMany
            (
                foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id),
                (foo, bar) => new
                    {
                    Foo = foo,
                    Bar = bar
                    }
            );

Simply add DefaultIfEmpty to the left join ()

var qry = Foos.SelectMany
            (
                foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id).DefaultIfEmpty(),
                (foo, bar) => new
                    {
                    Foo = foo,
                    Bar = bar
                    }
            );

EF and LINQ to SQL both convert to SQL successfully. It is preferable to use GroupJoin for LINQ to Objects because it uses Lookup internally. However, if you’re accessing a database, bypassing GroupJoin is, AFAIK, as fast.

In comparison to GroupJoin, I find this method to be more readable (). SelectMany()

Answered by Gediminas Zimkus

Solution #4

You can make an extension method that looks like this:

public static IEnumerable<TResult> LeftOuterJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> other, Func<TSource, TKey> func, Func<TInner, TKey> innerkey, Func<TSource, TInner, TResult> res)
    {
        return from f in source
               join b in other on func.Invoke(f) equals innerkey.Invoke(b) into g
               from result in g.DefaultIfEmpty()
               select res.Invoke(f, result);
    }

Answered by hajirazin

Solution #5

Improving on Ocelot20’s response, if you have a table that you’re left outer connecting with and only want 0 or 1 row out of it, but it could have numerous, you should Order your joined table:

var qry = Foos.GroupJoin(
      Bars.OrderByDescending(b => b.Id),
      foo => foo.Foo_Id,
      bar => bar.Foo_Id,
      (f, bs) => new { Foo = f, Bar = bs.FirstOrDefault() });

Otherwise, the row you get in the join will be chosen at random (or more specifically, whichever the db happens to find first).

Answered by Chris Moschini

Post is based on https://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods