LINQ的加入与COUNT [英] Linq join with COUNT
本文介绍了LINQ的加入与COUNT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有2个表,论坛和帖子结果
我想找回一个新的额外的领域的所有论坛字段:算属于这个论坛的所有帖子
$。 b $ b
我有这个现在:
VAR v =(来自论坛论坛
加入后在forum.ForumID帖子等于post.Forum.ForumID
选择新的
{
论坛,//需要检索从论坛
PostCount所有字段/列= //统计所有属于这个论坛有后置条件:指望它只有post.Showit == 1
}
).Distinct()
- 的加入必须LEFT JOIN:如果没有职位属于一些论坛,论坛字段应该检索但PostCount现场应为0。
- 的结果集必须是不同的(加盟给我的全十字......或者它怎么叫)
解决方案
我想你想是这样的:
从论坛
论坛,// 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()
- 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)
解决方案
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屋!
查看全文