title: LINQ to SQL with multiple joins, group by and count
body: <p>I have 3 tables, Cust, Order and Item with the following relevant fields:</p>
  <pre><code>Cust - CustID, CustName
  Order - OrderID, CustID
  Item - ItemID, OrderID
  </code></pre>
  <p>I want to find the total number of orders and items for each customer.</p>
  <p>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.</p>
  <pre><code>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
  </code></pre>
  <p>My first attempt at converting this to LINQ was to just count items and came up with this:</p>
  <pre><code>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()
      };
  </code></pre>
  <p>With this code I get the exception:</p>
  <pre><code>Value cannot be null. Parameter name: inner
  </code></pre>
  <p>Am I on the right track? What would I have to do to get both counts?</p>