Linq query to get Child's Child records

Question

Hi, In my Parent Child scenario, I need to get the custom list of all the customers with their total number of orders and orderitems.

A Customer can have many orders and orders can have many OrderItems. I need to write a query which returns me the collection of entity Objects which has the Customer Entity and Total Orders and Total OrderItems for that Customer object e.g

var result = from c in  ctx.Customers
         from o in  ctx.Orders
         from d in  ctx.OrderItems
         select new List<CustomerVM()
         {
            customer = c,
            totalOrders = o.Count,
            totalOrderItems = XXXX  
         }  
public class CustomerVM {
 public Customer customer {get; set;}
 public int totalOrders {get; set;}
public int totalOrderItems {get; set;}
}

How can I do that ??

Answer 1

You need to use SelectMany:

var result = (from c in ctx.Customers
              select new CustomerVM
              {
                  customer = c,
                  totalOrders = c.Orders.Count(),
                  totalOrderItems = c.Orders.SelectMany(o => o.OrderItems).Count()
              }).ToList();