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

NHibernate's Take/Skip normally generates dialect-specific SQL for pagination, but when a collection is eagerly fetched (e.g., Fetch(p => p.Children)), the query may fall back to in-memory pagination after loading the full result set. This makes the number of distinct entities returned less than the requested page size and leaves the total row count ambiguous.

The decision between SQL-level and in-memory pagination is not explicitly documented. It depends on query shape, the presence of collection fetches, and the NHibernate version. A repeatable development environment must pin the version and use a fixed dataset and dialect to observe behavior consistently.

For a focused diagnostic, how can one reliably determine whether a given query uses SQL pagination or in-memory fallback? What are the version-specific differences between NHibernate 5.2, 5.3, and 6.x for a single collection fetch with Take/Skip? And what is the recommended approach for complex fetch graphs where pagination is required?