LINQ中GroupBy后执行操作无法被EF Core转换为SQL的问题

数据表结构 #

Schoolattempts表 #

Aspnetusers表 #

原LINQ代码 #

from attempts in (from q in Schoolattempts
            group q by q.UserId into g
            select g.OrderByDescending(c => c.Rating).First())
join users in Aspnetusers on attempts.UserId equals users.Id
select new
{
    FullName = users.LastName + " " + users.FirstName + " " + users.MiddleName,
    Rating = attempts.Rating
}

错误信息 #

InvalidOperationException: The LINQ expression 'DbSet<Schoolattempts>()
    .GroupBy(s => s.UserId)
    .Select(g => g
        .AsQueryable()
        .OrderByDescending(e => e.Rating)
        .First())
    .Join(
        inner: DbSet<Aspnetusers>(), 
        outerKeySelector: e0 => e0.UserId, 
        innerKeySelector: a => a.Id, 
        resultSelector: (e0, a) => new TransparentIdentifier<Schoolattempts, Aspnetusers>(
            Outer = e0, 
            Inner = a
        ))' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

EF Core对GroupBy后直接调用OrderByDescending().First()的转换支持有限，可通过以下方式改写查询：

方法一：先分组取最高评分再关联 #

先通过分组得到每个用户的最高评分，再关联用户表和对应的记录，避免直接在分组后取实体：

from user in Aspnetusers
join attempt in Schoolattempts on user.Id equals attempt.UserId
join maxRating in (
    from q in Schoolattempts
    group q by q.UserId into g
    select new { UserId = g.Key, MaxRating = g.Max(r => r.Rating) }
) on new { attempt.UserId, attempt.Rating } equals new { maxRating.UserId, maxRating.MaxRating }
select new
{
    FullName = user.LastName + " " + user.FirstName + " " + user.MiddleName,
    Rating = maxRating.MaxRating
}

方法二：使用窗口函数（EF Core 3.0+支持） #

利用ROW_NUMBER()窗口函数为每个用户的评分排序，取排名第一的记录后关联用户表：

from attempt in (
    from q in Schoolattempts
    select new
    {
        q.UserId,
        q.Rating,
        RowNum = EF.Functions.RowNumber().Over(
            partitionBy: q.UserId,
            orderBy: q.Rating descending
        )
    }
)
where attempt.RowNum == 1
join user in Aspnetusers on attempt.UserId equals user.Id
select new
{
    FullName = user.LastName + " " + user.FirstName + " " + user.MiddleName,
    Rating = attempt.Rating
}

方法三：拆分分组与关联逻辑 #

先单独获取用户最高评分集合，再依次关联评分表和用户表：

var maxRatings = from q in Schoolattempts
                 group q by q.UserId into g
                 select new { UserId = g.Key, MaxRating = g.Max(r => r.Rating) };

var result = from mr in maxRatings
             join a in Schoolattempts on new { mr.UserId, mr.MaxRating } equals new { a.UserId, a.Rating }
             join u in Aspnetusers on mr.UserId equals u.Id
             select new
             {
                 FullName = u.LastName + " " + u.FirstName + " " + u.MiddleName,
                 Rating = mr.MaxRating
             };

内容的提问来源于stack exchange，提问作者Saidnazar Saidnazarov