Linqtosql - Find all entities matching all tags in a query

Question

I have the classic 3 table - entity, tag and entitytag - database structure.

In order to find all entities tagged with certain tags I am using the following Linqtosql code:

string[] myTags = {"tag1","tag2"};

var query = from m in entity
      where m.entitytag.Where(c => myTags.Contains(c.tag.TagName)).Count() == myTags.Count()
      select m;

However, when entities have duplicate tags(there is a valid reason for this in my real app) the query returns entities that do not match all tags.

eg in the above code sample, if an entity was tagged twice with 'tag1' and not 'tag2' it would return in the results despite not matching both tags.

I cant figure out how to exclude these entities from the results?

Or is there a completely different approach I should be taking?

Answer 1

How about changing it to

where m.entitytag.Distinct().Where(c => ...

that would remove the duplicate entitytags from your entity objects sub collection, and allow your count to work properly

Answer 2

Try this query instead:

                    string[] myTags = { "tag1", "tag2" };
                    var query = from m in entity
                                where myTags.All(tag => m.entitytag.Contains(tag))
                                select m;
                    query.Dump();

The All extension method is what will ensure that each tag satisifies the contains criteria.

There is also an Any extension method for cases where you want just one criteria met.

Hope it helps.

Kavan

Answer 3

As suggested by Eoin, Distinct() needs to be used but it doesn't work against entire entitytag sets. Using another Select statement to only compare against the actual tag is the trick here.

    string[] myTags = {"tag1","tag2"};

    var query = from m in entity
    where m.entitytag.Select(et => et.tag.TagName).Distinct().Where(c => myTags.Contains(c)).Count() == myTags.Count()
    select m;

Unfortunately, the downside is that this hurts performance somewhat.

Answer 4

Try

string[] myTags = { "tag1", "tag2" };

var query = from e in entity
            where !myTags.Except(from e.tag select e.tag.TagName).Any()
            select e;

The idea is to remove the entity's tags from a copy of myTags. Any elements left after that correspond to tags that are missing from the entity.

I don't know how this performs, though.