NHibernate Paging: Keeping Skip/Take in SQL When Fetching Collections

You add a new admin screen that lists orders with their line items. In development it looks fine—ten orders, a few dozen lines. In production the same page times out or spikes memory. The generated SQL returned thousands of rows, and NHibernate quietly applied your Skip and Take after the database had already sent everything. The useful takeaway: when you join-fetch a collection in a paged NHibernate query, the database may not do the paging at all.

Join-Fetching a Collection Changes the Paging Contract

NHibernate can page root rows with SetFirstResult/SetMaxResults or LINQ Skip/Take. The problem appears when you add a collection fetch, such as left join fetch o.OrderLines in HQL or .Fetch(o => o.OrderLines) in LINQ. A single order with fifty line items becomes fifty rows in the SQL result set. To return ten distinct orders, NHibernate may read all matching rows into memory, discard duplicates, and only then apply the offset and limit. Depending on dialect and version, it can emit a warning that it is applying results in memory.

Fetching a many-to-one association—say o.Customer—usually does not multiply root rows, because each order references one customer. Collections are different. Always inspect the generated SQL: the same mapping can be safe on one database and expensive on another because dialect and LINQ rewrites change the plan.

Page Identifiers, Then Batch the Graph with Future Queries

A safer pattern is to page only identifiers or scalar projections, then load the page and needed associations in a second step. NHibernate future queries can batch those follow-ups into one round trip without turning the paged query into a collection join fetch.

The example below uses HQL because the syntax is stable across NHibernate versions, but the same idea applies to QueryOver and LINQ. Run this code inside an open NHibernate session and transaction in your data-access layer.

// Step 1: page identifiers only
var ids = session.CreateQuery(
    "select o.Id from Order o where o.Status = :status order by o.Id")
    .SetString("status", "Open")
    .SetFirstResult(20)
    .SetMaxResults(10)
    .List<int>();

// Step 2: batch the graph with future queries
var orders = session.CreateQuery(
    "select o from Order o where o.Id in (:ids)")
    .SetParameterList("ids", ids)
    .Future<Order>();

var lines = session.CreateQuery(
    "select l from OrderLine l where l.Order.Id in (:ids)")
    .SetParameterList("ids", ids)
    .Future<OrderLine>();

// Enumerating either future executes both in one round trip
var page = orders.ToList();

Future queries are not executed when you build them; they run when the first result is enumerated. Keep the session and transaction open until enumeration finishes, or you will trigger lazy-loading exceptions after the session closes.

Verify the SQL, Not Just the Results

Small tests with ten rows can pass even when paging happens in memory. Production data volumes expose the difference. Enable the NHibernate.SQL logger in your application configuration and inspect the paged query for database-side paging syntax—LIMIT/OFFSET on PostgreSQL and MySQL, or TOP/OFFSET FETCH on SQL Server—and for warnings about applying results in memory.

<logger name="NHibernate.SQL">
  <level value="DEBUG"/>
</logger>

Where to run this: your application configuration file (App.config or Web.config) or your logging framework setup. Required permissions: ability to deploy configuration changes. Placeholder: the logger name NHibernate.SQL is fixed; adjust only the level and appender. Expected check: the paged query contains dialect-appropriate paging syntax and no in-memory paging warning. Risk: DEBUG-level SQL logging is verbose and adds overhead; do not leave it on in high-traffic production paths.

Create a parent with many children, page once with a collection fetch and once with ID paging plus futures, then compare SQL text, rows returned by the database, and round trips. Run the comparison on your target NHibernate version and database dialect before trusting the pattern.

Trade-offs and When Not to Use This

ID paging plus futures costs extra round trips and introduces a consistency window: the second query can see newer data than the page. For read-mostly graphs, that is often acceptable. When it is not, consider alternatives:

• Batch-size on collections can reduce N+1 selects without changing the paged query.
• Lazy extra collections load only the slice you touch, which helps when the full graph is rarely needed.
• Caching can make repeated graph loads cheap enough that a single round trip matters less.

If you must return a consistent snapshot, wrap the reads in a transaction with an appropriate isolation level, or load the graph in a single query and accept the row multiplication.

Checklist Before You Ship the Page

1. Turn on NHibernate.SQL and confirm the paged query uses database-side paging.
2. Compare row counts for the collection-fetch version and the ID-paging version under realistic data volumes.
3. Enumerate future queries inside the session and transaction; assert no lazy-loading exceptions afterward.
4. Verify the exact LINQ, QueryOver, or HQL syntax against your NHibernate version and dialect—do not assume identical SQL across versions.

Paging bugs rarely show up in a clean development database. They show up when the join-fetch multiplies rows and the ORM quietly moves the limit into application memory. Inspect the SQL, page identifiers first, and batch the rest.