How do I search for an array of values in a many to many relation?

Question

Hey

I have a problem with my LINQ to Entity model many to mant relation. I am new to both C# and LINQ, so bear with me.

I have a model containing pictures and tags, where each picture can have many tags, and each tag can be on many pictures. In the db there is a normal relation table, but in the object model I see it as picture.tags (as a list) and tag.pictures (as a list). A search query contains several tags, and the result of the search is to contain all pictures that are tagged with all the tags (but maybe more) I have searched for. The number of tags to search for is not fixed.

How can this best be done?

Answer 1

There are a lot of ways to do this. Here is one way. I'm not going to claim it's "the best," but it will work.

IQueryable<Picture> FindByTags(IEnumerable<string> tags)
{
    var q = Context.Pictures;
    foreach (var tag in tags) 
    {
        q = q.Where(p => p.Tags.Any(t => t.Name == tag));
    }
    return q;
}

Answer 2

Hmm.'

I cant seem to get the second line to work, the Contex.Pictures. Context doen't allow it. And as far as I can see, this algorithm would add all pictures that match at least one tag, not only the pictures that match all tags? Or am I wrong?

Answer 3

IQueryable<Picture> FindByTags(IEnumerable<string> included, IEnumerable<string> excluded)
{
      return (from p in context.Pictures
              where (from item in p.Tags
                     where included.Contains(item.Tag)
                     select 1).Count() == included.Count()
              where (from item in p.Tags
                     where excluded.Contains(item.Tag)
                     select 1).Count() == 0
              select p);
}

This will also allow excluded, just take out the second where if you don't want it. This also only works if a picture doesn't have duplicate tags