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

Question

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 stuck at:

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

Answer 1

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

Answer 2

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

Answer 3

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.