linq query to select top 10 entries with most comments from the table

Question

I have two tables "POSTS" and "COMMENTS". One post can have many comments and I want to be able to select the top 10 posts with highest number of comments. The post_id is a FK in the comments table. I am using Linq to SQL. Please advise me on how to do this . Thanks in advance.

EDIT

var top = (from q in db.question_tables
                   from a in db.answer_tables
                   where q.QUEST_ID.Equals(a.ANS_QUEST_ID)
                   orderby q.QUEST_TEXT.Count() descending
                   select new
                   {
                       QUEST_TEXT = q.QUEST_TEXT


                   }).Take(10);

this is how my linq query looks like now , its giving an error "Sequence operators not supported for type 'System.String'. " . :/

Answer 1

That error message is because you are calling .Count() on a string property (QUEST_TEXT). That compiles because strings are enumerable. However, Linq-to-SQL doesn't understand this.

If you have the relationship between the two tables mapped in your DBML file, then you can use it in your expression:

var top = (from q in db.question_tables
           orderby q.answers.Count() descending
           select q).Take(10);

However the code you posted doesn't quite match the description you gave. You mention comments, but the code talks about answers.