Geek asks and answers

Linq to SQL - grouping products by the total quantity ordered

I am trying to get a list of "popular products" on an e-commerce site using LINQ to SQL - products are ordered and become rows in the Redemption table with a quantity column indicating the ordered quantity.

Here are the relevant table bits:

product

    ProductId INT
    ProductTitle VARCHAR
    (other columns not shown)

Buyout

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

I had problems with grouping, and then sorting by the amount of the ordered quantity. Here is what I still have:

(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 products in a buyback connection, I cannot order the sum of the ordered values.

Ideally, I want to return a product object, not an anonymous type, including quantity.

+3
source share
1 answer

Execution join intomakes this a lot 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);
+1
source

More articles:


All Articles