linq query for tag system - search for multiple tags

Question

I have two tables, Tags(tagid, postid, tagname) and posts(postid, name, ...)
now i want to make a query that returns me all posts that have a generic amount of tags. like: i want all posts that have the tag asp.net AND jquery

as i said, the amount of tags to look for is generic

how can i do something like that?

thx

update 17.11.2009: there is one problem: the relation betwenn the tables does not exist, because my primary key is on 2 fields (for versioning) how can i make it without a relation? Im using Linq To Entities

also, the query should have good performance, and should not make thousands of server requests.

Answer 1

You don't say if you're using L2S, L2O, L2E, or what. So here's an extension method which works for all of them. I'm making some guesses about the layout of your objects, so you may have to correct some of this.

public static IQueryable<Post> WhereHasAllTags(this IQueryable<Post> posts, IEnumerable<string> tags)
{
    var q = posts;
    foreach (var tag in tags)
    {
        q = q.Where(p => p.Tags.Any(t => t.Name == tag));
    }
    return q;
}

Now use it:

var filtered = Context.Posts.WhereHasAllTags(new [] { "asp.net", "jquery" } );

Answer 2

Your best bet is to look into LinqKit to dynamically build predicates. That will allow you to join multiple predicates together, so you would just loop over the tags to build up the final expression.

There are special considerations if you are using Entity Framework versus Linq2SQL, so it's a little difficult to post the specific code, but the examples on that page should be enough to point you in the right direction.