.net - Select items by tag when searching multiple tags

Question

I'm struggling a bit here so I thought why not ask:

Every entity in my system has a list of tags (a list of strings), and I want to be able to search for multiple tags at once.

I have a IQueryable to work with. Every Entity has a IList called Tags and my input parameter is a IList.

I simply could go through all tags and do IQueryable.Where(p => p.Tags.Contains(currentTag), but that would not scale very well with many tags as input, and also I have the feeling that this could be done inside LinQ.

Hope anyone has an Idea.

Edit: Clarification of question: I search for a way to only select Items from my IQueryable that contain ALL supplied parameter tags (of IList).

greetings Daniel / Tigraine

Answer 1

From here, this is some sql that will work for you:

SELECT entityID
FROM tags
WHERE tagID in (...) --taglist
GROUP BY entityID
HAVING COUNT(DISTINCT tagID) = ... --tagcount

Now the trick is getting Linq to produce it... Here's some LinqToSql code:

public List<int> GetEntityIds(List<int> tagIds)
{
  int tagCount = tagIds.Count;

  CustomDataContext myDC = new CustomDataContext();

  List<int> entityIds = myDC.Tags
    .Where(t => tagIds.Contains(t.TagId))
    .GroupBy(t => t.entityId)
    .Where(g => g.Select(t => t.TagId).Distinct().Count() == tagCount)
    .Select(g => g.Key)

  return entityIds;
}

A few caveats apply:

• List(T).Contains is translated by LinqToSql, but LinqToEntities will not translate it. You will instead get a runtime exception.
• IList.Contains... nobody translates that. Use List(T) instead.
• There is a parameter count limit in effect for sql server. It's approximately 2000 parameters (higher, but lower than 2500). If you need to use more than 2000 tags, you should seek a different solution.
• I wrote this without tools, after midnight. It's probably not perfect.