linq查询从表中选择包含大多数注释的前10个条目

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.

我有两个表“POSTS”和“评论”。一篇文章可以有很多评论，我希望能够选择评论数量最多的前10个帖子。 post_id是注释表中的FK。我正在使用Linq to SQL。请告诉我如何做到这一点。提前致谢。

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'. " . :/

这就是我的linq查询现在的样子，它给出了一个错误“类型'System.String'不支持序列运算符。”。 ：/

2 个解决方案

#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.

该错误消息是因为您在字符串属性（QUEST_TEXT）上调用.Count（）。编译因为字符串是可枚举的。但是，Linq-to-SQL并不理解这一点。

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

如果您在DBML文件中映射了两个表之间的关系，那么您可以在表达式中使用它：

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.

但是，您发布的代码与您提供的描述不完全匹配。你提到评论，但代码谈到了答案。

#2

Join Posts and Comments, order by Post.Comment count descending and the take the top 10.

加入帖子和评论，按Post.Comment计数递减顺序排在前10位。

(from p in Posts
from c in Comments
where c.PostId == p.Id
orderby p.Comments.Count() descending
select p).Take(10);

EDIT

From your edits, it looks like you are trying to find the question with the longest QUEST_TEXT value. If that is what you need, just change your code to orderby q.QUEST_TEXT.Length descending, but that doesn't sound like what you originally asked.

从您的编辑中，您似乎试图找到具有最长QUEST_TEXT值的问题。如果这是您所需要的，只需将您的代码更改为q.QUEST_TEXT.Length降序，但这听起来不像您最初的要求。