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()
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...
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)
};
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With