CODEWITHSUNDEEP
c#nhibernate
jhovgaard
jhovgaard

Reputation: 560

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

Nhibernate 3.0

Hi,

Classic blog scenario:

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!

Upvotes: 2

Views: 294

Answers (2)

Andrew T Finnell
Andrew T Finnell

Reputation: 13628

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

Upvotes: 2

Justin Shield
Justin Shield

Reputation: 2380

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.

Upvotes: 0

Related Questions