C# LINQ多对多关系，如何写出正确的WHERE子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10505595/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LINQ many-to-many relationship, how to write a correct WHERE clause?
提问by Jean Louis
I use many-to-many relationship for my tables.
我对我的表使用多对多关系。
There is a query:
有一个查询：
var query = from post in context.Posts
from tag in post.Tags where tag.TagId == 10
select post;
Ok, it works fine. I get posts having the tag specified by id.
好的，它工作正常。我收到带有 id 指定标签的帖子。
I have a collection of tag ids. And i want to get posts having every tag in my collection.
我有一组标签 ID。我想获得在我的收藏中包含每个标签的帖子。
I try the following way:
我尝试以下方式：
var tagIds = new int[]{1, 3, 7, 23, 56};
var query = from post in context.Posts
from tag in post.Tags where tagIds.Contains( tag.TagId )
select post;
It doesn't work. The query returns all posts having ANY one of the specified tags.
它不起作用。该查询返回具有任何一个指定标签的所有帖子。
I want to get a clause like this but dynamicaly for any count of tags in the collection:
我想得到一个这样的子句，但是对于集合中的任何标签数量都是动态的：
post.Tags.Whare(x => x.TagId = 1 && x.TagId = 3 && x.TagId = 7 && ... )
采纳答案by Douglas
You shouldn't project each post's tags in the outerquery; rather, you need to use an inner query which performs the check for the outer filter. (In SQL, we used to call it a correlated subquery.)
您不应该在外部查询中投影每个帖子的标签；相反，您需要使用内部查询来执行外部过滤器的检查。（在 SQL 中，我们过去称它为相关子查询。）
var query =
from post in context.Posts
where post.Tags.All(tag => tagIds.Contains(tag.TagId))
select post;
Alternate syntax:
替代语法：
var query =
context.Posts.Where(post =>
post.Tags.All(tag =>
tagIds.Contains(tag.TagId)));
Edit: Correcting per Slauma's clarification. The version below returns posts which contain, at least, all the tags in the tagIdscollection.
编辑：根据Slauma 的说明进行更正。下面的版本返回至少包含tagIds集合中所有标签的帖子。
var query =
from post in context.Posts
where tagIds.All(requiredId => post.Tags.Any(tag => tag.TagId == requiredId))
select post;
Alternate syntax:
替代语法：
var query =
context.Posts.Where(post =>
tagIds.All(requiredId =>
post.Tags.Any(tag =>
tag.TagId == requiredId)));
Edit2: Corrected above per Slauma. Also including another alternative making full use of query syntax below:
编辑2：根据 Slauma 进行更正。还包括另一个充分利用以下查询语法的替代方案：
// Project posts from context for which
// no Ids from tagIds are not matched
// by any tags from post
var query =
from post in context.Posts
where
(
// Project Ids from tagIds that are
// not matched by any tags from post
from requiredId in tagIds
where
(
// Project tags from post that match requiredId
from tag in post.Tags
where tag.TagId == requiredId
select tag
).Any() == false
select requiredId
).Any() == false
select post;
I've used .Any() == falseto simulate the NOT EXISTSoperator in Transact-SQL.
我曾经在 Transact-SQL 中.Any() == false模拟过NOT EXISTS操作符。
回答by Tejs
This is actually pretty easy to do:
这实际上很容易做到：
var tags = context.Posts.Where(post => post.Tags.All(tag => tagIds.Contains(tag)));
回答by Daniel A. White
Try it with Any.
试试吧Any。
var query = from post in context.Posts
from tag in post.Tags where tagIds.Any(t => t == tag.TagId )
select post;
回答by scottm
Another option is to intersect the two lists if you want the collection of tags to ONLY contain the set you specify and no others:
如果您希望标签集合仅包含您指定的集合而不包含其他集合，则另一种选择是将两个列表相交：
var query = from post in context.Posts
let tags = post.Tags.Select(x => x.Id).ToList()
where tags.Intersect(tagIds).Count() == tags.Length
select post;
