Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,026 | Comments: 44,842

filter by tags archive

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)


Jon Skeet

I'm probably being stupid, but doesn't that SQL find the users that are members of any of the same blogs that the specified user is a member of? (Rather than all, as specified in the post.)

And out of interest, am I right in saying we don't actually need the join to Blogs at all? Assuming there are appropriate constraints, the subselect could be:

SELECT this0.Id AS y0_

FROM Users this0

INNER JOIN UsersBlogs blogs4_

ON this0.Id = blogs4_.UserId

INNER JOIN UsersBlogs users6_

ON blogs4.BlogId = users6.BlogId

INNER JOIN Users user2_

ON users6.UserId = user2.Id

WHERE this0.Id = @p0

Hopefully the query optimiser in the DB would figure this out though :)


Ayende Rahien


Yes, you are correct, I wasn't precise enough.

As for skipping the blog's join, it would be valid, but it is likely that using the fully path will allow the DB to utilize the FK indexes.

Craig Neuwirt

Maybe I am reading it wrong, but shouldn't the projection be something like

.SetProjection(Property.ForName("user.id")) so we get the related user ids?

Ayende Rahien

No, create criteria means that you are now in a sub criteria, so the entity you now refer to is the user.

If I would have used the CreateAlias(), then I would have to do so.

"user.id" would work as well, btw.

Comment preview

Comments have been closed on this topic.


No future posts left, oh my!


  1. Technical observations from my wife (3):
    13 Nov 2015 - Production issues
  2. Production postmortem (13):
    13 Nov 2015 - The case of the “it is slow on that machine (only)”
  3. Speaking (5):
    09 Nov 2015 - Community talk in Kiev, Ukraine–What does it take to be a good developer
  4. Find the bug (5):
    11 Sep 2015 - The concurrent memory buster
  5. Buffer allocation strategies (3):
    09 Sep 2015 - Bad usage patterns
View all series


Main feed Feed Stats
Comments feed   Comments Feed Stats