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   gt  f Foo Id  b   gt  b Foo Id   f b    gt            Select

User · Answer

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.

User · Answer

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   gt  foo Foo Id            bar   gt  bar Foo Id             x y    gt  new   Foo   x  Bars   y            SelectMany             x   gt  x Bars DefaultIfEmpty                x y    gt  new   Foo x Foo  Bar y

User · Answer

Turning Marc Gravell s answer into an extension method  I made the following   internal static IEnumerable lt Tuple lt TLeft  TRight gt  gt  LeftJoin lt TLeft  TRight  TKey gt       this IEnumerable lt TLeft gt  left      IEnumerable lt TRight gt  right      Func lt TLeft  TKey gt  selectKeyLeft      Func lt TRight  TKey gt  selectKeyRight      TRight defaultRight   default TRight       IEqualityComparer lt TKey gt  cmp   null        return left GroupJoin              right              selectKeyLeft              selectKeyRight               x  y    gt  new Tuple lt TLeft  IEnumerable lt TRight gt  gt  x  y               cmp    EqualityComparer lt TKey gt  Default           SelectMany              x   gt  x Item2 DefaultIfEmpty defaultRight                x  y    gt  new Tuple lt TLeft  TRight gt  x Item1  y

User · Answer

Group Join method is unnecessary to achieve joining of two data sets   Inner Join   var qry   Foos SelectMany                               foo   gt  Bars Where  bar   gt  foo Foo id    bar Foo id                    foo  bar    gt  new                                           Foo   foo                      Bar   bar                                        For Left Join just add DefaultIfEmpty    var qry   Foos SelectMany                               foo   gt  Bars Where  bar   gt  foo Foo id    bar Foo id  DefaultIfEmpty                     foo  bar    gt  new                                           Foo   foo                      Bar   bar                                        EF and LINQ to SQL correctly transform to SQL  For LINQ to Objects it is beter to join using GroupJoin as it internally uses Lookup  But if you are querying DB then skipping of GroupJoin is AFAIK as performant   Personlay for me this way is more readable compared to GroupJoin   SelectMany

User · Answer

You can create extension method like   public static IEnumerable lt TResult gt  LeftOuterJoin lt TSource  TInner  TKey  TResult gt  this IEnumerable lt TSource gt  source  IEnumerable lt TInner gt  other  Func lt TSource  TKey gt  func  Func lt TInner  TKey gt  innerkey  Func lt TSource  TInner  TResult gt  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

User · Answer

Whilst the accepted answer works and is good for Linq to Objects it bugged me that the SQL query isn t just a straight Left Outer Join   The following code relies on the LinkKit Project that allows you to pass expressions and invoke them to your query   static IQueryable lt TResult gt  LeftOuterJoin lt TSource TInner  TKey  TResult gt        this IQueryable lt TSource gt  source        IQueryable lt TInner gt  inner        Expression lt Func lt TSource TKey gt  gt  sourceKey        Expression lt Func lt TInner TKey gt  gt  innerKey        Expression lt Func lt TSource  TInner  TResult gt  gt  result             return from a in source AsExpandable               join b in inner on sourceKey Invoke a  equals innerKey Invoke b  into c             from d in c DefaultIfEmpty               select result Invoke a d       It can be used as follows  Table1 LeftOuterJoin Table2  x   gt  x Key1  x   gt  x Key2   x y    gt  new   x y

User · Answer

Improving on Ocelot20 s answer  if you have a table you re left outer joining with where you just want 0 or 1 rows out of it  but it could have multiple  you need to Order your joined table   var qry   Foos GroupJoin        Bars OrderByDescending b   gt  b Id         foo   gt  foo Foo Id        bar   gt  bar Foo Id         f  bs    gt  new   Foo   f  Bar   bs FirstOrDefault         Otherwise which row you get in the join is going to be random  or more specifically  whichever the db happens to find first

[c#] How do you perform a left outer join using linq extension methods

Examples related to c#

Examples related to linq-to-sql

Examples related to lambda