C# + Castle ActiveRecord: HasAndBelongsToMany and collections

Question

Let's say I have many-to-many relationship (using the ActiveRecord attribute HasAndBelongsToMany) between Posts and Tags (domain object names changed to protect the innocent), and I wanted a method like

FindAllPostByTags(IList<Tag> tags)

that returns all Posts that have all (not just some of) the Tags in the parameter. Any way I could accomplish this either with NHibernate Expressions or HQL? I've searched through the HQL documentation and couldn't find anything that suited my needs. I hope I'm just missing something obvious!

Answer 1

I don't have a system at hand with a Castle install right now, so I didn't test or compile this, but the code below should about do what you want.

Junction c = Expression.Conjunction();
foreach(Tag t in tags)
    c = c.Add( Expression.Eq("Tag", t);

return sess.CreateCriteria(typeof(Post)).Add(c).List();

Answer 2

You could also just use an IN statement

DetachedCriteria query = DetachedCriteria.For<Post>();
query.CreateCriteria("Post").Add(Expression.In("TagName",  string.Join(",",tags.ToArray()) );

I haven't compiled that so it could have errors

Answer 3

I just had the same problem and tried to read the HQL-documentation, however some of the features doesn't seem to be implemented in NHibernate (with-keyword for example)

I ended up with this sort of solution:

select p 
FROM Post p
JOIN p.Tags tag1
JOIN p.Tags tag2
WHERE
    tag1.Id = 1
    tag2.Id = 2

Meaning, dynamically build the HQL using join for each tag, then make the selection in your WHERE clause. This worked for me. I tried doing the same thing with a DetachedCriteria but ran into trouble when trying to join the table multiple times.