c# - 查询选择最畅销的产品

Question

我在 Entity Framework 模型中有这 3 个表/类:

Orders (OrderedItems, OrderTime)
OrderedItems (Product, Qty)
Products (Name, CreatedTime)

我可以通过这样做来选择最畅销的产品

var items = Orders.SelectMany(o => o.OrderedItems);
var products = items.GroupBy(oi => oi.Product)
                    .OrderByDescending(g => g.Sum(oi => oi.Qty))
                    .Select(g => g.Key).Take(10);

我如何选择表现最差的产品(即相反)？

注意:订单表中可能不存在性能最差的产品，因为它们可能永远不会被订购。

Answer 1

您可以从 Products 表开始，然后从那里找到匹配项。一种方法是使用 join into将 Products 表与 OrderedItems 分组的子句:

var items = Orders.SelectMany(o => o.OrderedItems);
var products = (from product in Products
                join item in items on product equals item.Product into matchingItems
                orderby matchingItems.Sum(oi => oi.Qty)
                select product).Take(10);

另一种可能效率较低但您可能会发现更具可读性的方法是使用 Enumerable.Where() 过滤项目:

var items = Orders.SelectMany(o => o.OrderedItems);
var products = (from product in Products
                orderby items.Where(oi => oi.Product == product).Sum(oi => oi.Qty)
                select product).Take(10);

这很好地转化为方法语法:

var items = Orders.SelectMany(o => o.OrderedItems);
var products = Products.OrderBy(p => items.Where(oi => oi.Product == product)
                                          .Sum(oi => oi.Qty))
                       .Take(10);