Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

c# - EF Core 3.0 SumAsync triggers aggregate function exception

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...