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):无法执行包含聚合或 a 的表达式上的聚合函数子查询.

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屋！