c# - 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?

Answer 1

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);