EF Core Groupby Get highest value of row

Question

I have the following table:

| Id | ParentId | Version |
---------------------------
|  1 |    1     |    0    |
|  2 |    1     |    1    |
|  3 |    1     |    2    |
|  4 |    4     |    0    |
|  5 |    4     |    1    |
|  6 |    4     |    2    |

I am looking for a query (pref Linq to EF) where I can select the highest Version per group of ParentIds.

I currently have something like this:

await _context.Actions.GroupBy(a => a.ParentId)
                      .Select(s => s.Max(a => a.Version))
                      .ToListAsync();

The only problem I have is that only the version row is returned but I'd like the whole row to be returned per group.

Answer 1

You would need to order by descending and take the first in each group

await _context.Actions
              .GroupBy(a => a.ParentId)
              .Select(s => s.OrderByDescending(a => a.Version).First())
              .ToListAsync();