LINQ to SQL many to many, contains

Question

I have the following DB (simplified)

Image - ImageTag - Tag

ImageTag is a joining table to form the many to many relationship.

I want to make a method which returns all images which contain x tags, this is what I have started with:

public static IQueryable<Image> WithTags(this IQueryable<Image> qry, IEnumerable<Tag> tags)
{
    return from i in qry //uhhhh
}

But as you can see, I am a little stumped!

I know how I would do it with normal SQL but I am a little stumped with the LINQ syntax for this, any ideas?

--

Edit

It should match any image having any of the tags

So for example, if in the "qry" variable, there is an image with tags 1,2,3.... if you pass in the tags variable 1 and 2, it will match

Similary, if you passed 1,2,4 - It should still match even though it doesnt have 4

If you passed 3 and 4, it would also match

---

Edit 2

If it could order the images returned by the number of matches, that would be amazing. So for instance if you passed in 3 tags and an image had all 3 tags, it would be higher up than an image which only matched 1

Answer 1

Assuming you want to select all images where the supplied tags is a subset (rather than an exact match) of the images actual tags, I think this should do it:

public static IQueryable<Image> WithTags(this IQueryable<Image> qry, IEnumerable<Tag> tags)
{
    return 
        from i in qry
        from iTags in i.ImageTags.Select(it =>it.Tag)
        where !tags.Except(iTags).Any() //* See below
        select i;

}

*I borrowed the subset clause from: http://stackoverflow.com/questions/332973/linq-check-whether-an-array-is-a-subset-of-another

Answer 2

I'm assuming you have a relationship set up on Image to access its ImageTags - if not, you can use a join clause.

You can use Contains on your (small) local collection to pass those values to the server:

return from i in qry
       from it in i.ImageTags
       where tags.Contains(it.Tag)
       select i;

Rather than Tag entities you may need to use a key:

       where tags.Select(t => t.ID).Contains(it.Tag.ID)

See also: Creating IN Queries With Linq To Sql

---

You can use a group by clause to count the matches:

return from i in qry
       from it in i.ImageTags
       where tags.Select(t => t.ID).Contains(it.Tag.ID)
       group new { Image = i, it.Tag } by i.ID into g
       let tagCount = g.Count()
       orderby tagCount descending
       select g.First().Image;