LINQ to SQL with multiple joins, grouping, and counting

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 are the SQL statistics that generate what I want, a list of customers with the total number of orders for each customer and the total number of ordered items.

 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 to convert this to LINQ was just to count the elements and came up with the following:

 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 an exception:

 Value cannot be null. Parameter name: inner 

Am I on the right track? What do I need to do to get both bills?

Answer 1

• For left joins - I suggest using from with where and DefaultIfEmpty

• You need to group using an anonymous type to group multiple parameters

The value cannot be null. Parameter Name: Internal

Are any union properties allowed?

  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, };