Select max as second criteria with Linq

Question

i'm trying to get all rows who has some field = max of that field

to be able to explain my doubt, I will leave an example of model

class model
{
    [Key]
    [Column(Order=0)]
    public int Key { get; set; }     

    [Key]
    [Column(Order=1)]
    public int Revision { get; set; }

    public string OtherStuff { get; set; }    
}

A data example could be

|Key|Revision   |Otherstuff |
|1  |1          |A          |
|1  |2          |B          |
|1  |3          |C          |
|2  |1          |D          |
|2  |2          |E          |
|3  |1          |F          |

What i want to get:

|Key|Revision   |Otherstuff |
|1  |3          |C          |
|2  |2          |E          |
|3  |1          |F          |

I try the solutions i could find here but i couldn't make it work

This it what i got

IQueryable<model> data = dbContext.model;

data = data.GroupBy(x => x.Revision ).OrderByDescending(x => x.Key).SelectMany(x => x)

//Applying logic to filter the data 
data = data.Where(...)

My main problem is that it is a filtering method, and when using SelectMany the database is disposed and I can not continue modifying the list as I need

How i supose to do it?

Thanks!

Solved

data = data.GroupBy(x => x.Key).Select(x => x.OrderByDescending(d => d.Revision).FirstOrDefault());

Thanks all responses!

Answer 1

var result = data.GroupBy(x => x.Revision).Select(x => x.First())

if you try to do any other operations after the first query (such as select a subset of columns) - you need to use x.FirstOrDefault