Recently, I ran into an issue with NHibernate Criteria queries. The scenario is the following:
User has a many-many association with Role and Role has a many-many association with Site. I simply wanted all users that belong to a given site (and a whole slew of other optional parameters, therefore the Criteria query).
With hql this is simple:
With a criteria query, it appeared a little bit harder. First I tried this:
Note the absence of the distinct keyword anywhere in the query. A nice Cartesian Product was the result due to the joins on the linking tables. Some googling showed that we can get distinct results with Criteria queries by applying the DistinctRootEntityResultTransformer via Criteria.SetResultTransformer() but that all happens in memory and not in the database query (call me old-fashioned, but I’d like my database results properly filtered :)).
Finally, I found the solution in using subqueries:
Yes, that’s an insane amount of code to do something that simple, but it works and the generated SQL is highly efficient :). Notice the distinct projection in the second DetachedCriteria (userIdsForRoles).
I’d really appreciate it if somebody has any suggestions for improvement.