Full join in Linq

Question

I am new to Writing Linq Queries and would like to write a query as below.

Brief Info on Requirement:

I need to get a count of distinct group keys for a user with a full join to another table having its extended data

TABLE - 1:                                         Table - 2: 
---------------                                    -------------

| Id | GrpKey | prdId | UserId|                    | Id | GrpKey | GrpName  | UserId
| 1  | 123455 | Test1 | 11111                      | 1  | 123455 | GroupOne | 1111
| 2  | 123455 | Test2 | 22222                      | 2  | 551234 | GroupTwo | 1111
| 3  | 123455 | Test3 | 22222                      | 3  | 233344 |GroupThree| 1111
| 4  | 551234 | Test4 | 11111                      | 4  | 278344 |GroupFour | 1111
| 5  | 551234 | Test5 | 11111
| 6  | DBNULL | Test4 | 11111
| 7  | DBNULL | Test5 | 11111

REQD. RESULT for UserId : 11111 
--------------------------------

GrpKey | GrpName    | Count(GrpKey)
DBNULL | DBNULL     |  2
551234 | GroupTwo   |  2
123455 | GroupOne   |  1
233344 | GroupThree |  0
278344 | GroupFour  |  0

Queries Tried:

**LEFT JOIN:**
                          from item in table1
                          join grp in table2 on item.GrpKey equals grp.GrpKey  into j1
                          from rt in j1.DefaultIfEmpty()
                          where item.UserId == "1111"
                          group rt by rt.GrpKey into g
                          select new Group
                          {
                                UserId = grp.userId
                                Count = j1.Count(),
                                Name = grp.GrpName,
                                Key = grp.GrpKey,
                          }).ToList();

**RIGHT JOIN:**
              from grp in table2
                          join item in table1 on grp.GrpKey equals item.GrpKey  into j1                          
                          where grp.UserId == "1111"
                          group grp by grp.GrpKey into g
                          select new Group
                          {
                                UserId = grp.userId
                                Count = j1.Count(),
                                Name = grp.GrpName,
                                Key = grp.GrpKey,
                          }).ToList();

result = LeftJoinResult.Union(RightJoinResult).ToList();

Issues With TriedQuery1:

With the above LINQ query, Result set I am getting is:

GrpKey | GrpName    | Count(GrpKey)
DBNULL | DBNULL     |  2
551234 | GroupTwo   |  2
123455 | GroupOne   |  1
551234 | GroupTwo   |  1
233344 | GroupThree |  1
278344 | GroupFour  |  1

Please help me on how to convert this left join to a full join or data as in Reqd result

Thanks in Advance

do a left join, then do a right join and then take the union of both — Vivek Nuna, Jun 26 '20 at 12:29
Is this the only way to achieve it? When I tried to implement union, duplicate rows are existing — vamsi penta, Jun 26 '20 at 12:31
Does this answer your question? [LINQ - Full Outer Join](https://stackoverflow.com/questions/5489987/linq-full-outer-join) — Vivek Nuna, Jun 26 '20 at 12:34
I tried the right join, but then I am getting count as one in each row — vamsi penta, Jun 26 '20 at 12:41
PS You don't want a count of Distinct GrpKeys, as that would imply the answer is 6 for user 11111. — NetMage, Jun 26 '20 at 17:48
What version of EF are you using (EF 6.x / EF Core 2.0 / 2.1 / 2.2 / 3.x)? — NetMage, Jun 26 '20 at 18:04
Did you mean for Table 1 to have user id `11111` and Table 2 to have `1111` and the query to filter down to `1111` which excludes all of Table 1? — NetMage, Jun 26 '20 at 23:05

score 0 · Answer 1 · answered Jun 26 '20 at 23:01

LINQ doesn't include a proper full outer join operator, which could be composed from a left outer join and a right anti-semijoin, but LINQ also doesn't have right anti-semijoin.

Unfortunately EF can't effectively translate my full outer join code combined with a GroupBy, so you have to do most of the work client side.

Using an extension method that provides a proper, correct Full Outer Join for IEnumerable without excessive memory usage:

public static class IEnumerableExt {
    public static IEnumerable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> leftItems,
        IEnumerable<TRight> rightItems,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TKey, TLeft, TRight, TResult> resultSelector) =>
            from left in leftItems
            join right in rightItems on leftKeySelector(left) equals rightKeySelector(right) into rightj
            from right in rightj.DefaultIfEmpty()
            select resultSelector(leftKeySelector(left), left, right);

    public static IEnumerable<TResult> RightAntiSemiJoin<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> leftItems,
        IEnumerable<TRight> rightItems,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TKey, TLeft, TRight, TResult> resultSelector) {

        var hashLK = leftItems.Select(l => leftKeySelector(l)).ToHashSet();
        return rightItems.Where(r => !hashLK.Contains(rightKeySelector(r))).Select(r => resultSelector(rightKeySelector(r), default(TLeft), r));
    }

    public static IEnumerable<TResult> FullOuterJoin<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> leftItems,
        IEnumerable<TRight> rightItems,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TKey, TLeft, TRight, TResult> resultSelector) where TLeft : class {

        return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector).Concat(leftItems.RightAntiSemiJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector));
    }
}

You can compute your answer with:

var userId = "11111";

var ans = t1.Where(u => u.UserId == userId)
            .GroupBy(u => u.GrpKey)
            .ToList()
            .FullOuterJoin(t2.Where(t => t.UserId == userId), tg => tg.Key, t => t.GrpKey, (grpKey, ug, t) => new { grpKey, ug, t })
            .Select(gut => new { GrpKey = gut.grpKey, GrpName = gut.t != null ? gut.t.GrpName : null, Count = gut.ug != null ? gut.ug.Count() : 0 })
            .OrderByDescending(r => r.Count)
            .ThenBy(r => r.GrpKey);

score 0 · Answer 2 · answered Jun 29 '20 at 08:42

So you have a sequence of Table2Elements, where every Table2Element has at least a GrpName, a GrpKey and a UserId.

Furthermore you have a sequence of Table1Elements, where every Table1Element also has a GrpKey and a UserId.

Requirement: for every Table2Element, give me the GrpKey, the GrpName and the number of Table1Elements that have the same value for [GrpKey, UserId] as the Table2Element has.

Whenever you have a sequence of items A, where every item A has some items B from another table that belong to this item A, like Schools with their Students, Authors with their Books, Customers with their Orders, consider to use Enumerable.GroupJoin, or since you are using entity framework Queryable.GroupJoin

var result = dbContext.Table2          // GroupJoin the elements of Table2
.GroupJoin(dbContext.Table1,           // with the elements of Table1
table2Element => new                   // from every element of Table2 take properties ...
{
    GrpKey = table2Element.GrpKey,
    UserId = table2Element.UserId,
},
table1Element => new                   // from every element of Table1 take properties ...
{
    GrpKey = table1Element.GrpKey,
    UserId = table1Element.UserId,
},

// parameter resultSelector: take the element of Table2,
// with all zero or more elements of Table1 with the same key
// to make one new resulting element:
(table2Element, table1ElementsWithSameKey) => new
{
    GrpKey = table2Element.GrpKey,
    GrpName = table2Element.GrpName,

    Count = table1ElementsWithSameKey.Count(),
});

Simple comme bonjour!

Full join in Linq

Issues With TriedQuery1:

2 Answers2