I am having a problem creating a LINQ statement to select orders where there are items remaining to be shipped.
Simplified model as follows:
SalesOrder
SalesOrderId (PK)
SalesOrderItem
SalesOrderItemId (PK)
Description (string)
SalesOrderId (FK)
ProductId(FK)
Qty (int)
Product
ProductId (PK)
Description
Virtual (Bit)
Shipment
ShipmentId (PK)
ShipmentItem
ShipmentItemId (PK)
SalesOrderItemId (FK)
Qty (int)
I need a LINQ statement which generates a list of sales orders where there is an outstanding qty of items to be shipped (taking into account the fact that virtual items do not need to be shipped).
A Sales Order can have multiple shipments related to it. Each shipment item relates to a sales order item.
The Qty of the Shipment Item relates to the qty of the Sales Order Item. It can be for all of the Sales Order Item or only part.
A shipped order is one where all Order Items are shipped (having total qty ordered = total qty shipped). Outstanding Sales Orders still have an outstanding qty left to ship.
I have no idea where to start comparing a sum of a qty from one table to another in a clear, concise and economical LINQ statement.
Thank you
What I have tried:
This is the SQL which achieves my goal:
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
I have also tried the following EF statement:
var salesOrders = await _context.SalesOrder.Where(p => p.SalesOrderItems.Sum(x => x.Qty) > p.SalesOrderItems.Sum(i => i.ShipmentItems.Sum(f => f.Qty))).ToListAsync();
This results in the error: SqlException: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery.