LINQ statement to list orders with outstanding items to be shipped

user_11829861 0
Viewed 77

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.

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.

Thank you

sql-server linq c#
1 Answers

What the exception is telling you is that EF cannot translate the linq '.Sum(x => x.Qty) ' to SQL.

In this case, you have to make a decision, either

there is not and will never be that much data and you bring the entire related table entities back and compute it in your .NET runtime - doing it locally in multiple steps

there is too much data to bring back and you are better off writing a SPROC in SQL Server that does the computation for you and bringing only the results that back to the server where your .NET is running and you are left to do only the comparison in your .NET runtime.

user_1272570
Related