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 }

Answer 2

You are getting the whole products table because you are selecting pg.Key where pg is an IGrouping with Product as the key and your anonymous type as the IElement.

So, you can refactor your final select to do something like this:

//rest of Linq query...
select new 
{ 
    pg.Key.Name, 
    pg.Key.Description,
    totalQuantity
})Take(10);