6

Basically I want to do the same thing as here: https://stackoverflow.com/a/16274992/4138686 , but using this answer in EF Core 3.1.3 gives me

System.InvalidOperationException: The LINQ expression '(GroupByShaperExpression: KeySelector: (r.Price), ElementSelector:(EntityShaperExpression: EntityType: RetailerRate ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) ) .OrderByDescending(r => r.CreatedAt)' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Here is the code:

await _db.RetailerRates
                .GroupBy(r => r.Price)
                .Select(g => g.OrderByDescending(r => r.CreatedAt).First())
                .ToListAsync();

Is there a way to retrieve the first element based on a date from every group in EF Core 3.1?

Rufus L
  • 36,127
  • 5
  • 30
  • 43
Reath
  • 491
  • 5
  • 16
  • Have you considered `ToList`ing _before_ the `GroupBy` like the error suggests? – mjwills May 07 '20 at 01:32
  • 2
    @mjwills Not really an option because I can have 10k+ rows in RetailerRates. Query should be done in SQL Server – Reath May 07 '20 at 01:34
  • My app server eats 10k+ rows for breakfast. Did you try it? Was it fast enough? _I am not saying there isn't a better option - just suggesting you give it a try._ – mjwills May 07 '20 at 01:39
  • @mjwills I have not tried it. If there isn't an option to query the db for this data, I'll just create an in-memory storage containing the latest rates and with each addition, I'll replace the old one. Will do a better job – Reath May 07 '20 at 01:44
  • Try to replace .First() by .FirstOrDefault(), .First() usually cannot be translated to SQL – pakeha_by May 07 '20 at 02:03
  • .FirstOrDefault() was the first thing I tried, because of the answer that I copied from the question I linked. Neither work – Reath May 07 '20 at 02:22
  • 1
    Linked "duplicate" posts show workaround. Associated EF Core issues: [Support ability to select top N of each group #13805](https://github.com/dotnet/efcore/issues/13805) and [Translate GroupBy followed by FirstOrDefault over group #12088](https://github.com/dotnet/efcore/issues/12088) – Ivan Stoev May 07 '20 at 05:29

0 Answers0