LINQ many-to-many how to write the correct WHERE clause?

Question

I use many-to-many relationships for my tables.

There is a request:

var query = from post in context.Posts from tag in post.Tags where tag.TagId == 10 select post; 

OK, everything is fine. I get messages with the tag specified by id.

I have a collection of tag identifiers. And I want to receive messages with 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; 

This does not work. The request returns all messages that have ANY one of the specified tags.

I want to get a suggestion like this, but dynamically for any number of tags in a collection:

 post.Tags.Whare(x => x.TagId = 1 && x.TagId = 3 && x.TagId = 7 && ... ) 

Answer 1

You should not design message tags in an external request; rather, you need to use an internal query that performs an external filter check. (In SQL, we called this a correlated subquery .)

 var query = from post in context.Posts where post.Tags.All(tag => tagIds.Contains(tag.TagId)) select post; 

Alternative syntax:

 var query = context.Posts.Where(post => post.Tags.All(tag => tagIds.Contains(tag.TagId))); 

Edit : Correction for clarifying Slaumas . The next version returns messages containing 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; 

Alternative syntax:

 var query = context.Posts.Where(post => tagIds.All(requiredId => post.Tags.Any(tag => tag.TagId == requiredId))); 

Edit ₂ : Fixed above for Slauma. Also includes another alternative that makes full use of the 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; 

Ive used .Any() == false to model the NOT EXISTS statement in Transact-SQL.

Answer 2

This is pretty easy to do:

 var tags = context.Posts.Where(post => post.Tags.All(tag => tagIds.Contains(tag))); 

Answer 3

Another option is to intersect the two lists if you want the tag collection to ONLY contain the parameter you specified, and 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; 

Answer 4

Try using Any .

 var query = from post in context.Posts from tag in post.Tags where tagIds.Any(t => t == tag.TagId ) select post;