Retrieving the last record in each group with EF Core

Question

I'm trying to do the retrieve the last record in each group like it does here

https://stackoverflow.com/a/20770779/4789608

but in Entity Framework Core. Based on what's on the link, I can get the SQL to work to bring back the correct data using

select * 
from [Location] 
where [LocationModelId] in (select max([LocationModelId]) 
                            from [Location] 
                            group by [UserModelId])

or

select m1.* 
from [Location] m1 
left outer join [Location] m2 on (m1.[LocationModelId]< m2.[LocationModelId] 
                              and m1.[UserModelId] = m2.[UserModelId])

This is the closest I've gotten based on that link

locationDetails = _context.Location
    .GroupBy(p => p.UserModelId)
    .Select(p => p.FirstOrDefault(w => w.UserModelId == p.Max(m => m.UserModelId)))
    .OrderBy(p => p.DateCreated)
    .ToList();

which returns this error message so, it's definitely not working.

The LINQ expression 'GroupByShaperExpression:\r\nKeySelector: l.UserModelId, \r\nElementSelector:EntityShaperExpression: \r\n EntityType: LocationModel\r\n ValueBufferExpression: \r\n ProjectionBindingExpression: EmptyProjectionMember\r\n IsNullable: False\r\n\r\n .FirstOrDefault(w => w.UserModelId == GroupByShaperExpression:\r\n KeySelector: l.UserModelId, \r\n ElementSelector:EntityShaperExpression: \r\n EntityType: LocationModel\r\n ValueBufferExpression: \r\n ProjectionBindingExpression: EmptyProjectionMember\r\n IsNullable: False\r\n\r\n .Max(m => m.UserModelId))' 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', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'

This would probably be supported in EFC 6.0. For EFC up to 5.0 use something like this https://stackoverflow.com/questions/59456026/how-to-select-top-n-rows-for-each-group-in-a-entity-framework-groupby-with-ef-3/59468439#59468439 — Ivan Stoev, Sep 28 '21 at 04:15
You can do it with the help of some extension: [EF Core Tools & Extensions](https://learn.microsoft.com/en-us/ef/core/extensions/) — Alexander Petrov, Sep 28 '21 at 04:35
I would used the pattern `.Where(r => r.value == r.Parent.Children.Max(c => c.value))`. — Jeremy Lakeman, Sep 28 '21 at 06:05

score 1 · Answer 1 · answered Sep 28 '21 at 07:48

This query should get you desired output. It is not fast as just SQL with window functions, but it should be acceptable.

var unique = _context.Location
    .Select(x => new {x.UserModelId})
    .Distinct();

var query =
    from u in unique
    from l in _context.Location
        .Where(x => x.UserModelId == u.UserModelId)
        .OrderByDescending(x.DateCreated)
        .Take(1)
    select l;

var locationDetails = query.ToList();

Retrieving the last record in each group with EF Core

1 Answers1

Linked