在LINQ中使用groupby后的其他操作不起作用？

jvlzgdj9 于 2022-12-06 发布在其他

关注(0)|答案(1)|浏览(168)

I have two tables
Schoolattempts
| AttemptId | UserID | Rating |
| ------------ | ------------ | ------------ |
| 1 | 1 | 15 |
| 2 | 1 | 20 |
Aspnetusers
| UserId | FirstName | LastName |
| ------------ | ------------ | ------------ |
| 1 | ... | ... |
| 2 | ... | ... |
I want to get from a database (mysql) the best ratings of all users with their fullnames.
Here is my 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
}

But EF Core couldn't translate it to SQL;
Here is the error:

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.

linq

来源：https://stackoverflow.com/questions/74213446/using-other-operations-after-groupby-in-linq-doesnt-work

1条答案

按热度按时间

iq0todco1#

您需要记住，一旦执行GroupBy语句，它将在键下对多行进行分组。
这里有两个例子，我认为会给予你想要的结果，但设置，以便它更容易理解发生了什么。
获取包含评分总和的列表：

Schoolattempts
  .Join(
    Aspnetusers,
    x => x.UserID,
    y => y.UserID,
    (x, y) => new {
      Name = y.LastName + " " + y.FirstName + " " + y.MiddleName,
      Id = x.UserID,
      Rating = x.Rating
    })
  .GroupBy(x => x.User) //Grouping the data
  .Select(x => new { 
    Name = x.First().Name, 
    Rating= x.Sum(y => y.Rating) //Getting the sum of the ratings of the user
  })
  .OrderByDescending(x => x.Rating) //Order the result from highest to lowest rating

获取每个用户的最高评分列表而不是总和，最后一个选择块将如下所示：

.Select(x => new {
    Name = x.First().Name, 
    Rating= x.Max(y => y.Rating) //Getting the highest rating of the user
  })

赞(0）回复(0）举报 2022-12-06

我来回答

在LINQ中使用groupby后的其他操作不起作用？

1条答案

相关问题

热门标签

最新问答