Adding count from another table to variable with method chain

I have a table of posts, and a table of likes. All likes for a post id have to be counted in order to get the total likes for each post. Currently I do this by looping through all the posts which is very inefficient, but I cannot find the correct way to do this in a single database call.

List<Post> posts = await db.Posts.OrderByDescending(c => c.DateCreated)
                .ToListAsync();

foreach (Post post in posts)
{
      post.TotalLikes = await db.Likes.Where(d => d.PostID == post.ID).CountAsync();
}

1 answer

  • answered 2018-01-14 09:46 Serkan Arslan

    You can try this.

    var posts = Posts.OrderByDescending(c => c.DateCreated)
                    .Select(post => new { post, TotalLikes = Likes.Where(d => d.PostID == post.ID).Count()})
                    .ToList();