Linq to SQL: Get top 10 most ordered products

Question

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?

Answer 1

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>.