Ayende @ Rahien

Hi!
My name is Ayende Rahien
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 5,949 | Comments: 44,548

filter by tags archive

NHiberante: Querying Many To Many associations using the Criteria API


Over a year ago I was asked how we can query a many to many association with NHibernate using the criteria API. At the time, that was not possible, but the question came up again recently, and I decided to give it another try.

First, let us recall the sample domain model:

Blog
    m:n Users
    1:m Posts
        n:m Categories
        1:m Comments

And what we want to do, which is to find all Posts where this condition is met:

Blog.Users include 'josh' and Categories includes 'Nhibernate'  and a Comment.Author = 'ayende'.

At the time, it wasn't possible to express this query using the criteria API, although you could do this with HQL. Doing this with HQL, however, meant that you were back to string concat for queries, which I consider a bad form.

I did mention that a year have passed, right?

Now it is possible, and easy, to do this using the criteria API. Here is the solution:

DetachedCriteria blogAuthorIsJosh = DetachedCriteria.For<User>()
	.Add(Expression.Eq("Username", "josh")
	.CreateCriteria("Blogs", "userBlog")
	.SetProjection( Projections.Id())
	.Add(Property.ForName("userBlog.id").EqProperty("blog.id"));

DetachedCriteria categoryIsNh = DetachedCriteria.For(typeof(Category),"category")
    .SetProjection(Projections.Id())
    .Add(Expression.Eq("Name", "NHibernate"))
    .Add(Property.ForName("category.id").EqProperty("postCategory.id "));

session.CreateCriteria(typeof (Post),"post")
    .CreateAlias("Categories", "postCategory")
    .Add(Subqueries.Exists(categoryIsNh))
    .CreateAlias("Comments", "comment")
    .Add(Expression.Eq("comment.Name", "ayende"))
    .CreateAlias("Blog", "blog")
    .Add(Subqueries.Exists(blogAuthorIsJosh))
    .List();

And this produces the following SQL:

SELECT This_.Id              AS Id1_3_,
       This_.Title           AS Title1_3_,
       This_.TEXT            AS Text1_3_,
       This_.Postedat        AS Postedat1_3_,
       This_.Blogid          AS Blogid1_3_,
       This_.Userid          AS Userid1_3_,
       Blog3_.Id             AS Id7_0_,
       Blog3_.Title          AS Title7_0_,
       Blog3_.Subtitle       AS Subtitle7_0_,
       Blog3_.Allowscomments AS Allowsco4_7_0_,
       Blog3_.Createdat      AS Createdat7_0_,
       Comment2_.Id          AS Id4_1_,
       Comment2_.Name        AS Name4_1_,
       Comment2_.Email       AS Email4_1_,
       Comment2_.Homepage    AS Homepage4_1_,
       Comment2_.Ip          AS Ip4_1_,
       Comment2_.TEXT        AS Text4_1_,
       Comment2_.Postid      AS Postid4_1_,
       Categories7_.Postid   AS Postid__,
       Postcatego1_.Id       AS Categoryid,
       Postcatego1_.Id       AS Id3_2_,
       Postcatego1_.Name     AS Name3_2_
FROM   Posts This_
       INNER JOIN Blogs Blog3_
         ON This_.Blogid = Blog3_.Id
       INNER JOIN Comments Comment2_
         ON This_.Id = Comment2_.Postid
       INNER JOIN Categoriesposts Categories7_
         ON This_.Id = Categories7_.Postid
       INNER JOIN Categories Postcatego1_
         ON Categories7_.Categoryid = Postcatego1_.Id
WHERE  EXISTS (SELECT This_0_.Id AS Y0_
               FROM   Categories This_0_
               WHERE  This_0_.Name = @p0
                      AND This_0_.Id = Postcatego1_.Id)
       AND Comment2_.Name = @p1
       AND EXISTS (SELECT This_0_.Id AS Y0_
                   FROM   Users This_0_
                          INNER JOIN Usersblogs Blogs3_
                            ON This_0_.Id = Blogs3_.Userid
                          INNER JOIN Blogs Userblog1_
                            ON Blogs3_.Blogid = Userblog1_.Id
                   WHERE  This_0_.Username = @p2
                          AND Userblog1_.Id = Blog3_.Id);

I am pretty sure that this is already in 1.2, but I don't have that handy to check.


Comments

Dave^2

Wish you'd posted this a couple of days earlier :-) I was trying to figure out many-to-many queries with NHibernate the other day, and posted the results here:

http://davesquared.blogspot.com/2007/12/messing-around-with-nhibernate.html

Mine is a trivial example, but I was still very impressed with how easy it was. All worked fine with NHibernate 1.2.

Nate Taylor

Thanks for posting this.

This is my first attempt at using CreateCriteria, and I got it working for the most part. However, I seem to not have the associations tied correctly in my hbm file. Would it be possible to show what the hbm files would look like (at least the relevant parts) for the above tables?

Thanks

Ayende Rahien

You can find those here:

https://rhino-tools.svn.sourceforge.net/svnroot/rhino-tools/trunk/SampleApplications/NHibernate.Experiments.Blog

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

No future posts left, oh my!

RECENT SERIES

  1. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
  2. Special Offer (2):
    27 May 2015 - 29% discount for all our products
  3. RavenDB Sharding (3):
    22 May 2015 - Adding a new shard to an existing cluster, splitting the shard
  4. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  5. Interview question (2):
    30 Mar 2015 - fix the index
View all series

RECENT COMMENTS

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats