Reputation: 27833
I seem to be having trouble with this. I have a Task table with an ID, and a Tag table, that has a tag field and a foreign key constraint to the task table.
I want to be able to perform AND searches for tasks by tags. So for example, if I search for tasks with the tags "portability" and "testing", I don't want tasks that are tagged with "portability" and not "testing".
I tried the following syntax:
var tasks = (from t in _context.KnowledgeBaseTasks
where t.KnowledgeBaseTaskTags.Any(x => tags.Contains(x.tag))
select KnowledgeBaseTaskViewModel.ConvertFromEntity(t)
).ToList();
This of course does an OR search, not an AND search. I can't figure out how to actually switch this to be an AND search.
Edit I also need to be able to search for 2 out of X tags that a task contains. So if the task is tagged with "bugfix", "portability", "testing" and I search for "testing" and "portability", that task will still show up.
Upvotes: 1
Views: 397
Reputation: 110221
You want to do this
the LinqToSql might look like:
List<int> myTags = GetTagIds();
int tagCount = myTags.Count;
IQueryable<int> subquery =
from tag in myDC.Tags
where myTags.Contains(tag.TagId)
group tag.TagId by tag.ContentId into g
where g.Distinct().Count() == tagCount
select g.Key;
IQueryable<Content> query = myDC.Contents
.Where(c => subQuery.Contains(c.ContentId));
I haven't tested this and the Distinct bit might be off a little. Check the generated sql to be sure.
Upvotes: 2
Reputation: 164341
Use All instead of Any; and in order to only select the KnowledgeBaseTasks, that has all the tags
(but possibly more); reverse the expression:
var tasks = (from t in _context.KnowledgeBaseTasks
where tags.All(tag => t.KnowledgeBaseTaskTags.Contains(tag))
select KnowledgeBaseTaskViewModel.ConvertFromEntity(t)
).ToList();
Upvotes: 3