ef core linq query: latest record in group

by

I wish to get the latest record in a group using EF core.
Sadly the transformed query I got was way to complex for what I intended.
For the sake of simplicity lets I have a table of Persons containing three properties:

public Person
{
    public string Name {get;set;}
    public string Address {get;set;}
    public int Iteration {get;set;}
}

I wish to get from the DB the person with the name Bob (there can be many of those) but with the latest iteration.

The query I used in EF core is:

context.Persons
    .Where(p=> p.Name == "Bob")
    .GroupBy(group => group.Name)
    .Select(group => group.OrderByDecending(p => p.Iteration).FirstOrDefault())
    .ToList();

Is there an easier way to do it? using max or some other function?
The resulting query after translation has 3 selects and a join.

  • 1

    You added tags for three different ORMs, please select the one you actually use. That said, the LINQ query you show is the way to do it.

    – 




  • 1

    EFCore constructs the query in layers and wraps a later layer around an earlier layer. EFCore SQL may not be very easy to read, but the SQL compilers don’t care how it looks. If you want SQL for humans, you’ll need to write it yourself.

    – 

  • 1

    Usually such query is done via SQL with ROW_NUMBER window function. Window functions are not supported by EF Core. So, you cannot optimize that with pure EF Core translator, use FromSql instead.

    – 

You need last iteration of “Bob” person name. This is your code:

context.Persons
.Where(p=> p.Name == "Bob")
.GroupBy(group => group.Name)
.Select(group => group.OrderByDecending(p => p.Iteration).FirstOrDefault())
.ToList();

You don’t need to group the selection, because you already have only persons with “Bob” name after Where condition. Just use OrderBy and LastOrDefault or OrderByDescending and FirstOrDefault:

 var bobPerson = await context.Persons
    .Where(p => p.Name == "Bob")
    .OrderBy(p => p.Iteration)
    .LastOrDefaultAsync();

If you want get last iteration for all of names – you need grouped user:

var listLastIterationPerson = await context.Persons
        .GroupBy(p => p.Name)
        .Select(pg => pg.OrderBy(p => p.Iteration).LastOrDefault())
        .ToListAsync();

Leave a Comment