Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq join with COUNT

I have 2 tables, Forums and Posts.
I want to retrieve all Forums fields with a new extra field: count all post that belong to this forum.

I have this for now:

var v =(from forum in Forums
    join post in Posts on forum.ForumID equals post.Forum.ForumID 
    select new 
    {
        forum, //Need to retrieve all fields/columns from forum     
        PostCount = //count all post that belong to this forum with a condition: count it only if post.Showit==1

    }
    ).Distinct()
  1. The join must be Left join: if there are no post that belongs to some forum, the forums fields should be retrieved but PostCount field should be 0.
  2. The result set must be distinct (join gives me the full cross...or how it's called)
like image 233
shivesh Avatar asked May 04 '10 16:05

shivesh


2 Answers

I think you want something like:

from forum in Forums
// ForumID part removed from both sides: LINQ should do that for you.
// Added "into postsInForum" to get a group join
join post in Posts on forum equals post.Forum into postsInForum
select new 
{
    Forum = forum,
    // Select the number of shown posts within the forum     
    PostCount = postsInForum.Where(post => post.ShowIt == 1).Count()
}

Or (as pointed out in the comments) you can put a condition in the Count call - I always forget that's available :)

from forum in Forums
// ForumID part removed from both sides: LINQ should do that for you.
// Added "into postsInForum" to get a group join
join post in Posts on forum equals post.Forum into postsInForum
select new 
{
    Forum = forum,
    // Select the number of shown posts within the forum     
    PostCount = postsInForum.Count(post => post.ShowIt == 1)
}

Another alternative for the filtering of only "shown" posts would be to do it in the join:

from forum in Forums
join post in Posts.Where(post => post.ShowIt == 1)
    on forum equals post.Forum into shownPostsInForum
select new 
{
    Forum = forum,
    // Select the number of shown posts within the forum     
    PostCount = shownPostsInForum.Count()
}

I believe all of these are logically correct, but I don't know what the SQL will look like...

like image 150
Jon Skeet Avatar answered Nov 11 '22 22:11

Jon Skeet


If you connect Forums to Posts in the linqtosql designer, this will create a relationship property that can be queried.

var query = 
  from f in db.Forums
  select new
  {
    forum = f,
    PostCount = f.Posts.Count(p => p.ShowIt == 1)
  };
like image 42
Amy B Avatar answered Nov 11 '22 23:11

Amy B