SQL to LINQ C# with join max and group

Question

How can I write the following query in LINQ.

select 
    a.IntId, Max(b.CreateDate), Max(a.TwoID) 
from 
    TableA a 
join 
    TableB b on a.IntId = b.IntId 
group by 
    IntId

I've got this currently but it doesn't work:

var res = DbContext.TableA
                   .Include(c => c.TableB)
                   .Select(p => new { CreateDate = p.TableA.CreateDate, IntId = p.IntId, TwoID = p.TwoID })
                   .GroupBy(x => x.IntId)
                   .Select(grp => grp.OrderByDescending(x => x.CreateDate)
                   .First()).ToList();

This post might help you: [Join/Where with LINQ and Lambda](https://stackoverflow.com/questions/2767709/join-where-with-linq-and-lambda/2767742#2767742) — rwpk9, May 12 '21 at 09:32
What's the meaning of "doesn't work"? And which type of LINQ are you using? EF/EF-core, some other O/R mapper, and which version? — Gert Arnold, May 12 '21 at 09:33

score 1 · Answer 1 · answered May 12 '21 at 09:47

Your identifiers are not very meaningful. You did this probably for the question, but it makes it fairly difficult to grasp the relation between TableA and TableB.

It seems to me that there is a one-to-many relation between TableA and TableB: every row from TableB has zero or more rows from TableA; every row from TableA "belongs" to exactly one row from TableB, namely the row that foreign key IntId refers to.

From every TableB row with its TableA items, you want to fetch TableB.IntId, TableB.CreateDate and the largest TableA.TwoId of all TableA rows that belong to this TableB.

Whenever you need to fetch "items with their sub-items" from a one-to-many relation, like Schools with their Students, Customer with their Orders, or "TableB rows with their TableA rows", consider to use one of the overloads of Queryable.GroupJoin.

Because I don't just want some special results, not just "TableB rows with their TableA rows", I use the overload that has a parameter resultSelector:

IQueryable<TableARow> tableA = ...
IQueryable<TableBRow> tableB = ...

var result = tableB.GroupJoin(tableA,   // GroupJoin TableB with TableA

    tableBRow => tableBRow.IntId,       // from every row in tableB take the IntId
    tableARow => tableARow.IntId,       // from every row in tableA take the foreign key

    // parameter resultSelector: from every row in tableB, with its zero or more 
    // matching rows from tableA, make one new object:
    (tableBRow, matchingTableARows) => new
    {
        IntId = tableBRow.IntId,
        CreateDate = tableBRow.CreateDate,

        LargestTwoId = matchingTableARows.Select(tableARow => tableARow.TwoId).Max(),
    })

In words: from every row from tableB, take the value of IntId. Then find all rows from tableA that have a matching IntId value. From every tableB row, with all its zero or more tableA rows, create one new object with the following three properties:

IntId is the IntId of the tableBRow (which, by the ways equals the IntId of all matching tableARows)
CreateDate is the CreateDate of the tableBRow
LargestTwoId is calculated as follows:

from every tableARow that has a value for IntId that matches the tableBRow, take only the value of property TwoId. From all these TwoId values take the largest one.

I chose to use Max instead of OrderByDescending, because it is a waste of processing power to Order all elements, if you will be using only the first one.

There is a small flaw with this: Max only works if there is at least one element. If you think there are some tableBRows that have no matching tableARow, then this won't work.

LargestTwoId = matchingTableARows.Select(tableARow => tableARow.TwoId)
    .OrderByDescending(twoId => twoId)
    .FirstOrDefault(),

or:

LargestTwoId = matchingTableARows.Any() ?
   matchingTableARows.Select(tableARow => tableARow.TwoId).Max() :
   null,

SQL to LINQ C# with join max and group

1 Answers1