Linq to Sql - Double Group By with Parent Table Relation

Answer 1

If you have the appropriate relationships set up in the database (and therefore, in the DBML), you may 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-behind:

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 around your markup and some of the object types, but I'm hoping this will get you going. I try to use the autogenerated class object structure as much as possible to avoid creating unreadable LINQ queries.

Answer 2

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.

Answer 3

The result shape you are asking for is known as a pivot. It has columns which are data-driven, instead of columns which are declared before the query runs.

The trick here is to project the result into a type where the "properties" can vary at run-time. I did something like that here

Here's a stab at your particular 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();