Linq to SQL: Get top 10 most ordered products

linq-to-sql, select, sql

Solution

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

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

Edit:

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

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

Problem

I'm wanting to grab the 10 most ordered products. My tables look similar to this: Product `ProductID | ProductName` OrderedProduct `ProductID | OrderID` Order `OrderID | DateOrdered` At the moment I've got the following: ``` 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?

Original source