EFCore Linq: Select only the records with the max value of a column

Question

I am using Entity Framework Core (lambda syntax). This seems like such an easy thing to do, but it is eluding me for some reason.

Lets say I have a simple table like this:

Run  Result
1    result1
1    result2
2    result3
2    result4    

All I want to do is grab the rows with the max run value.

So the result should be:

2    result3
2    result4

Basically group by the Run and get the group with the max run value and return all the rows. Maybe I'm thinking about that the wrong way?

Answer 1

Group by won't work (no natural SQL translation).

However some sort of self join will do.

For instance

db.Runs.Where(e => e.Run == db.Runs.Max(e2 => (int?)e2.Run))

i.e. take the records with max Run (see How to translate this SQL query to a LINQ query in EF Core? for why nullable cast is needed)

Or

db.Runs.Where(e => !db.Runs.Any(e2 => e2.Run > e.Run))

i.e. take records where no record with greater Run exists.