LINQ select row with max value after group by

Question

I have following sql statement:

Select 
   tsl.Transaction_Id, 
   tsl.State_Id,
   MAX(tsl."Timestamp") 
from TransactionStatesLog tsl 
group by tsl.Transaction_Id

How this statement can be translated to LINQ? I just want to select the whole row, where Timestamp is maximum of the group.

With this code i am able just to select TransactionId and max Timestamp from the group.

var states = (from logs in _context.TransactionStatesLog
              group logs by new { logs.TransactionId } into g
              select new
              {
                  TransactionId = g.Key,
                  Timestamp = g.Max(x => x.Timestamp)
              }).ToList();

I am working with ef core 3.1

I cant see how that SQL would work since `tsl.State_Id` is not part of the group by. — Magnus, Oct 27 '20 at 15:03
This is Sqlite right? It allows fields in the Select that are on the same aggregation level as the grouping field(s). But LINQ and EF's translation won't allow that, so you have to add `State_Id` to the grouping key, as answered. — Gert Arnold, Oct 27 '20 at 15:07

Selim Yildiz · Answer 1 · 2020-10-27T15:07:46.093

Assuming you forgot to add tsl.State_Id to your SQL as grouping key as follows(otherwise that SQL does not work either):

Select 
   tsl.Transaction_Id, 
   tsl.State_Id,
   MAX(tsl."Timestamp") 
from TransactionStatesLog tsl 
group by tsl.Transaction_Id, tsl.State_Id

If I understood you correctly you need to add StateId to grouping statement as well so that you will be able to select StateId and TransactionId.

So this should work:

var states = (from logs in _context.TransactionStatesLog
              group logs by new { logs.TransactionId, logs.StateId } into g
              select new
              {
                  TransactionId = g.Key.TransactionId,
                  StateId = g.Key.StateId,
                  Timestamp = g.Max(x => x.Timestamp)
              }).ToList();

See: Group by with multiple columns using lambda

LINQ select row with max value after group by

1 Answers1