LINQ的加入与COUNT [英] Linq join with COUNT

问题描述

我有2个表，论坛和帖子结果
我想找回一个新的额外的领域的所有论坛字段：算属于这个论坛的所有帖子

$。 b $ b

我有这个现在：

  VAR v =（来自论坛论坛
加入后在forum.ForumID帖子等于post.Forum.ForumID 
选择新的
 {
论坛，//需要检索从论坛
 PostCount所有字段/列= //统计所有属于这个论坛有后置条件：指望它只有post.Showit == 1 
 
} 
）.Distinct（）
  

1. 的加入必须LEFT JOIN：如果没有职位属于一些论坛，论坛字段应该检索但PostCount现场应为0。


2. 的结果集必须是不同的（加盟给我的全十字......或者它怎么叫）

解决方案

我想你想是这样的：

 从论坛
论坛，// ForumID部分从两侧去除：LINQ应该为你做的。 
 //添加到postsInForum来得到一组加入
在论坛帖子加入后等于post.Forum到postsInForum 
选择新的
 {
 =论坛论坛，
 //选择显示帖子的论坛
 PostCount = postsInForum.Where（后= GT; post.ShowIt == 1）中的数字.Count之间的（）
} 
  

或（如在评论中指出的），你可以把一个条件在计数电话 - 我总是忘了可用：）

 从论坛，论坛
 // ForumID部分双方的删除：LINQ应该为你做的。 
 //添加到postsInForum来得到一组加入
在论坛帖子加入后等于post.Forum到postsInForum 
选择新的
 {
 =论坛论坛，
 //选择论坛
 PostCount内显示的职位数= postsInForum.Count（后= GT; post.ShowIt == 1）
} 
  

另一种方法为显示的帖子是这样做的加入只能过滤：

 从论坛，论坛
加入后在Posts.Where（后= GT; post.ShowIt == 1）
在论坛上等于帖子。论坛进入shownPostsInForum 
选择新的
 {
 =论坛论坛，
 //选择论坛$ b $内显示的职位数b PostCount = shownPostsInForum.Count（）
} 
  

我相信，所有这些都的逻辑的正确的，但我不知道SQL会是什么样子...

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)

解决方案

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

这篇关于LINQ的加入与COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！