How to implement left join in JOIN Extension method

Question

I am trying to implement an outer join on this kind of query for the p.Person table. How would I do this?

This example is taken from http://ashishware.com/DSLinqExample.shtml

var onlyinfo = p.Person
    .Where(n => n.FirstName.Contains('a'))
    .Join(p.PersonInfo,
        n => n.PersonId,
        m => m.PersonId,
        (n, m) => m)
    .ToArray<Persons.PersonInfoRow>();

Answer 1

Normally left joins in LINQ are modelled with group joins, sometimes in conjunction with DefaultIfEmpty and SelectMany:

var leftJoin = p.Person.Where(n => n.FirstName.Contains("a"))
                       .GroupJoin(p.PersonInfo, 
                                  n => n.PersonId,
                                  m => m.PersonId,
                                  (n, ms) => new { n, ms = ms.DefaultIfEmpty() })
                       .SelectMany(z => z.ms.Select(m => new { n = z.n, m }));

That will give a sequence of pairs (n, m) where n is the entry from p.Person and m is the entry from p.PersonInfo, but m will be null if there are no matches.

(It's completely untested, btw - but should give you the idea anyway :)

Answer 2

For Left outer Join try Following query. This is tested

var leftJoin = Table1
                .GroupJoin(
                               inner: Table2,
                    outerKeySelector: t1 => t1.Col1,
                    innerKeySelector: t2 => t2.Col2,
                      resultSelector: ( t1, t2Rows ) => new { t1, t2Rows.DefaultIfEmpty() }
                )
                .SelectMany( z =>
                    z.t2Rows.Select( t2 =>
                        new { t1 = z.t1, t2 = t2 }
                    )
                );

Answer 3

If anyone comes across this question and wants an extension method to accomplish this, I created one using the same approach as the other answers. It has the same signature as the regular join extension method.

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,
            (outerObj, inners) => new { outerObj, inners = inners.DefaultIfEmpty() })
        .SelectMany(a => a.inners.Select(innerObj => resultSelector(a.outerObj, innerObj)));
}

Answer 4

I have used the following extension method on of my project, It might help you.

/// <summary>
/// Performs a left outer join on two collections.
/// </summary>
/// <typeparam name="TLeft">Type of left IEnumerable collection</typeparam>
/// <typeparam name="TRight">Type of left IEnumerable collection</typeparam>
/// <typeparam name="TKey">The type of the key returned by the key selector functions.</typeparam>
/// <typeparam name="TResult">The type of the result elements.</typeparam>
/// <param name="left"> The left IEnumerable collection of the join operation.</param>
/// <param name="right"> The right IEnumerable collection of the join operation.</param>
/// <param name="leftKeySelector">Function that projects the key given an element from <paramref name="left"/>.</param>
/// <param name="rightKeySelector">Function that projects the key given an element from <paramref name="right"/>.</param>
/// <param name="resultSelector">Function that projects the result given 
///     an element from <paramref name="left"/> and 
///     an element from <paramref name="right"/>
///     that match on a common key.</param>
/// <returns>A sequence containing results projected from a left outer join of the two input collections.</returns>

public static IQueryable<TResult> LeftJoin<TLeft, TRight, TKey, TResult>(
    this IQueryable<TLeft> left,
    IQueryable<TRight> right,
    Expression<Func<TLeft, TKey>> leftKeySelector,
    Expression<Func<TRight, TKey>> rightKeySelector,
    Expression<Func<TLeft, TRight, TResult>> resultSelector)
{
    if (left == null) throw new ArgumentNullException(nameof(left));
    if (right == null) throw new ArgumentNullException(nameof(right));
    if (leftKeySelector == null) throw new ArgumentNullException(nameof(leftKeySelector));
    if (rightKeySelector == null) throw new ArgumentNullException(nameof(rightKeySelector));
    if (resultSelector == null) throw new ArgumentNullException(nameof(resultSelector));

    return left
        .AsExpandable() // LinqKit to convert everything into an expression tree.
        .GroupJoin(
            right,
            leftKeySelector,
            rightKeySelector,
            (leftItem, rightItem) => new { leftItem, rightItem })
        .SelectMany(
            joinResult => joinResult.rightItem.DefaultIfEmpty(),
            (joinResult, rightItem) =>
                resultSelector.Invoke(joinResult.leftItem, rightItem));
}

And this is how it was called

base.context.Users
    .LeftJoin(
        base.context.Workers
        user => user.Userid,
        worker => worker.Workerid,
        (user, worker) => new
        {
            User = user,
            Worker = worker
        })

Got this answer here, Trying to implement a LeftJoin extension method to work with EF Core 2.0