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.
I have a collection of tag ids. And i want to get posts having every tag in my collection.
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 && ... )
You shouldn’t project each post’s tags in the outer query; 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.)
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 tagIds
collection.
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:
// 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() == false
to simulate the NOT EXISTS
operator in Transact-SQL.