How do i get the top 5 items in a database based on the occurence of a particluar field?

Using Ado.Net Entity framework, I am trying to get the 'top 3' items in a table based on the amount of times they appear in a table.

For example:

Table: basket_to_product_id | basket_id | product_id

I want to see how many times product_id occurs, and would like to return the top 3 product_ids that occur the most frequently.

I'm s开发者_开发技巧tuck at:

List<BasketToProduct> btplist = entities.BasketToProduct. ..........?

Something like this should work (of course I do not know the actual names of your properties):

IEnumerable<int> top3ProductIds = (from btp in entities.BasketToProduct
                                   group btp by btp.ProductId into g
                                   orderby g.Count() descending
                                   select g.Key).Take(3);

You could try to use a LINQ query on the table.

Try this:

var query = entities.BasketToProduct
                         .GroupBy(btp => btp.ProductID)
                         .Select(g => ProductID = g.Key, Count = g.Count())
                         .OrderBy(g => g.Count)
                         .Take(3);

It'll get you the top three ProductIDs and their associated counts.