Linq query to retrieve: Customers, Orders and OrderLineItems

Question

I have a table containing a list of customers, a second table containing orders placed by my customers and a third table containing the line items for the orders.

I would like to be able to use a Linq query to get the customer name, the number of orders and the total value of all the orders placed by this customer.

Assuming the following data:

[Customers]
CustomerId Name
---------------------
1          Bob Smith
2          Jane Doe

[Orders]
OrderId  CustomerId
---------------------
1        1
2        1
3        2

[OrderLineItems]
LineItemId  OrderId  UnitPrice  Quantity
--------------------------------------------
1           1        5          2
2           1        2          3
3           2        10         10
4           2        4          2
5           3        2          5

I would like the following result:

Name       OrdersCount  TotalValue
--------------------------------------------
Bob Smith  2            124
Jane Doe   1            10

What would be the Linq query to get this result?

Answer 1

If you presume that you have a strongly typed datacontext and a partial class Customer that you are free to add to, I would solve this problem like this:

public partial class Customer {

   public int NumberOfOrders {
      get { return Orders.Count(); } 
   }

   public int TotalValue {
      get { return Orders.OrderLineItems.Sum( o => o.UnitPrice * o.Quantity); }
   }
}

YourDataContext db = new YourDataContext();

 var query = from c in db.Customers
select new {c.Name, c.NumberOfOrders, 
c.TotalValue}

This would project a new anonymous type with the data you requested.