Get Top x Products using Linq

Question

I need to get the Top 10 records using Linq to Entities.

I need to bind the returned result into a GridView as follows"

Product Name  |   Product Description  |   Number of Items Sold

Item 1        |   Item 1 Description   |           24

As per the above, the items need to be stored in order, starting with the most item sold.

I've tried a couple of example such as this, this, and followed the examples here.

The following is what I have tried so far:

public IQueryable GetTopTenProducts(DateTime startDate, DateTime endDate)
{
    return
        (from p in this.Entities.Product
         join pro in this.Entities.ProductOrder on p.ID equals pro.ProductID
         join o in this.Entities.Order on pro.OrderID equals o.ID
         where o.DateOfOrder >= startDate && o.DateOfOrder <= endDate
         select new
         {
             Product = p,
             Quantity = pro.Qty,
             ProductName = p.Name,
             ProductDescription = p.Description

         } into productQty
         group productQty by productQty.Product into pg
         let totalQuantity = pg.Sum(prod => prod.Quantity)
         orderby totalQuantity descending
         select pg.Key).Take(10);
}

This returns the products table as a whole, but I need to retrieve only the details pasted above.

Any ideas?

Answer 1

I think you have to change your last select select pg.Key to select what you want

for example: select new { pg.Key.ProductName, pg.Key.ProductDescription, totalQuantity }