NHibernate Queries: Find all users that are members of the same blogs as this user

time to read 3 min | 570 words

Let us assume that we have the following model:

  • User
    • n:m -> Blogs
      • n:m -> Users

Given a user, how would you find all the users that are members of all the blogs that the user is a member of?

Turn out that NHibernate makes it very easy:

DetachedCriteria usersForSameBlog = DetachedCriteria.For<User>()
	.CreateCriteria("Users", "user")
	.Add(Subqueries.PropertyIn("id", usersForSameBlog))

And the resulting SQL is:

SELECT this_.Id        AS Id5_0_,

       this_.Password  AS Password5_0_,

       this_.Username  AS Username5_0_,

       this_.Email     AS Email5_0_,

       this_.CreatedAt AS CreatedAt5_0_,

       this_.Bio       AS Bio5_0_

FROM   Users this_

WHERE  this_.Id IN (SELECT this_0_.Id AS y0_

                    FROM   Users this_0_

                           INNER JOIN UsersBlogs blogs4_

                             ON this_0_.Id = blogs4_.UserId

                           INNER JOIN Blogs blog1_

                             ON blogs4_.BlogId = blog1_.Id

                           INNER JOIN UsersBlogs users6_

                             ON blog1_.Id = users6_.BlogId

                           INNER JOIN Users user2_

                             ON users6_.UserId = user2_.Id

                    WHERE  this_0_.Id = @p0)