Retrieve the latest row in a group using GROUP BY

Question

I'm trying to retrieve the latest row in a group base on the CreatedDate field.

How can this query be rewritten for EF Core 5 in a way which is not going to throw this Exception?

".OrderByDescending(s => s.CreatedDate)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable'"?

Inserting AsEnumerable works, but this would be terrible solution.

var entries = _dbContext.MyTable
                    .GroupBy(s => s.Something)
                    .Select(g => g.OrderByDescending(s => s.CreatedDate).First())
                    .ToListAsync();

What are you trying to do? Retrieve the latest date per group? That's `Max`. Or retrieve the first/last row per group? You can't do that with `GROUP BY` in SQL, the language itself — Panagiotis Kanavos, Sep 17 '21 at 11:56
no, not just a date but whole table rows (entries). Table has many columns this is just simplified example. — Milos, Sep 17 '21 at 11:59
You can't do that in SQL, the language, with `GROUP BY`. Grouping only returns aggregates from the group. EF Core can't do what the language itself can't. You'd need to use `ROW_NUMBER() OVER(PARTITION BY something ORDER BY CreatedDate desc)` to calculate an order for the items in a group and retrieve the first one. — Panagiotis Kanavos, Sep 17 '21 at 12:00
Unfortunately EFC 5.0 is no different than EFC 3.x in that regard, hence the duplicate. Will see how it goes with EFC 6.0 when released. — Ivan Stoev, Sep 17 '21 at 16:30

score 0 · Answer 1 · answered Sep 17 '21 at 13:20

Faster solution is via ROW_NUMBER function. But you can create workaround:

var mainQuery = _dbContext.MyTable;

var query =     
    from d in mainQuery.Select(s => new {s.Something}).Distinct()
    from x in mainQuery.Where(x => x.Something == d.Something)
        .OrderByDescending(x.CreateDate)
        .Take(1)
    select x;

var result = await query.ToListAsync();

Retrieve the latest row in a group using GROUP BY

1 Answers1