How to do select multiple records based on max version using Entity Framework

Question

I have records such like this

id  name     number version
---------------------------
1   NewYork     1     1 
2   LosAngeles  1     2 
3   Seatle      1     3
4   Toronto     2     1
5   Ottawa      2     2

I want to select only the records with highest version within the same number

So I wrote query like this

SELECT * 
FROM city c 
WHERE c.[version] = (SELECT Max([version])
                     FROM [city] c2 
                     WHERE c2.number = c.number)

and it would return

id  name     number version      
---------------------------
3   Seatle      1     3
5   Ottawa      2     2

How do I write that in linq with Entity Framework?

db.cities.where(c => c.version == (????))

I don't know how Entity Framework would work for this.

Answer 1

Use GroupBy to group by the number and then OrderBy the record with the highest version:

var result = db.cities.GroupBy(item => item.number)
                      .Select(grouping => grouping.OrderByDescending(item => item.version)
                                                  .First());

Answer 2

var maxVersionCities = db.cities
    .GroupBy(c => c.number)
    .Select(grp => grp
        .OrderByDescending(c => c.version)
        .First())
    .SelectMany(grp => grp);