编写LINQ语句查询存在待发货剩余数量的销售订单(解决EF聚合报错)
待发货销售订单LINQ筛选问题
简化业务实体模型
SalesOrder(销售订单主表)SalesOrderId:主键
SalesOrderItem(销售订单明细表)SalesOrderItemId:主键Description:商品描述SalesOrderId:关联销售订单主表外键ProductId:关联商品表外键Qty:订购数量
Product(商品表)ProductId:主键Description:商品描述Virtual:Bit类型,标记是否为无需发货的虚拟商品
Shipment(发货单主表)ShipmentId:主键
ShipmentItem(发货单明细表)ShipmentItemId:主键SalesOrderItemId:关联销售订单明细外键Qty:本次发货数量
核心需求
筛选出存在待发货剩余数量的销售订单,虚拟商品无需发货,不计入待发货统计范围。
业务规则说明
- 一张销售订单可关联多张发货单,每张发货单明细对应唯一一条销售订单项
- 支持分批次发货:单条销售订单项可拆分多次部分发货,无需一次性发完全部订购量
- 发货完成判定标准:所有需发货的订单项总订购数量 = 累计已发货数量;反之即为存在待发货的订单
现有参考实现
可正常运行的原生SQL
SELECT a.* FROM ( SELECT o.SalesOrderId, SUM(i.Qty) AS QtyOrdered, SUM(ISNULL(s.Qty,0)) AS QtyShipped FROM SalesOrder o INNER JOIN SalesOrderItem i ON i.SalesOrderId = o.SalesOrderId INNER JOIN Product p ON p.ProductId = i.ProductId LEFT JOIN ShipmentItem s ON s.SalesOrderItemId = i.SalesOrderItemId WHERE p.Virtual = 0 GROUP BY o.SalesOrderId ) a WHERE QtyOrdered > QtyShipped
运行报错的EF Core代码
var salesOrders = await _context.SalesOrder .Where(p => p.SalesOrderItems.Sum(x => x.Qty) > p.SalesOrderItems.Sum(i => i.ShipmentItems.Sum(f => f.Qty))) .ToListAsync();
报错信息
SqlException: 无法在包含聚合或子查询的表达式上执行聚合函数
问题原因
- 原有代码未添加
Virtual = false的过滤条件,统计范围包含了无需发货的虚拟商品,不符合业务逻辑 - 已发货数量的计算使用了嵌套聚合写法:外层对
SalesOrderItem做Sum时,内层又对ShipmentItem做Sum,EF Core会将其翻译为SUM(嵌套聚合子查询)的结构,SQL Server不支持在聚合函数的参数中嵌套使用聚合函数,因此抛出异常。
可直接运行的LINQ实现
以下写法可被EF Core正确翻译为和上述原生SQL逻辑一致的SQL语句,无嵌套聚合问题:
查询语法版
var salesOrders = await ( from order in _context.SalesOrder join orderItem in _context.SalesOrderItem on order.SalesOrderId equals orderItem.SalesOrderId join product in _context.Product on orderItem.ProductId equals product.ProductId where product.Virtual == false // 过滤无需发货的虚拟商品 group new { order, orderItem } by order.SalesOrderId into orderGroup let totalOrdered = orderGroup.Sum(g => g.orderItem.Qty) // 无发货记录时Sum返回null,用?? 0兜底 let totalShipped = orderGroup.SelectMany(g => g.orderItem.ShipmentItems) .Sum(s => (int?)s.Qty) ?? 0 where totalOrdered > totalShipped select orderGroup.First().order ).ToListAsync();
方法语法等价版
var salesOrders = await _context.SalesOrder .Select(order => new { Order = order, // 先过滤出需要发货的有效订单项 ValidItems = order.SalesOrderItems.Where(item => item.Product.Virtual == false) }) .Where(x => x.ValidItems.Sum(item => item.Qty) > // 汇总所有有效订单项的累计发货量,空值兜底为0 x.ValidItems.SelectMany(item => item.ShipmentItems) .Sum(shipItem => (int?)shipItem.Qty) ?? 0) .Select(x => x.Order) .ToListAsync();
内容的提问来源于stack exchange,提问作者steve_c123
相关产品推荐
相关产品推荐

