I have two tables

Schoolattempts

Aspnetusers

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.

CodePudding user response：

You need to remember that once you perform a GroupBy statement, it will group multiple rows under the key.

Here's two examples I think will give you the desired result, but set up so it's hopefully easier to comprehend what's going on.

Getting the list with a sum of ratings:

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

Getting the list with each user's highest rating instead of a sum, the last select block would instead look like this:

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