EF Core 3.0 SumAsync触发聚合功能异常 [英] EF Core 3.0 SumAsync triggers aggregate function exception

问题描述

我正在升级到EF Core 3.0和.NET Core 3.0，但是我的某些查询停止了工作.这是一个示例:

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:

我有一个名为Bins的表，我还有另一个名为BinItems的表，现在它当然具有一对多的关系了. BinItems具有一个称为Qty的属性，我想根据客户端在过滤器中给出的条件来总结BinItems中的所有Qty.

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.

下面是代码:

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

我抛出异常:

Microsoft.Data.SqlClient.SqlException(0x80131904):无法执行 包含集合或表达式的表达式上的集合函数 子查询.

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

它在.NET Core 2.1和EF Core 2中完全可以正常运行，但是现在我在所有以这种方式进行的查询中都遇到这些错误.

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.

有什么想法可以在.NET Core 3.0/EF Core 2中获得这项功能吗?

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

推荐答案

问题是嵌套聚合(在本例中为Sum的Sum). EF Core 3.0仍然无法正确转换此类聚合.它很可能在3.0之前的版本中与客户评估一起使用，而该评估已在3.0中删除.

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.

解决方案与往常一样，是避免嵌套聚合，并在展平的(通过SelectMany)集上执行单个聚合.它适用于除Average以外的所有标准分组聚合.

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.

这是有问题的查询的解决方案(请注意，由于查询是在服务器端执行的，因此Include是不必要的):

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触发聚合功能异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！