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:
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.