Select most frequent value and count using LINQ and assign to dictionary

Question

I'm trying to select the top five most frequent values and their count in my table and return them in a Dictionary. I'm able to get the values in sql:

SELECT top 5 
    SR_Status,
    COUNT(SR_Status) AS 'value_count'
FROM     
    ServiceRequests
GROUP BY 
    SR_Status
ORDER BY 
    'value_count' DESC;

How to convert to linq and assign to Dictionary

Answer 1

You do not specify if you are using Linq2Sql or Linq2Objects, so, let's just assume linq then. Try something like this (see the comments for each line):

var result = (from s in ServiceRequests // define the data source
             group s by s.SR_Status into g  // group all items by status
             orderby g.Count() descending // order by count descending
             select new { g.Key, Total = g.Count() }) // cast the output
             .Take(5) // take just 5 items
             .ToDictionary(x => x.Key, x => x.Total); // cast to dictionary

Obs: I didn't test it.