Sql – Linq to Sql – Double Group By with Parent Table Relation

asp.netlinqlinq-to-sql

I'm having a trouble getting a linq to sql query and was wondering if someone could help. I have these tables: 'Orders', 'OrderProducts', 'Products' and 'Users'. I want to get a breakdown of the count of how many products (contained in 'OrderProducts' by ProductID) each 'User' has ordered. The User's UserID is contained in the 'Orders' table and 'OrderProducts' orderID is related to the orderID in 'Orders'. Final results should be like this:

            User1   User2    User3   
Product1     100     75       17
Product2     24      78       18
Product3     82      48       35

So it is grouped by both UserID and ProductID and takes the sum of the qty of all grouped ProductIDs by that userID. I hope that makes sense. Any help is much appreciated, thanks!

Here's a simplified database breakdown:
Products
– ProductID
– ProductName

Orders
– OrderID
– UserID

OrderProducts
– OrderID
– ProductID
– ProductQty

Users
– UserID
– UserName

Best Answer

Here's a query that will give you a list of Users, Products, and the Count:

from op in OrderProducts
group op by new {op.Order.User, op.Product} into g
select new
{
  g.Key.User.UserName,
  g.Key.Product.ProductName,
  Count=g.Sum(op => op.ProductQty)
}

It won't give you results in the exact format you want, but it will get you most of the way there using only Linq.

Also take note that this won't pull any extra data - (Users or Products with counts of 0). This may or may not be what you're looking for.

[Edit] Here's another query to take a look at (two actually):

from p in Products
from u in Users
let i = (from op in OrderProducts
         where op.Order.User == u && op.Product == p
         select op.ProductQty)
let quant = i.Count() == 0 ? 0 : i.Sum(v=>v)
select new {p.ProductName, u.UserName, Quantity = quant}
//group new {u.UserName, Quantity = quant} by p into g
//select new {g.Key.ProductName, Users = g.AsEnumerable()}

As is, this should give you straight results (table of ProductName, UserName, Quantity). The commented out portion will instead group by Product, which (depending on how you render your results) might be what you want.

Related Topic