Why doesn't nHibernate support LIMIT when doing "join fetch"?

Question

In nHibernate, if you do an HQL query with "join fetch" to eagerly load a child collection, nHibernate will ignore SetMaxResults and SetFirstResult and try to retrieve every single item from the database. Why? This behavior is specific to HQL, as ICriteria supports eager loading (with an outer join, though) and LIMIT.

There are more details here http://www.lesnikowski.com/blog/index.php/nhibernate-ignores-setmaxresults-in-sql/.

Further on the topic and regarding other issues with "join fetch" (such as getting a Cartesian product as a result): as pointed out here and here, it is possible to eagerly load child collections with both HQL and ICriteria. However, ICriteria doesn't allow inner joins (resulting in a Cartesian product), and HQL doesn't allow paging the results on the DB side.

and what to do if a have to do the same thing but paged?

SetMaxResults, but that might cause issues with the loading. At that point, I might want to execute two totally separate queries, first to get just the entities, then to load the related associations.

So what is the issue with paging here and what would be the possible solution?

Answer 1

It looks like nHibernate is trying to populate the whole child collection by always doing an outer join on eagerly loaded collections, which results in a nonidentical set of rows with identical left part.

 Id | CollectionId
-------------------
  1 | 1
  1 | 2
  2 | 3
  3 | 4
  3 | 5

You get the idea. LIMIT on such a row set would have no meaning, as you practically would restrict nHibernate's internal data, instead of restricting the result set you are really interested in. I am, however, yet to find a way to do paging with eagerly loading. I think it might be impossible to do with just one query.