0

Accessing a table with values: Id ImageId Score . Need to get the highest score of each simular ImageId.

Ex:

1 a1 10 points
2 a1 30 points
3 b1 15 points

Want: a1 30 points and b1 15 points

Code used:

public async Task<List<HistoryEntry>> Handle(Request request, CancellationToken cancellationToken)
                => await _db.History.GroupBy(a => a.ImageId).Select(s => s.OrderByDescending(x => x.Score).First()).ToListAsync();
        }

Error:

.OrderByDescending(x => x.Score)' 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.

InvalidOperationException: The LINQ expression 'GroupByShaperExpression: KeySelector: h.ImageId, ElementSelector:EntityShaperExpression: EntityType: HistoryEntry ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False .OrderByDescending(x => x.Score)' 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.
Marsh Mellow
  • 17
  • 4
  • Hopefully this functionality is coming with EF Core 6.0 which should be released soon. Until then, use the workarounds from the linked posts, in your case something like `_db.History.Select(a => a.ImageId).Distinct().SelectMany(key => _db.History.Where(x => x.ImageKey == key).OrderByDescending(x => x.Score).Take(1))` – Ivan Stoev Nov 06 '21 at 11:33

1 Answers1

1

The easiest way in your code is to change order:

var groupedResult  = await _db.History.GroupBy(a => a.ImageId).ToListAsync();
return groupedResult.Select(s => s.OrderByDescending(x => x.Score).First());

EF cannot translate such nested queries into SQL - hence your error. In this solution grouped result is materialized in memory, so such nested queries won't cause issues. Be careful, though - this is surely not optimal solution if there are lots of records in DB, but in few records case it will do its work.

Piotr Wojsa
  • 938
  • 8
  • 22