CODEWITHSUNDEEP
linq-to-sqldistincttagging
Geoff Appleford
Geoff Appleford

Reputation: 18832

Linqtosql - Find all entities matching all tags in a query

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?

Upvotes: 0

Views: 423

Answers (4)

kuhlmancer
kuhlmancer

Reputation: 85

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.

Upvotes: 0

Geoff Appleford
Geoff Appleford

Reputation: 18832

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.

Upvotes: 0

Kavan Shaban
Kavan Shaban

Reputation: 11

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

Upvotes: 1

Eoin Campbell
Eoin Campbell

Reputation: 44306

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

Upvotes: 0

Related Questions