获取日期期间最畅销的 10 种产品

我想返回用户指定日期内销量最高的 10 种产品（数量最多）。

我的数据库表：

Orders

OrderId | OrderDate

订单_产品:

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);

到目前为止的代码。

我可以想到两种方法来做到这一点

这对每个产品执行子查询，以总结日期范围内销售的数量。这对我来说似乎最清楚，但可能表现不佳

  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);

或者作为单个查询，获取日期范围内的所有订单，按产品对它们进行分组，并对数量求和。

  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);