Linq to SQL order by aggregate

Question

I have a very simple grouping and aggregation problem in LINQ to SQL that I just can't figure out, and it is driving me mad.

I've simplified things into this example:

class Customer { public Guid Id; public String Name; }

class Order { public Guid Customer_Id; public double Amount; }

How do I get a list of customers ordered by the number of orders they have? And on the total amount they have purchased for?

Answer 1

return dataContext.Customers.OrderBy(cust => cust.Orders.Count)
    .ThenBy(cust => cust.Orders.Sum(order => order.Amount))
    .ToList();

Answer 2

By number of orders:

var customers = (from c in db.Customers
                 select new 
                  {
                    c.Name, 
                    OrderCount = c.Orders.Count()
                  }).OrderBy(x => x. OrderCount);

By total amount purchased:

var customers = (from c in db.Customers
                 select new 
                  {
                    c.Name, 
                    Amount = (from order in c.Orders
                              select order.Amount).Sum()
                  }).OrderBy(x => x.Amount);

Answer 3

var qry1 = from c in db.Customers
           join o in db.Orders on c.Id equals o.Customer_Id into orders
           orderby orders.Count()
           select c;

var qry2 = from c in db.Customers
           join o in db.Orders on c.Id equals o.Customer_Id into orders
           orderby orders.Sum(o => o.Amount)
           select c;