Get Top 10 most sold products between date

Question

I would like to return the top 10 most sold products (most quantity) during dates specified by the user.

My tables from the database:

Orders

OrderId | OrderDate

Order_Products:

ProductID | OrderID | Quantity

Products

ProductID | ProductName | ProductDescription

Code:

return (from product in this.Entities.Products
        from orderProduct in this.Entities.Order_Product 
        from order in this.Entities.Orders
        where order.OrderId = orderProduct.ProductID && orderProduct.ProductID == product.ProductID
        where (order.OrderDate >= date1 &&  <= date2)
        select product).OrderByAscending(COUNT(Quantity)).Distinct().Take(10);

code up to now.

Answer 1

I can think of two ways to do this

This does a sub query for each product to sum up the quantities sold between the date range. This seems the most clear to me, but might not perform as well

  var query = 
   (from p in Entities.Products
    let totalQuantity = ( from op in Entities.Order_Product
                          join o in Entities.Orders on op.OrderID equals o.OrderId
                          where op.ProductID == p.ProductID && o.OrderDate >= date1 && o.OrderDate <= date2
                          select op.Quantity ).Sum()
    where totalQuantity > 0
    orderby totalQuantity descending
    select p).Take(10);

Or as a single query which gets all orders in the date range, groups them up by their product, and sums the quantities.

  var query = 
   (from p in Entities.Products
    join op in Entities.Order_Product on p.ProductID equals op.ProductID
    join o in Entities.Orders on op.OrderID equals o.OrderId
    where o.OrderDate >= date1 && o.OrderDate <= date2
    select new { Product = p, Quantity = op.Quantity } into productQty
    group productQty by productQty.Product into pg
    let totalQuantity = pg.Sum(prod => prod.Quantity)
    orderby totalQuantity descending
    select pg.Key).Take(10);

Answer 2

var query = 
   (from p in Entities.Products
    join op in Entities.Order_Product on p.ProductID equals op.ProductID
    join o in Entities.Orders on op.OrderID equals o.OrderId
    where o.OrderDate >= date1 && o.OrderDate <= date2
    select new { Product = p, op.Quantity } into pq
    group pq by pq.Product.ProductID into g
    select g.OrderByDescending(x => x.Quantity).First().Product).Take(10);