LINQ to SQL with multiple joins, group by and count

Viewed 12853

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?

1 Answers
Related