Linq join with COUNT
- by shivesh
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()
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.
The result set must be distinct (join gives me the full cross...or how it's called)
