LINQ Sum Nested Collection with GroupBy and OrderByDescending

Question

I have:

public class Order { public string Customer; public List<OrderLine> OrderLines; } public class OrderLine { public decimal Quantity; public decimal UnitPrice; } 

My goal is to search the list of customer orders in descending order. (Amount == Adding each order Line of Quantity * UnitPrice)

Let's say we have 6 orders from 2 customers (Customer1, Customer2). Each client has 3 order orders.

I'm trying to:

 var result = Database.GetAllOrders().OrderByDescending(order => order.Lines.Sum(ol=>ol.Quantity*ol.UnitPrice)) .GroupBy(order=>order.CustomerName, order=>order.Lines.Sum(ol=>ol.Quantity*ol.UnitPrice), (customerName, amount) => new {Customer=customerName, Amount=amount}); 

but I get something like this:

Customer2:
- Amount: 78
- Amount: 89
- Amount: 12

customer1:
- Amount: 64
- Amount: 36
- Amount: 28

while I try to find:

Customer2:
- Amount 179

customer1:
- Amount 128

Any suggestion please?
Thank you very much!

Answer 1

It looks like you need to do grouping and summation before the order, and only a group by customer name (or, ideally, customer ID):

 var query = Database.GetAllOrders() .GroupBy(order => order.CustomerName) .Select(orders => new { Customer = orders.Key, Amount = orders.Sum(o => o.OrderLines.Sum( l => l.Quantity * l.UnitPrice)) }) .OrderByDescending(x => x.Amount); 

Answer 2

Jon's answer is correct, but if you intend to use this Amount property to order results and display, then why not turn it into a specific property?

 public class Order { public string Customer; public List<OrderLine> OrderLines; public decimal Amount { get { if (this.OrderLines == null) return 0; return this.OrderLines.Sum(o => o.Quantity * o.UnitPrice); } } } 

You still count it on the fly, but if you use it to order a collection and then want to display it, you probably want to use it for something else - save it as an access property.

That is why we have properties!

---

And to answer the question,

 var result = orders .GroupBy(o => o.CustomerName) .Select(g => new { CustomerName = g.Key, TotalAmount = g.Sum(o => o.Amount) }) .OrderByDescending(g => g.TotalAmount)); 

Answer 3

I know this is not a specific answer, but I had a familiar challenge and he did this:

 var elements = db.Orderheads .Where(o => o.OrderDate.Value >= start && o.OrderDate.Value <= end) .GroupBy(o => o.Employee) .Select(g => new { Employee = g.Key, OrdersCount = g.Count(), TotalAmount = g.Sum(o => o.TotalExVat), TotalQuantity = g.SelectMany(o => o.Orderlines).Sum(ol => ol.QuantityOrdered) }) .OrderByDescending(o => o.TotalAmount)