Linq join with COUNT

Question

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)

Answer 1

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

Answer 2

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)
  };