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?
Group by won't work (no natural SQL translation).
However some sort of self join will do.
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)
db.Runs.Where(e => !db.Runs.Any(e2 => e2.Run > e.Run))
i.e. take records where no record with greater