LINQ LEFT OUTER JOIN

Problem

Without using join-on-equals-into clauses, how can I execute a left outer join in C# LINQ to objects? Is there a way to accomplish this with the where clause? Fix the issue: Inner join is simple, and I have a solution for you.

List<JoinPair> innerFinal = (from l in lefts from r in rights where l.Key == r.Key
                             select new JoinPair { LeftId = l.Id, RightId = r.Id})

However, I’m looking for a solution for the left outer join. Mine looks like this, but it doesn’t work.

List< JoinPair> leftFinal = (from l in lefts from r in rights
                             select new JoinPair { 
                                            LeftId = l.Id, 
                                            RightId = ((l.Key==r.Key) ? r.Id : 0
                                        })

JoinPair is a class that:

public class JoinPair { long leftId; long rightId; }

Asked by Toy

Solution #1

As stated on:

101 LINQ Samples – Outer Join (Left)

var q =
    from c in categories
    join p in products on c.Category equals p.Category into ps
    from p in ps.DefaultIfEmpty()
    select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };

Answered by ajay_whiz

Solution #2

If a database-driven LINQ provider is utilized, the following is a much more readable left outer join:

from maintable in Repo.T_Whatever 
from xxx in Repo.T_ANY_TABLE.Where(join condition).DefaultIfEmpty()

You’ll get an inner join if you don’t use DefaultIfEmpty().

Consider the standard response:

  from c in categories
    join p in products on c equals p.Category into ps
    from p in ps.DefaultIfEmpty()

When you wish to left connect MULTIPLE tables, this syntax is highly perplexing, and it’s not apparent how it works.

Note It should be noticed that in Repo, from alias. whatever. Where(condition). Default IfEmpty() is the same as an outer-apply/left-join-lateral, which any (good) database optimizer can convert to a left join as long as per-row-values aren’t introduced (aka an actual outer apply). This should not be done in Linq-2-Objects (since there is no DB-optimizer in Linq-to-Objects).

Detailed Example

var query2 = (
    from users in Repo.T_User
    from mappings in Repo.T_User_Group
         .Where(mapping => mapping.USRGRP_USR == users.USR_ID)
         .DefaultIfEmpty() // <== makes join left join
    from groups in Repo.T_Group
         .Where(gruppe => gruppe.GRP_ID == mappings.USRGRP_GRP)
         .DefaultIfEmpty() // <== makes join left join

    // where users.USR_Name.Contains(keyword)
    // || mappings.USRGRP_USR.Equals(666)  
    // || mappings.USRGRP_USR == 666 
    // || groups.Name.Contains(keyword)

    select new
    {
         UserId = users.USR_ID
        ,UserName = users.USR_User
        ,UserGroupId = groups.ID
        ,GroupName = groups.Name
    }

);


var xy = (query2).ToList();

It will translate neatly to the following very clear SQL query when used with LINQ 2 SQL:

SELECT 
     users.USR_ID AS UserId 
    ,users.USR_User AS UserName 
    ,groups.ID AS UserGroupId 
    ,groups.Name AS GroupName 
FROM T_User AS users

LEFT JOIN T_User_Group AS mappings
   ON mappings.USRGRP_USR = users.USR_ID

LEFT JOIN T_Group AS groups
    ON groups.GRP_ID == mappings.USRGRP_GRP

Edit:

For a more complicated example, see ” Convert SQL Server query to Linq query “.

Also, if you’re doing it in Linq-2-Objects (rather than Linq-2-SQL), you should do it the old-fashioned manner (since LINQ to SQL correctly translates this to join operations, but over objects this technique requires a full scan and ignores index searches, for whatever reason…):

    var query2 = (
    from users in Repo.T_Benutzer
    join mappings in Repo.T_Benutzer_Benutzergruppen on mappings.BEBG_BE equals users.BE_ID into tmpMapp
    join groups in Repo.T_Benutzergruppen on groups.ID equals mappings.BEBG_BG into tmpGroups
    from mappings in tmpMapp.DefaultIfEmpty()
    from groups in tmpGroups.DefaultIfEmpty()
    select new
    {
         UserId = users.BE_ID
        ,UserName = users.BE_User
        ,UserGroupId = mappings.BEBG_BG
        ,GroupName = groups.Name
    }

);

Answered by Stefan Steiger

Solution #3

Using lambda expression

db.Categories    
  .GroupJoin(db.Products,
      Category => Category.CategoryId,
      Product => Product.CategoryId,
      (x, y) => new { Category = x, Products = y })
  .SelectMany(
      xy => xy.Products.DefaultIfEmpty(),
      (x, y) => new { Category = x.Category, Product = y })
  .Select(s => new
  {
      CategoryName = s.Category.Name,     
      ProductName = s.Product.Name   
  });

Answered by N Rocking

Solution #4

As an example of an extension method:

public static class LinqExt
{
    public static IEnumerable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(this IEnumerable<TLeft> left, IEnumerable<TRight> right, Func<TLeft, TKey> leftKey, Func<TRight, TKey> rightKey,
        Func<TLeft, TRight, TResult> result)
    {
        return left.GroupJoin(right, leftKey, rightKey, (l, r) => new { l, r })
             .SelectMany(
                 o => o.r.DefaultIfEmpty(),
                 (l, r) => new { lft= l.l, rght = r })
             .Select(o => result.Invoke(o.lft, o.rght));
    }
}

Use join in the same way you would normally:

var contents = list.LeftOuterJoin(list2, 
             l => l.country, 
             r => r.name,
            (l, r) => new { count = l.Count(), l.country, l.reason, r.people })

I hope this has helped you save some time.

Answered by Matas Vaitkevicius

Solution #5

Take a look at this illustration. The following query should work:

var leftFinal = from left in lefts
                join right in rights on left equals right.Left into leftRights
                from leftRight in leftRights.DefaultIfEmpty()
                select new { LeftId = left.Id, RightId = left.Key==leftRight.Key ? leftRight.Id : 0 };

Answered by Devart

Post is based on https://stackoverflow.com/questions/3404975/left-outer-join-in-linq