I'm using EF core 3.1 and I have an Order entity that I want to get report by this code:
public async Task<List<OrderReport>> GetOrderReportAsync(DateTimeOffset @from, DateTimeOffset to,
OrderStatus? status, CancellationToken token)
{
var query = _dbContext.Orders.AsNoTracking()
.Where(x => x.StartAt >= from && x.StartAt <= to);
if (status.HasValue)
query = query.Where(x => x.Status == status);
return await query
.Select(x => new
{
x.StartAt,
x.Status,
TotalPrice = Convert.ToInt64(x.TotalPrice),
PayedPrice = Convert.ToInt64(x.PayedPrice),
})
.GroupBy(x => new
{
x.Status,
x.StartAt.Date
})
.Select(x => new OrderReport()
{
DateTime = x.Key.Date,
Timestamp = x.Key.Date.Ticks,
Total = x.Count(),
TotalPrice = x.Sum(s => s.TotalPrice),
TotalPayedPrice = x.Sum(s => s.PayedPrice),
Status = x.Key.Status
}).ToListAsync(token);
}
but in runtime EF throws an exception:
The LINQ expression '(ProjectionBindingExpression: 1)' 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().
I know what the exception says but don't want to get all records in memory and then set group by - I ran the same code on EF 2.1 before and worked with no exception.
Is there any problem with my code?