EF Core 3.0 SumAsync triggers aggregate function exception

Question

I am in the process of upgrading to EF Core 3.0 and .NET Core 3.0, but some of my queries stopped working. Here is an example:

I have a table called Bins, I have another table which is called BinItems, now it has, of course, a one to many relationship. BinItems has a property called Qty, and I want to sum up all the Qty from BinItems based on criteria given by the client in a filter.

So here is the code:

var query = _binRepository.Table;


if (filter.LastRecountDate != null) {
    query = query.Where(x => x.LastRecountDate.Date == filter.LastRecountDate.Value.Date);
}

if (filter.StartRecountDate != null) {
    query = query.Where(x => x.LastRecountDate.Date >= filter.StartRecountDate.Value.Date);
}

if (filter.EndRecountDate != null) {
    query = query.Where(x => x.LastRecountDate.Date <= filter.EndRecountDate.Value.Date);
}

if (filter.Active != null) {
    query = query.Where(x => x.Active == filter.Active);
}

if (!string.IsNullOrEmpty(filter.BinLocation)) {
    query = query.Where(x => x.BinLocation == filter.BinLocation);
}

if (!string.IsNullOrEmpty(filter.Gtin)) {
    query = query.Where(x => x.BinItems.Any(o => o.UPC == filter.Gtin));
}

if (filter.WarehouseIds.Count() > 0) {
    query = query.Where(x => filter.WarehouseIds.Contains(x.Zone.Id));
}

if (!string.IsNullOrEmpty(filter.Keywords)) {
    query = query.Where(x => x.BinItems.Select(o => o.UPC).Contains(filter.Keywords));
}

query = query.Include(x => x.BinItems).Include(x => x.Zone);

if (!string.IsNullOrEmpty(filter.Keywords)) {
    return await query.SumAsync(x => x.BinItems.Where(p => p.UPC.Contains(filter.Keywords)).Sum(o => o.Qty));
}

return await query.SumAsync(x => x.BinItems.Sum(o => o.Qty));

I get an exception thrown:

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

It worked perfectly OK in .NET Core 2.1 and EF Core 2, but now I keep getting these errors in all my queries that I do this way.

Any idea how I can get this work in .NET Core 3.0/EF Core 2?

Its probably because they got rid of client sided evaluation and your query was being evaluated locally in 2.1 and now its just outright failing. Try run it against the 2.1 runtime and see if it is to confirm. — Kieran Devlin, Nov 14 '19 at 16:55
See also this question + you explanation : https://stackoverflow.com/questions/58092869/ef-core-2-2-linq-query-not-working-in-ef-core-3-0 the solution for your issue here is probably to use groupby. — Stef Heyenrath, Nov 14 '19 at 17:05
so i decided to go the other way around, starting off with the binItems table with an include for the Bins, and the sum is now for the binItems and this works now. — Sol Stein, Nov 14 '19 at 18:08

score 4 · Accepted Answer · answered Nov 14 '19 at 18:32

The problem is nested aggregate (in this case, Sum of Sum). EF Core 3.0 still is unable to translate such aggregates properly. Most likely it worked in pre 3.0 with client evaluation which has been removed in 3.0.

The solution is as usual to avoid the nested aggregate and perform single aggregate on the flattened (via SelectMany) set. It works for all standard grouping aggregates except Average.

Here is the solution for the query in question (note that the Includes were unnecessary because the query is performed server side):

var query = _binRepository.Table;
// ... (query filters)

var innerQuery = query.SelectMany(x => x.BinItems);

if (!string.IsNullOrEmpty(filter.Keywords)) {
    innerQuery = innerQuery.Where(x => x.UPC.Contains(filter.Keywords));
}

return await innerQuery.SumAsync(x => x.Qty);

EF Core 3.0 SumAsync triggers aggregate function exception

1 Answers1

Linked