用EF Core检索每组的最后一条记录

Question

我试图做检索最后记录在每个组就像它在这里

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

，但在实体框架核心。基于链接上的内容，我可以使用

让SQL工作以带回正确的数据

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

或

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

这是我从那个链接

得到的最接近的结果

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

返回这个错误信息，所以它肯定不能工作

LINQ表达式'GroupByShaperExpression:rnKeySelector: l.r usermodelid， rnElementSelector:EntityShaperExpression: rn EntityType: LocationModelrn ValueBufferExpression: rn ProjectionBindingExpression: EmptyProjectionMemberrn IsNullable: Falsernrn .FirstOrDefault(w =>w.r modelid == GroupByShaperExpression:rn KeySelector: l.r usermodelid， rn ElementSelector:EntityShaperExpression: rn EntityType: LocationModelrn ValueBufferExpression: rn ProjectionBindingExpression: EmptyProjectionMemberrn IsNullable: Falsernrn max (m =>m.UserModelId))'无法翻译。要么以可翻译的形式重写查询，要么通过插入对'AsEnumerable'， 'AsAsyncEnumerable'， 'ToList'或'ToListAsync'的调用显式切换到客户端求值

Answer 1

这个查询应该会得到您想要的输出。它不如SQL +窗口函数那么快，但应该是可以接受的。

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();