Linq most efficient top results

Question

I'm wondered I have a table with IDs and a version and a remove field. I d like to return the the top 20 records grouped by ID and for ech ID take only the highest version unless remove is set then ignore removed records. Then return a descending record set.

There are a few ways todo it with Linq but I wonder is there a most efficient way, are there patterns to avoid?.

...
    .OrderByDescending(x=>x.id)
    .GroupBy(x=>x.id)
    .SelectMany(y=>y.Where(x=>x.Version == y.Max(y=>y.Version)))
    .Where(x=>x.Remove=false)
    .Take(20)

Answer 1

One of then possible workarounds when using EF Core. I'm calling it workaround because with SQL and Window functions we can create more effective query.

var itemsQuery = ctx.SomeTable
    .Where(x => x.Remove = false);
    
var query =
    from d in itemsQuery.Select(d => new { d.id }).Distinct()
    from x in itemsQuery.Where(x => d.Id == x.Id)
        .OrderByDescending(x => x.Version)
        .Take(1)
    select x;

query = query.Take(20);

Similar queries when using EF Core 6:

var query = ctx.SomeTable
    .Where(x => x.Remove = false)
    .GroupBy(x => x.Id)
    .Take(20)
    .SelectMany(g => g.OrderByDescending(x => x.Version).Take(1));

var query = ctx.SomeTable
    .Where(x => x.Remove = false)
    .GroupBy(x => x.Id)
    .Select(g => g.OrderByDescending(x => x.Version).First());
    .Take(20);