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

c# entity-framework-core linq-to-entities

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?

1
0
4/26/2019 12:19:00 AM

Accepted Answer

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.

1
4/26/2019 12:18:00 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow