How to convert SQL Statement with TOP, COUNT and GROUP BY to return an object list with LINQ

Question

Hello guys

does anyone know how to convert this SQL statement to a LINQ to a List?

SELECT TOP(5) COUNT(Tickets.CategoryId), Categories.CategoryName
FROM Tickets 
INNER JOIN Categories ON Tickets.CategoryId = Categories.CategoryId
GROUP BY Categories.CategoryName
ORDER BY 1 DESC

The result would be something like this?

public static List<Categories> List()
{
    MyEntities db = new MyEntities();

    var categories = (from ticket in db.Ticket.Include("Category")
                        group ticket by ticket.Category.CategoryId into g
                        orderby g.Count() descending
                        select g.FirstOrDefault().Category).Take(5).ToList();

    return categories;

}

Answer 1

Have you tried creating a view from your query and calling that from Linq? It would look to Linq like a table. You could use the exact query you provided above.

You may be looking for a Linq only answer, but using a view would be a quick way to get there and you may get a slight boost in performance if you are hitting it very frequently.

I like to use DB for the things it is good at, and it looks to me like this might just be a good fit.

If you go with a Linq only solution, you might want to spend a few minutes trying the view and doing timing tests to see if it makes a difference in your app.

Answer 2

Looks like you just need to change the "SELECT" portion of your statement:

select new { Count = g.Count(), 
    CategoryName = g.FirstOrDefault().Category.CategoryName }