LINQ to SQL with multiple joins, group by and count

Question

I have 3 tables, Cust, Order and Item with the following relevant fields:

Cust - CustID, CustName
Order - OrderID, CustID
Item - ItemID, OrderID

I want to find the total number of orders and items for each customer.

Here is an SQL statement that generates what I want, a list of customers with the total number of orders for each customer and the total number of items ordered.

SELECT
  Cust.CustID, Cust.CustName,
  count(DISTINCT Order.OrderID) AS numOrders,
  count(DISTINCT Item.ItemID ) AS numItems
FROM Cust
LEFT JOIN Order ON Order.CustID = Cust.CustID
LEFT JOIN Item ON Item.OrderID = Order.OrderID
GROUP BY Cust.CustID, Cust.CustName
ORDER BY numItems

My first attempt at converting this to LINQ was to just count items and came up with this:

var qry = from Cust in tblCust
    join Order in tblOrder on Cust.CustID equals Order.CustID
    join Item in tblItem on Order.OrderID equals Item.OrderID
    group Cust by new {CustID = Cust.CustID, CustName = Cust.CustName} into grp
    orderby grp.Count()
    select new
    {
        ID = grp.Key.CustID,
        Name = grp.Key.CustName,
        Cnt = grp.Count()
    };

With this code I get the exception:

Value cannot be null. Parameter name: inner

Am I on the right track? What would I have to do to get both counts?

Answer 1

1. For Left Joins - I suggest using a from with a where and a DefaultIfEmpty

2. You need to group using an anonymous type in order to group multiple parameters

Value cannot be null. Parameter name: inner

Are any of joining properties nullable?

 var qry = 
        from Cust in tblCust
        from Order in tblOrder.Where(x => Cust.CustID == x.CustID)
                              .DefaultIfEmpty()
        from Item in tblItem.Where(x => Order.OrderID == x.OrderID)
                            .DefaultIfEmpty()
        group new { Cust, Order.OrderId, Item.ItemId } by new { Cust.CustID, Cust.CustName } into grp
        let numItems = grp.Select(x => x.ItemId).Distinct().Count()
        orderby numItems 
        select new
        {
            ID = grp.Key.CustID,
            Name = grp.Key.CustName,
            numOrders = grp.Select(x => x.OrderId).Distinct().Count(),
            numItems,
        };