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

I guess you can t use .Contains() using EF 3.5 which I have had a problem with. I got around this by using this "WhereIn" extension

Contains() workaround using Linq to Entities?

put this into a static class then you could use something like:

IQueryable<Post> PostsWithByTags(IEnumerable<string> tagNames)
{
    var postIds = context.Tags.Select(t=>t.postid); 

    foreach (var tag in tagNames)
    {
       postIds = context.Tags
                     .WhereIn(t=> t.postid, postIds)
                     .Where(t=>t.tagname == tag);
    }

    return context.Tags.Where( t=> t.posId, postIds)
}

I really think you should look at having relationships between your tables though.

Answer 2

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 3

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.

Answer 4

IQueryable<Post> FilterByTags(IQueryable<Post> posts, IEnumerable<Tag> tags)
{
    foreach (var tag in tags)
    {
        posts = posts.Where(post => post.Tags.Contains(tag));
    }
    return posts;
}

The IQueryable<Post> you pass in will have filters added to it to ensure it references each Tag in the list.

This is a generic implementation which you may have to tweak depending on your LINQ provider

友情链接