LINQ many-to-many relationship, how to write a correct WHERE clause?

Question

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 && ... )

Answer 1

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

Edit₂: 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.