LEFT OUTER JOIN in LINQ

take look at this example

class Person
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Phone { get; set; }
}

class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

public static void LeftOuterJoinExample()
{
    Person magnus = new Person {ID = 1, FirstName = "Magnus", LastName = "Hedlund"};
    Person terry = new Person {ID = 2, FirstName = "Terry", LastName = "Adams"};
    Person charlotte = new Person {ID = 3, FirstName = "Charlotte", LastName = "Weiss"};
    Person arlene = new Person {ID = 4, FirstName = "Arlene", LastName = "Huff"};

    Pet barley = new Pet {Name = "Barley", Owner = terry};
    Pet boots = new Pet {Name = "Boots", Owner = terry};
    Pet whiskers = new Pet {Name = "Whiskers", Owner = charlotte};
    Pet bluemoon = new Pet {Name = "Blue Moon", Owner = terry};
    Pet daisy = new Pet {Name = "Daisy", Owner = magnus};

    // Create two lists.
    List<Person> people = new List<Person> {magnus, terry, charlotte, arlene};
    List<Pet> pets = new List<Pet> {barley, boots, whiskers, bluemoon, daisy};

    var query = from person in people
        where person.ID == 4
        join pet in pets on person equals pet.Owner  into personpets
        from petOrNull in personpets.DefaultIfEmpty()
        select new { Person=person, Pet = petOrNull}; 



    foreach (var v in query )
    {
        Console.WriteLine("{0,-15}{1}", v.Person.FirstName + ":", (v.Pet == null ? "Does not Exist" : v.Pet.Name));
    }
}

// This code produces the following output:
//
// Magnus:        Daisy
// Terry:         Barley
// Terry:         Boots
// Terry:         Blue Moon
// Charlotte:     Whiskers
// Arlene:

now you are able to include elements from the left even if that element has no matches in the right, in our case we retrived Arlene even he has no matching in the right

here is the reference

How to: Perform Left Outer Joins (C# Programming Guide)

Here is a fairly easy to understand version using method syntax:

IEnumerable<JoinPair> outerLeft =
    lefts.SelectMany(l => 
        rights.Where(r => l.Key == r.Key)
              .DefaultIfEmpty(new Item())
              .Select(r => new JoinPair { LeftId = l.Id, RightId = r.Id }));

Extension method that works like left join with Join syntax

public static class LinQExtensions
{
    public static IEnumerable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
        this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, 
        Func<TOuter, TKey> outerKeySelector, 
        Func<TInner, TKey> innerKeySelector, 
        Func<TOuter, TInner, TResult> resultSelector)
    {
        return outer.GroupJoin(
            inner, 
            outerKeySelector, 
            innerKeySelector,
            (outerElement, innerElements) => resultSelector(outerElement, innerElements.FirstOrDefault()));
    }
}

just wrote it in .NET core and it seems to be working as expected.

Small test:

        var Ids = new List<int> { 1, 2, 3, 4};
        var items = new List<Tuple<int, string>>
        {
            new Tuple<int, string>(1,"a"),
            new Tuple<int, string>(2,"b"),
            new Tuple<int, string>(4,"d"),
            new Tuple<int, string>(5,"e"),
        };

        var result = Ids.LeftJoin(
            items,
            id => id,
            item => item.Item1,
            (id, item) => item ?? new Tuple<int, string>(id, "not found"));

        result.ToList()
        Count = 4
        [0]: {(1, a)}
        [1]: {(2, b)}
        [2]: {(3, not found)}
        [3]: {(4, d)}

I would like to add that if you get the MoreLinq extension there is now support for both homogenous and heterogeneous left joins now

http://morelinq.github.io/2.8/ref/api/html/Overload_MoreLinq_MoreEnumerable_LeftJoin.htm

example:

//Pretend a ClientCompany object and an Employee object both have a ClientCompanyID key on them

return DataContext.ClientCompany
    .LeftJoin(DataContext.Employees,                         //Table being joined
        company => company.ClientCompanyID,                  //First key
        employee => employee.ClientCompanyID,                //Second Key
        company => new {company, employee = (Employee)null}, //Result selector when there isn't a match
        (company, employee) => new { company, employee });   //Result selector when there is a match

EDIT:

In retrospect this may work, but it converts the IQueryable to an IEnumerable as morelinq does not convert the query to SQL.

You can instead use a GroupJoin as described here: https://stackoverflow.com/a/24273804/4251433

This will ensure that it stays as an IQueryable in case you need to do further logical operations on it later.

Easy way is to use Let keyword. This works for me.

from AItem in Db.A
Let BItem = Db.B.Where(x => x.id == AItem.id ).FirstOrDefault() 
Where SomeCondition
Select new YourViewModel
{
    X1 = AItem.a,
    X2 = AItem.b,
    X3 = BItem.c
}

This is a simulation of Left Join. If each item in B table not match to A item , BItem return null

As per my answer to a similar question, here:

Linq to SQL left outer join using Lambda syntax and joining on 2 columns (composite join key)

Get the code here, or clone my github repo, and play!

Query:

        var petOwners =
            from person in People
            join pet in Pets
            on new
            {
                person.Id,
                person.Age,
            }
            equals new
            {
                pet.Id,
                Age = pet.Age * 2, // owner is twice age of pet
            }
            into pets
            from pet in pets.DefaultIfEmpty()
            select new PetOwner
            {
                Person = person,
                Pet = pet,
            };

Lambda:

        var petOwners = People.GroupJoin(
            Pets,
            person => new { person.Id, person.Age },
            pet => new { pet.Id, Age = pet.Age * 2 },
            (person, pet) => new
            {
                Person = person,
                Pets = pet,
            }).SelectMany(
            pet => pet.Pets.DefaultIfEmpty(),
            (people, pet) => new
            {
                people.Person,
                Pet = pet,
            });

Here's a version of the extension method solution using IQueryable instead of IEnumerable

public class OuterJoinResult<TLeft, TRight>
{
    public TLeft LeftValue { get; set; }
    public TRight RightValue { get; set; }
}

public static IQueryable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(this IQueryable<TLeft> left, IQueryable<TRight> right, Expression<Func<TLeft, TKey>> leftKey, Expression<Func<TRight, TKey>> rightKey, Expression<Func<OuterJoinResult<TLeft, TRight>, TResult>> result)
{
    return left.GroupJoin(right, leftKey, rightKey, (l, r) => new { l, r })
          .SelectMany(o => o.r.DefaultIfEmpty(), (l, r) => new OuterJoinResult<TLeft, TRight> { LeftValue = l.l, RightValue = r })
          .Select(result);
}

Overview: In this code snippet, I demonstrate how to group by ID where Table1 and Table2 have a one to many relationship. I group on Id, Field1, and Field2. The subquery is helpful, if a third Table lookup is required and it would have required a left join relationship. I show a left join grouping and a subquery linq. The results are equivalent.

class MyView
{
public integer Id {get,set};
    public String Field1  {get;set;}
public String Field2 {get;set;}
    public String SubQueryName {get;set;}                           
}

IList<MyView> list = await (from ci in _dbContext.Table1
                                               join cii in _dbContext.Table2
                                                   on ci.Id equals cii.Id

                                               where ci.Field1 == criterion
                                               group new
                                               {
                                                   ci.Id
                                               } by new { ci.Id, cii.Field1, ci.Field2}

                                           into pg
                                               select new MyView
                                               {
                                                   Id = pg.Key.Id,
                                                   Field1 = pg.Key.Field1,
                                                   Field2 = pg.Key.Field2,
                                                   SubQueryName=
                                                   (from chv in _dbContext.Table3 where chv.Id==pg.Key.Id select chv.Field1).FirstOrDefault()
                                               }).ToListAsync<MyView>();


 Compared to using a Left Join and Group new

IList<MyView> list = await (from ci in _dbContext.Table1
                                               join cii in _dbContext.Table2
                                                   on ci.Id equals cii.Id

                       join chv in _dbContext.Table3
                                                  on cii.Id equals chv.Id into lf_chv
                                                from chv in lf_chv.DefaultIfEmpty()

                                               where ci.Field1 == criterion
                                               group new
                                               {
                                                   ci.Id
                                               } by new { ci.Id, cii.Field1, ci.Field2, chv.FieldValue}

                                           into pg
                                               select new MyView
                                               {
                                                   Id = pg.Key.Id,
                                                   Field1 = pg.Key.Field1,
                                                   Field2 = pg.Key.Field2,
                                                   SubQueryName=pg.Key.FieldValue
                                               }).ToListAsync<MyView>();

The prescribed way to do a left join using LINQ is to to a GroupJoin followed by a SelectMany. I prefer to abstract this logic into a LeftJoin extension method to make the code much more readable.

Here is the LeftJoin implementation that I use. The two major benefits of this implementation are:

1. It specifically works with IQueryables instead of materialized lists, which means the query can be translated into SQL.

2. The final parameter is an Expression<Func<TOuter,TInner,TResult>> instead of an Expression<Func<JoinResult<TOuter,Tinner>,TResult>> (or something similar). This means the LeftJoin method is used identically to the Join method:

       var results = DbContext.Categories
           .LeftJoin(
               DbContext.Products,
               c => c.Id,
               p => p.CategoryId,
               (c, p) => new { Category = c, ProductName = p == null ? "(No Products)" : p.ProductName })
           .ToList();
   
       var results = DbContext.Categories
           .Join(
               DbContext.Products,
               c => c.Id,
               p => p.CategoryId,
               (c, p) => new { Category = c, ProductName = p.ProductName })
           .ToList();
   

Here's how:

IQueryable Extensions:

public static class QueryableExtensions
{
    public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(this IQueryable<TOuter> outer, IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, Expression<Func<TInner, TKey>> innerKeySelector, Expression<Func<TOuter, TInner, TResult>> resultSelector)
    {
        var query = outer
            .GroupJoin(inner, outerKeySelector, innerKeySelector, (o, i) => new { o, i })
            .SelectMany(o => o.i.DefaultIfEmpty(), (x, i) => new { x.o, i });
        return ApplySelector(query, x => x.o, x => x.i, resultSelector);
    }

    private static IQueryable<TResult> ApplySelector<TSource, TOuter, TInner, TResult>(
        IQueryable<TSource> source,
        Expression<Func<TSource, TOuter>> outerProperty,
        Expression<Func<TSource, TInner>> innerProperty,
        Expression<Func<TOuter, TInner, TResult>> resultSelector)
    {
        var p = Expression.Parameter(typeof(TSource), $"param_{Guid.NewGuid()}".Replace("-", string.Empty));
        Expression body = resultSelector?.Body
            .ReplaceParameter(resultSelector.Parameters[0], outerProperty.Body.ReplaceParameter(outerProperty.Parameters[0], p))
            .ReplaceParameter(resultSelector.Parameters[1], innerProperty.Body.ReplaceParameter(innerProperty.Parameters[0], p));
        var selector = Expression.Lambda<Func<TSource, TResult>>(body, p);
        return source.Select(selector);
    }
}

Expression Extensions:

public static class ExpressionExtensions
{
    public static Expression ReplaceParameter(this Expression source, ParameterExpression toReplace, Expression newExpression)
        => new ReplaceParameterExpressionVisitor(toReplace, newExpression).Visit(source);
}

Expression Visitors:

public class ReplaceParameterExpressionVisitor : ExpressionVisitor
{
    public ReplaceParameterExpressionVisitor(ParameterExpression toReplace, Expression replacement)
    {
        this.ToReplace = toReplace;
        this.Replacement = replacement;
    }

    public ParameterExpression ToReplace { get; }

    public Expression Replacement { get; }

    protected override Expression VisitParameter(ParameterExpression node)
        => (node == ToReplace) ? Replacement : base.VisitParameter(node);
}

This is the prettiest solution I use, give it a try!

(from c in categories
      let product = products.Where(d=> d.Category == c.Category).FirstOrDefault()
    select new { Category = c, ProductName = p == null ? "(No products)" : product.ProductName };