Linq to Sql - double group with parent table relation
I am having trouble getting the linq to sql query and was wondering if anyone could help. I have these tables: "Orders", "OrderProducts", "Products" and "Users". I want to get a schedule for counting the number of products (contained in "OrderProducts" by ProductID) that each "User" has ordered. The UserID is in the Orders table, and the orderProducts OrderProducts is linked to the orderID in Orders. The end results should be like this:
User1 User2 User3 Product1 100 75 17 Product2 24 78 18 Product3 82 48 35
So it is grouped with both UserID and ProductID and takes the sum of the count of all grouped product ids using that user ID. I hope this makes sense. Any help is greatly appreciated, thanks!
Here's a simplified breakdown of the database:
Products
- ProductID
- ProductName
Orders
- OrderID
- UserID
OrderProducts
- OrderID
- ProductID
- ProductQty
Users
- UserID
- UserName
source to share
Here's a query that will give you a list Users
, Products
and 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 help you mostly use Linq.
Also note that this will not pull additional data - ( Users
or Products
with the number 0). This may or may not be what you are looking for.
[Edit] Here's another request to look at (two in fact):
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 it is, this should give you direct results (ProductName, UserName, Quantity table). The enclosed part will be grouped by product, which (depending on how you are doing your results) might be what you want.
source to share
If you have relevant relationships established in the database (and therefore in DBML), you might want to consider something like this (UNTESTED):
<table>
<tr>
<asp:Repeater ID="_users" runat="server">
<ItemTemplate>
<td><%# Eval("UserName") %></td>
</ItemTemplate>
</asp:Repeater>
</tr>
<asp:Repeater ID="_products" runat="server">
<ItemTemplate>
<tr>
<td><%# Eval("Product.ProductName") %></td>
<asp:Repeater ID="_users" runat="server" DataSource='<%# GetUsersProducts(Eval("ProductID")) %>'>
<ItemTemplate>
<td><%# Eval("count") %></td>
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:Repeater>
</table>
Code for:
protected void Page_Load(object sender, EventArgs e)
{
_users.DataSource = context.Users.OrderBy(u => u.UserName);
_users.DataBind();
_products.DataSource = context.Products;
_products.DataBind();
}
protected object GetUsersProducts(string ProductID)
{
var prodord = from op in context.OrderProducts where op.ProductID == ProductID select op;
return from u in context.Users.OrderBy(u2 => u2.UserName) select new {
count = prodord.Where(op => op.Order.User.UserID == u.UserID).Sum(op => op.ProductQty)
};
}
You will obviously want to change your markup and some object types, but I hope this helps you. I try to use the autogenerated class object structure as much as possible to avoid creating unreadable LINQ queries.
source to share
The result form you are asking for is called a pivot. It has columns that are data driven, not columns that are declared before the query is run.
The trick here is to project the result into a type, where the "properties" can change at runtime. I did something like here
Here's a blow to your specific problem:
// grab relationally-shaped results from database
var queryResult =
(
from u in myDataContext.Users
from o in u.Orders
from op in o.OrderProducts
select new
{
UserName = u.UserName,
ProductName = op.ProductName,
Quantity = op.Quantity
}
).ToList()
// now use a LinqToObjects query to pivot
List<XElement> pivotedResults =
(
from x in queryResult
group by x.ProductName into g
select new XElement(g.Key,
from y in g
group by y.UserName into subg
select new XAttribute(subg.Key, subg.Sum(z => z.Quantity))
).ToList();
source to share