Why doesn't nHibernate support LIMIT when doing "join fetch"?
- by HeavyWave
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/ .