Linq to SQL:获得订购量最大的前十种产品 [英] Linq to SQL: Get top 10 most ordered products

问题描述

我想拿10个订购最多的产品.我的表格看起来像这样:

I'm wanting to grab the 10 most ordered products. My tables look similar to this:

产品
ProductID | ProductName

订购的产品
ProductID | OrderID

订单
OrderID | DateOrdered

目前，我有以下内容:

return (from product in db.Products
        from orderedProduct in db.OrderedProducts
        where orderedProduct.ProductID == product.ProductID
        select product).OrderByDescending(???).Distinct().Take(10);

在上面的查询中，我已经指出了不确定的内容.如何根据已订购产品表中显示的产品数量进行订购?

I've noted in the above query where I'm uncertain of what to put. How do I orderby the number of products that appear in the ordered products table?

推荐答案

return (from product in db.Products
        from orderedProduct in db.OrderedProducts
        where orderedProduct.ProductID == product.ProductID
        group orderedProduct by product into productGroups
        select new 
        {
              product = productGroups.Key, 
              numberOfOrders = productGroups.Count() 
        }
        ).OrderByDescending(x => x.numberOfOrders).Distinct().Take(10);

它将给您10个项目，每个项目包含产品对象和numberOfOrders整数.

It will give you 10 items, each item contains product object, and numberOfOrders integer.

修改:

由于这将作为方法的返回值，并且由于C#不允许返回匿名类型(但C. 4.0中仍具有此功能)，因此您可以将匿名类型转换为类.

Since this will be as a return value for a method, and since C# doesn't allow returning an anonymous type (yet .. this feature is in C# 4.0), you convert the anonymous type into a class.

创建您要返回的类型的类

Create a class of the type you want to return

public class ProductOrders
{
    public ProductOrders() {
    }

    public Product product { get; set; }
    public int numberOfOrders { get; set; }
}

并使用此查询返回该类的对象

and Use this query to return objects of that class

 return (from product in db.Products
        from orderedProduct in db.OrderedProducts
        where orderedProduct.ProductID == product.ProductID
        group orderedProduct by product into productGroups
        select new ProductOrders
        {
              product = productGroups.Key, 
              numberOfOrders = productGroups.Count() 
        }
        ).OrderByDescending(x => x.numberOfOrders).Distinct().Take(10);

现在返回值的类型为 IEnumerable<ProductOrders> .

The return value now is of type IEnumerable<ProductOrders>.

这篇关于Linq to SQL:获得订购量最大的前十种产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！