1

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

Chris James
  • 11,571
  • 11
  • 61
  • 89

2 Answers2

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;
dahlbyk
  • 75,175
  • 8
  • 100
  • 122
  • Assuming he means images with one of 'x' tags? I think he wants all of them to match. – MattH Aug 18 '09 at 14:40
  • Get this error on the select line: Error 4 An expression of type 'lambda expression' is not allowed in a subsequent from clause in a query expression with source type 'System.Linq.IQueryable'. Type inference failed in the call to 'SelectMany'. – Chris James Aug 18 '09 at 14:52
  • That seems to be complaining about the second from clause. If you add a line like "let iTags = i.ImageTags", what type is iTags? I would expect it to be IQueryable. – dahlbyk Aug 18 '09 at 15:23
  • Added a group by example that sorts by the number of matched tags. – dahlbyk Aug 19 '09 at 13:42
0

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: Check whether an array is a subset of another

Community
  • 1
  • 1
MattH
  • 4,166
  • 2
  • 29
  • 33
  • Ok when I tried the second line of the qry, when you do qry. - the imagetags selection doesnt appear in the intellisense, just methods available on IQueryable. If you just take a *single* Image object it would have that property.. – Chris James Aug 18 '09 at 14:49
  • Also, Except is going to expect a collection but is here being passed single Tag objects. Would need to be let iTags = i.ImageTags.Select(it => it.Tag). However, I don't believe LINQ to SQL will know how to translate Except. – dahlbyk Aug 18 '09 at 14:57
  • @dahlbyk Thanks for spotting that – MattH Aug 18 '09 at 14:57
  • I believe iTags is an IQueryable, note that i'm using select, rather than i.ImageTags.Tag Let me know if you get a problem with it MSDN seems to show Except as OK, but I admit I haven't used it myself: http://msdn.microsoft.com/en-us/library/bb399342.aspx – MattH Aug 18 '09 at 15:00
  • Your Select will indeed return an IQueryable, but "from iTags in ..." will get each Tag from it. – dahlbyk Aug 18 '09 at 15:19
  • Just tried this and iTags is an IQueryable as its per evaluated per row - I mean that Select actually returns IQueryable< IQueryable > – MattH Aug 18 '09 at 15:29
  • Sorry, for my first comment i mean when you type from Itags in i.ImageTags, you get no more intellisense after that, so you cant call "Select" – Chris James Aug 18 '09 at 16:04