score:2

Accepted answer

If you want to do a right outer join of table A and B, simply exchange these tables and you can do a left outer join.

A left outer join is a GroupJoin followed by a SelectMany. In my experience I use the GroupJoin far more often than I use the Left Outer Join. Especially in One-to-many relations.

For example: Suppose you have a table of Schools and a table of Students. Every School has zero or more Students, every Student studies at exactly one School, using foreign key SchoolId: a straightforward one-to-many relation.

Give me all Schools with all their Students

var result = dbContext.Schools
    .GroupJoin(dbContext.Students,          // GroupJoin Schools and Students
    school => school.Id,                    // from every School take the primary key
    student => student.SchoolId,            // from every Student take the foreign key
    (school, studentsOnThisSchool) => new   // from every School with all its Students
    {                                       // make one new object

        // Select only the School properties I plan to use
        Id = schoolId,
        Name = school.Name,

        OlderStudents = studentsOnThisSchool

            .Select(student => new
            {
                // Select only the Student properties I plan to use:
                Id = student.Id,
                Name = student.Name,
                ...

                // not needed, I already know the value:
                // SchoolId = student.SchoolId,
            });

The result will be a sequence like:

School 1 with Students A, B, C, D.
School 2 with Students E, F,
School 3 without any Students
School 4 with Students G, H, I,
...

This seems to me much more useful than the result of the left outer join:

School 1 with Student A,
School 2 with Student E,
School 3 with Null student,
School 1 with Student B,
School 2 with Student F,
School 1 with Student C,
...

But hey, it's your choice.

I have a TblTrade, which contains Trades. Every Trade has at least properties Bt, PosGuid and RowType. I also have a TblSechedule which contains Schedules. Every Schedule has at least properties Bt and PosGuid. Give me all Trades with RowType 4 with all zero or more Schedules that have the same value for PosGuid.

var result = tblTrade
    // keep only the trades that have RowType equal to 4:
    .Where(trade => trade.RowType == 4)

    // do the GroupJoin:
    .GroupJoin(tblSchedule,
         trade => trade.PosGuid,
         schedule => schedule.PosGuid,
         (trade, schedulesWithSamePosGuid) => new
         {
             // Select the trade properties you plan to use:
             TradeId = trade.Id,
             PosGuid = trade.PosGuid,
             ...

             Schedules = schedulesWithSamePosGuid.Select(schedule => new
             {
                  // Select the schedule properties you plan to use:
                  Id = schedule.Id,
                  ...

                  // not needed, you already know the value:
                  // PosGuid = schedule.PosGuid.
              })
              .ToList(),
         });

If you really want a flat Left Outer Join, add a SelectMany:

.SelectMany(groupJoinResult.Schedules,
(trade, schedule) => new
{
    PosGuid = trade.PosGuid,

    // the Trade properties:
    Trade = new
    {
        Id = trade.TradeId,
        ...
    },

    Schedule = new
    {
        Id = schedule.Id,
        ...
    },
});

If you want, you can create an extension function LeftOuterJoin:

public static class MyQueryableExtensions
{
    // version without EqualityComparer:
    public static IQueryable<TResult> LeftOuterJoin<T1, T2, TKey, TResult>(
       this IQueryable<T1> source1,
       IQueryable<T2> source2,
       Func<T1, TKey> key1Selector,
       Func<T2, TKey> key2Selector,
       Func<T1, T2, TResult) resultSelector)
    {
        return LeftOuterJoin(source1, source2,
            key1Selector, key2Selector, resultSelector, null);
    }

version with EqualityComparer:

public static IQueryable<TResult> LeftOuterJoin<T1, T2, TKey, TResult>(
   this IQueryable<T1> source1,
   IQueryable<T2> source2,
   Func<T1, TKey> key1Selector,
   Func<T2, TKey> key2Selector,
   Func<T1, T2, TResult) resultSelector,
   IEqualityComparer<TKey> comparer)
{
    if (comparer == null) comparer = EqualityComparer<TKey>.Default;

    // GroupJoin followed by SelectMany:
    return GroupJoin(source1, source2, key1Selector, key2Selector,
        (source1Item1, source2ItemsWithSameKey) => new
        {
            Source1Item = source1Item,
            Source2Items = source2ItemsWithSameKey,
        })
        .SelectMany(groupJoinResult => groupJoinResult.Source2Items,
           (groupJoinResult, source2Item) =>
               ResultSelector(groupJoinResult.Source1Item, source2Item));

    }
}

Usage:

var result = tblTrade
    .Where(trade => trade.RowType == 4)
    .LeftOuterJoin(tblSchedule,
    trade => trade.PosGuid,
    schedule => schedule.PosGuid,
    (trade, schedule) => new
    {
       // Select the trade and Schedule properties that you plan to use
       // for example the complete trade and schedule:
       Trade = trade,
       Schedule = schedule,

       // or only some properties:
       CommonPosGuid = trade.PosGuid,
       Trade = new
       {
           Id = trade.Id,
           ...
       }

       Schedule = new
       {
           Id = trade.Id,
           ...
       }
    })

Related Articles