CODEWITHSUNDEEP
linq-to-sqlgroup-bysql-order-by
Spud
Spud

Reputation: 420

Linq to SQL - grouping products by the total quantity ordered

I'm trying to get a list of 'popular products' on an ecommerce site using LINQ to SQL - products are ordered and become rows in a Redemption table, with a quantity column to indicate the quantity ordered.

Here are the relevant bits of the tables:

Product

    ProductId INT
    ProductTitle VARCHAR
    (other columns not shown)

Redemption

    RedemptionId INT
    ProductId INT
    Quantity INT
    (other columns not shown)

I'm having trouble grouping and then ordering by the sum of quantity ordered. Here's what I have so far:

(from product in Products
join redemption in Redemptions on product.ProductId equals redemption.ProductId
group product by new { product.ProductId, product.ProductTitle } into g
orderby g.Sum(r => r.Key.Quantity) // Quantity is not part of the key :(
select g.Key).Take(5)

While I can group the products in the join to redemptions, I cannot order by the sum of quantities ordered.

Ideally, I only want the product object to be returned, not an anonymous type including the quantity.

Upvotes: 3

Views: 765

Answers (1)

russau
russau

Reputation: 9098

Doing a join into makes it much easier:

var x = (from p in products
join r in redemptions on p.ProductId equals r.ProductId into pr
orderby pr.Sum(r => r.Quantity) descending
select p).Take(5);

Upvotes: 1

Related Questions