SQL query in LINQ

Question

I have two tables: Category (Id, Name, ...) and Question (Id, CategoryId, Title, ...) with relation Category 1-* Question. I need to get the top three categories (with the highest number of questions) from the database. I wrote this SQL query:

SELECT TOP 3 c.Name, COUNT(q.CategoryId)
FROM Category c
JOIN Question q
ON c.Id = q.CategoryId
GROUP BY Name
ORDER BY COUNT(q.CategoryId) DESC

and of course it works, but I need to write this query in LINQ. The main problem is in the first line with COUNT. How should I use it in this query?

Lourens · Accepted Answer · 2011-07-14 16:22:10Z

3

I'm not sure where your data comes from but the query will look something like

var q =
    from c in categories
    join q in questions on c.Id equals q.CategoryID
    group c by c.Name into g 
    orderby g.Count() descending
    select new
    {
        Name = g.Key,
        Count = g.Count()
    };

 q = q.Take(3);

answered Jul 14, 2011 at 16:22

Lourens

1,5181 gold badge13 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query in LINQ

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related