How to use Contains() in a many-to-many scenario?

Nhibernate 3.0

Hi,

Classic blog scenario:

• Blog
  • Tags
• Posts
  • Tags (many-to-many)

When I want to only show the posts containing a specific tag I use the Contains() function like this:

var tag = session.Query<Tag>().Single(x => x.Name == "C#");
var postsByTag = session.Query<Post>().Where(x => x.Tags.Contains(tag));

This works like a charm and NHibernate generates the right SQL.

However, now my user wants to get all posts containing a minimum of one of multiple tags. I ended up doing it like this:

var tags = session.Query<Tag>().Where(x => x.Name.StartsWith("Nhibernate"));
var postsByTag = session.Query<Post>().Where(x => x.Tags.Any(t => tags.Contains(t)));

This works as expected, postsByTag only contains posts that contains one of the selected tags. My problem with this is NHibernate unfortunately don't know how to translate this to SQL, so instead of using WHERE IN it simply gets all posts from the database, and then do the filtering afterwards. This is a开发者_开发问答 huge performance issue for me, because the blog can possibly have millions of posts.

Does anyone have a solution for this issue?

Thanks in advance!

After having worked on LINQ providers I can understand its problems. Thankfully though there is a better way than contains and any.

from post in session.Query<Post>
from tag in post.Tags 
where tag.Name.StartsWith("blah")
select post

HQL

You should take a look at Hibernate Query Language, there's actually a few ways to do it using HQL or even using an Alias and ICriterion.

• Nhibernate HQL where IN query
• http://ayende.com/blog/4023/nhibernate-queries-examples