LINQ statement to list orders with outstanding items to be shipped

Question

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 6-Mar-21 9:08am

stevecurrey

Updated 10-Mar-21 6:23am

Herman<T>.Instance

v2

Add a Solution

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"

[no name] 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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Jörgen Andersson · Answer 1 · 2021-03-08T04:22:00

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.

Richard Deeming · Answer 2 · 2021-03-10T06:23:00

Solution 2

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();

Posted 10-Mar-21 6:23am

Richard Deeming