How to get most commented posts without duplication in LINQ
I tried to solve this problem and could not. I am making a web blog application using ASP.net MVC and trying to create better stories based on counting comments. This query is below:
var top = (from p in posts
join c in comments
on p.Id equals c.whichPost
orderby posts.Where(h=>h.Id==c.whichPost).Count()
select(p)
).Take(5);
My view:
@foreach (var item in Model) {
<tr>
<td>
<a href="/Posts/Detail/@item.Id">@Html.DisplayFor(modelItem => item.Title)</a>
</td>
</tr>
}
Actually asks for the top ones, but it prints the title title according to the number of comments. What I am trying to say:
- Heading 1 (i.e. has 4 comments)
- Heading 1
- Heading 1
- Heading 1
- title 3 (2 comments)
- title 3
- Heading 2 (one comment)
Any hints for a solution to this problem? Thank.
+3
source to share
1 answer
Use a group connection :
var top = (from p in posts
join c in comments on p.Id equals c.whichPost into g
orderby g.Count() descending // don't forget to use descending order
select p).Take(5);
Or if you have navigation properties in your entities
var top = posts.OrderByDescending(p => p.Comments.Count()).Take(5);
Note: your current code creates a couple of comments for each comment that matches some post. This is why you see duplication. There is also a problem with posts that have no comments. A simple connection won't give you any matches.
+2
source to share