Click here to Skip to main content
14,932,737 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:


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


I have also tried the following EF statement:

SQL
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.
Posted
Updated 10-Mar-21 6:23am
v2
Comments
Maciej Los 6-Mar-21 16:09pm
   
Are you using Entity Framework?
Jörgen Andersson 8-Mar-21 10:23am
   
I would suspect so, since he refers to "I have also tried the following EF statement"
Gerry Schmitz 7-Mar-21 14:03pm
   
Do it in steps (intermediate queries). Sum shipped. Subtract shipped from ordered; which leaves: outstanding orders. BTW, in a "production system", one "posts" the shipped amounts to the "order items"; you treat the "sales / AR" systems as "accounting ledgers". This way, you don't keep "recalculating".
BillWoodruff 7-Mar-21 23:09pm
   
describe the structure of your tables.

If you're using entity framework and already have a working SQL-query you don't NEED to convert to linq.

You can execute a raw query instead: Execute Raw SQL Query in Entity Framework 6[^]
Alternatively you can wrap the query in a stored procedure and call that instead: Use Stored Procedure in Entity Framework[^]

Just one thing, make it a habit to not use SELECT * in production.
   
v2
Try something like this:
C#
var salesOrders = await _context.SalesOrder
    .Where(p => p.SalesOrderItems.Sum(x => x.Qty) > p.SalesOrderItems.SelectMany(i => i.ShipmentItems).Select(f => f.Qty).DefaultIfEmpty().Sum())
    .ToListAsync();
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900