Pagination with collection fetch: ambiguous row counts and in-memory fallback

Answering the Core Question

When a query contains a Fetch(p => p.Children) (or fetch join in HQL), NHibernate expands the parent rows into a Cartesian product. setMaxResults and setFirstResult are applied to that raw row set, not to the distinct parent entities. If the product contains fewer unique parents than the requested page size, NHibernate will load the entire result set and perform pagination in memory. This is why the page may contain fewer rows and the total count appears ambiguous.

How to Tell Whether SQL or In‑Memory Pagination Is Used

1. Enable SQL logging. In hibernate.cfg.xml set show_sql=true (or format_sql=true) and run the query. Inspect the generated SQL for LIMIT/OFFSET (PostgreSQL, MySQL) or ROWNUM / FETCH FIRST (Oracle) clauses.
2. Count the rows returned. Execute the same query without setMaxResults and setFirstResult and note the ResultSet size. If that size is larger than the distinct parent count, you have a multiplication problem.
3. Check the result list size. After applying pagination, compare list.size() with the number of distinct parents you expect. If list.size() is less than the page size, NHibernate has fallen back to in‑memory paging.
4. Verify NHibernate’s internal state. Enable hibernate.generate_statistics=true and examine the SessionFactoryStatistics for queryExecutionCount vs queryExecutionPlanCacheSize. A high number of “ResultSet rows fetched” relative to “entities returned” indicates row multiplication.

Version‑Specific Behaviour

Version	Collection Fetch Handling	Known Pagination Quirk
5.2.x	Plain fetch join; no automatic distinct handling.	Direct in‑memory fallback when row count < page size.
5.3.x	Introduced maxFetchDepth and fetchSize adjustments, but setMaxResults still applies to raw rows.	Same multiplication issue; occasional “distinct” option via CriteriaQuery.setDistinct(true) helps but not guaranteed.
6.x	Enhanced distinct handling for root entities and support for fetchMode=JOIN with distinct=true in Criteria.	In some dialects (e.g., PostgreSQL) the LIMIT/OFFSET is still applied to the raw row set; NHibernate will still perform in‑memory paging if the distinct parent count is insufficient.

Recommended Approach for Complex Fetch Graphs

• Use a two‑step strategy: first run a SELECT COUNT(*) query on the root entity without any fetch join to obtain the true total; second fetch the paged root entities with setFirstResult and setMaxResults, but avoid eager collection fetches.
• After retrieving the page of roots, load the required collections lazily or with a separate batch-size strategy (e.g., @BatchSize(25)) or a subselect fetch mode.
• If eager fetching is unavoidable, consider projecting to a DTO that contains only the root fields, then manually join collections in a second query.
• When using Criteria, set setDistinct(true) and setResultTransformer(Transformers.DISTINCT_ROOT_ENTITY) to reduce row multiplication, but still verify the distinct count.
• For very large result sets, use ScrollMode.FORWARD_ONLY or ScrollableResults to stream rows and apply in‑memory slicing only when the dataset is fully loaded.

One Diagnostic Detail That May Change the Recommendation

Which database dialect you are targeting can affect how NHibernate generates the LIMIT/OFFSET clause and whether the dialect supports row‑number based pagination. Knowing the dialect (e.g., PostgreSQL, Oracle, SQL Server, MySQL) will help decide if you can rely on the database’s pagination or must enforce in‑memory handling.

Question for you: What is the database dialect you are using with NHibernate?