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 order by descending amount. (Amount == Addition of every orderLine of Quantity*UnitPrice)

Let's say we have 6 orders of 2 customers (Customer1, Customer2). Each customer has 3 orderLines.

I'm trying with:

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 obtain something like this:

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

Customer1:
- Amount: 64
- Amount: 36
- Amount: 28

while I'm trying to seek is:

Customer2:
- Amount 179

Customer1:
- Amount 128

Any suggestion, please?
Thanks so much!

Your code is working correctly for me. – Miroslav Bajtoš Aug 21 '12 at 06:00 — Miroslav Bajtoš, Aug 21 '12 at 06:00

score 9 · Answer 1 · answered Aug 21 '12 at 05:54

It sounds like you need to be doing the grouping and summing before the ordering, and only 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);

score 0 · Answer 2 · answered Aug 21 '12 at 06:01

Jon's answer is correct, but if you are going to be using this Amount property to order results and to display, then why not make it into a concrete 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're still calculating it on the fly, but if you're using it to order the collection, and then want to display it, it's likely you'll want to use it for something else as well - keep it as an access property.

That's why we have properties!

And to actually 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));

That's fine if you're using LINQ to Objects - but won't work if these are really entities in a database, and you want all the summing to occur *in SQL*. Of course then we probably wouldn't *really* have a `List<...>` anyway. It will depend on whether we've been given the exact real code, or pseudo-code... — Jon Skeet, Aug 22 '12 at 09:29

score 0 · Answer 3 · answered Jun 04 '15 at 11:10

I know it is not specifically an answer, but I had a smiliar challenge and 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)

LINQ Sum Nested collection with GroupBy and OrderByDescending

3 Answers3