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

possible duplicate of [How do I retrieve items that are tagged with all the supplied tags in linq?](http://stackoverflow.com/questions/3478874/how-do-i-retrieve-items-that-are-tagged-with-all-the-supplied-tags-in-linq) — Amy B, May 08 '12 at 20:51

score 33 · Accepted Answer · edited May 23 '17 at 12:33

33

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.

edited May 23 '17 at 12:33

Community

1
1

answered May 08 '12 at 20:02

Douglas

53,759
13
140
188

2

+1 for being the only one who matches on only **ALL** tagIds, not just **ANY** – Mathew Thompson May 08 '12 at 20:02
@mattytommo Are you sure about that? It looks to me like he is getting all posts. Edit: He just added the post.Tags.All block which should do the trick, but Tejs had that first. – Trisped May 08 '12 at 20:14
@Trisped, no he's matching **ALL** id's in the tagIds list, not just **ANY** – Mathew Thompson May 08 '12 at 20:15
Actually, I’d originally assumed a non-existent overload of `Contains`; the code wouldn’t have compiled. – Douglas May 08 '12 at 20:21
1

That is true, I just assumed that you knew something I did not. Even so, mattytommo is still incorrect, as your original idea (if it had worked) would not have resulted in only the posts with ALL tags being returned. +1 since your updated answer has the longer linq I prefer and the alternate and is well written. – Trisped May 08 '12 at 20:42
1

Well, you can’t really say whether he was correct or not, since it would have depended on whether the hypothetical `Contains(IEnumerable)` was logically equivalent to an `Any` or an `All`. I’d assumed it would be equivalent to an `All` (as in “contains subset”). – Douglas May 08 '12 at 20:47
OK, I agree, now seeing it, it's also pretty easy :) (I honestly thought @scottm's solution would be the only way). If you replace now `post.Tags` by `post.Tags.Select(t => t.TagId)` then it even compiles. Nonetheless +1! – Slauma May 08 '12 at 21:13
@Slauma: Thanks for pointing that out. My fault for not testing before submitting. All code samples should at least compile now. – Douglas May 08 '12 at 21:41
1

Ah, with `Any` it even looks better! The last code snippet looks strange. For me it's a good example that extension method syntax is easier to read as soon as queries become more complex. I would clearly prefer the version with the "indentation cascade" (but indent differently) :) – Slauma May 08 '12 at 21:48
@Slauma: Yes, I agree that the purely query syntax one looks convoluted, although it’s close to what one would traditionally have had to write in SQL. It might be possible to make it look better using joins… – Douglas May 08 '12 at 21:55

score 4 · Answer 2 · answered May 08 '12 at 20:01

4

This is actually pretty easy to do:

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

answered May 08 '12 at 20:01

Tejs

40,736
10
68
86

He wants matching **ALL**, not just the 1 – Mathew Thompson May 08 '12 at 20:02
Updated then simply using the `All` extension. – Tejs May 08 '12 at 20:02
This would return posts where *all* tags occur in the tagIds list. For example: tagIds = ("C#","Java") and a post which has only the tag "C#". Your query would return this post. But he wants: "to get posts having **every tag** in my collection". I.e.: Only the posts which are tagged with "C#" **and** "Java". Perhaps, not that pretty easy to do? :) – Slauma May 08 '12 at 20:27
@Slauma: It’s the same level of difficulty (or “pretty easiness”) – you just need to swap the collections on which the `All` and `Contains` calls are made. I’ve left [both versions](http://stackoverflow.com/a/10505638/1149773) for comparison. – Douglas May 08 '12 at 20:58

scottm · Answer 3 · 2012-05-08T20:48:00.010

4

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;

edited May 08 '12 at 20:48

answered May 08 '12 at 20:04

scottm

27,829
22
107
159

1

Wouldn't you want `tags.Intersect(tagIds).Count() == tagIds.Length` ? – Trisped May 08 '12 at 20:20
I wouldn't be sure that the `ToList()` inside of `let` works with LINQ to Entities. (I'd expect "cannot translate into store expression" exception, also `tags` wouldn't be an `IQueryable` anymore.) In my opinion without the `ToList()` it was fine. – Slauma May 08 '12 at 21:42

score 0 · Answer 4 · answered May 08 '12 at 20:01

0

Try it with Any.

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

answered May 08 '12 at 20:01

Daniel A. White

187,200
47
362
445

He wants matching **ALL**, not just the 1 – Mathew Thompson May 08 '12 at 20:01

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

4 Answers4

Linked