Linqtosql - Find all objects matching all tags in a query
I have a classic 3 table structure - entity, tag and entitytag - database.
To find all entities tagged with specific tags, I use 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 good reason in this valid application), the query returns entities that do not match all tags.
for example, in the above code example, if an object is tagged twice with the tag "tag1" and not "tag2", it will be returned in the results, even though it does not match both tags.
I can't figure out how to exclude these objects from the results?
Or is there a completely different approach that I should take?
a source to share
As suggested by Eoin, Distinct () needs to be used, but it does not work against whole sets of entities. Using another Select statement to compare against the actual tag only is the trick.
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 it degrades performance slightly.
a source to share
Try this query:
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 ensures that each tag meets the content criteria.
There is also any extension method for cases where only one criterion is required.
Hope it helps.
Kavan
a source to share
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 tags from the myTags copy. Any items left after that match tags that are not present in the object.
I don't know how it works.
a source to share