Linq加入COUNT

c# entity-framework linq

我有2张桌子,论坛和帖子。
我想要使用新的额外字段检索所有论坛字段:计算属于此论坛的所有帖子。

我现在有这个:

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. 连接必须是左连接:如果没有属于某个论坛的帖子,则应检索论坛字段,但PostCount字段应为0。
  2. 结果集必须是不同的(join给我完整的交叉......或者它是如何调用的)

一般承认的答案

我想你想要的东西:

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

或者(如评论中所指出的)你可以在Count调用中添加一个条件 - 我总是忘记它是可用的:)

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

仅过滤“显示”帖子的另一种方法是在连接中执行此操作:

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

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


热门答案

如果在linqtosql设计器中将论坛连接到帖子,则会创建一个可以查询的关系属性。

var query = 
  from f in db.Forums
  select new
  {
    forum = f,
    PostCount = f.Posts.Count(p => p.ShowIt == 1)
  };



许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因