Paging Large Result Sets with NHibernate ICriteria: SetFirstResult and SetMaxResults in Practice

When a result set is too large to load

An application that lists products, orders, or audit records eventually meets a table with tens of thousands of rows. Loading the whole set into memory to display twenty items is wasteful and slow. NHibernate's ICriteria API lets you ask the database for just the slice you need, using SetFirstResult (the offset) and SetMaxResults (the page size). The catch is that these two methods only behave well when you pair them with explicit ordering, a matching count query, and a look at the SQL they generate.

What SetFirstResult and SetMaxResults actually do

Both methods map to the row-limiting syntax of the database dialect. On SQL Server 2012 and later, NHibernate typically emits OFFSET ... ROWS FETCH NEXT ... ROWS ONLY; on PostgreSQL, MySQL, and SQLite it emits LIMIT with OFFSET; Oracle dialects use their own variants. The slicing happens in the database rather than in .NET, so the session does not materialize rows you will never display.

The semantics have been stable across NHibernate 3.3, 5.x, and 6.0. Later versions added lambda-based overloads to the criteria builder, but the offset and page-size behavior did not change.

A worked example: paging a product catalog

Assume a Product entity with an indexed Name column and an IsActive flag. The snippet below fetches one page plus a separate total count. It is illustrative code, not a tested sample: run it against your own schema and confirm the SQL it produces.

int pageSize = 20;
int pageIndex = 3; // zero-based

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    // Count matching rows without loading entities
    var totalCount = session.CreateCriteria<Product>()
        .Add(Restrictions.Eq("IsActive", true))
        .SetProjection(Projections.RowCount())
        .UniqueResult<int>();

    // Fetch only the requested page
    var products = session.CreateCriteria<Product>()
        .Add(Restrictions.Eq("IsActive", true))
        .AddOrder(Order.Asc("Name"))
        .SetFirstResult(pageIndex * pageSize)
        .SetMaxResults(pageSize)
        .List<Product>();

    tx.Commit();
}

Three details matter:

• The count query uses Projections.RowCount(), so it returns a number instead of entities.
• Both criteria share the same WHERE clause, so the page and the total describe the same set of rows.
• Ordering is explicit. Without an ORDER BY, the database may return rows in any order, and a row can appear on two pages or on none.

Trade-offs and pitfalls to check

Issue	Impact	Mitigation
Deep-page offsets	A large offset forces the engine to walk and discard preceding rows, so page 5000 can be far slower than page 1.	Keep page sizes modest and consider keyset pagination (seek on an indexed column) for very deep pages.
Lazy-loaded collections	SetMaxResults limits the root entity query only; each collection you touch issues another SELECT.	Use SetFetchMode or batch fetching for associations the page actually needs.
Projections	Projecting a subset of columns means lazy properties are not available on the returned objects.	Fetch the full entity, or include every column the view needs in the projection.
Dialect differences	Older dialects may emulate OFFSET with subqueries, which can perform differently.	Test against the exact database version you deploy, and keep the dialect package current.

Verifying the generated SQL

Pagination bugs are easiest to catch by reading the SQL. Enable NHibernate's SQL logging, then run the paging code against a development database.

1. Configure your logging provider (log4net or Microsoft.Extensions.Logging) so the logger NHibernate.SQL is at DEBUG level.
2. Execute the page fetch for a known page index and page size.
3. Inspect the logged statement: for page index 3 and page size 20, the offset should be 60 and the fetch or limit should be 20.

If the statement contains no row-limiting clause, the slicing is happening in memory and the query is not doing what you expect. If the execution plan shows a full scan rather than an index seek, check the indexes behind your WHERE and ORDER BY columns.

An actionable next step

Write one test against a seeded dataset: insert a known number of rows, request a middle page, and assert both the returned count and the first row's identity. That single test catches the two failures that matter most here: an unstable sort order and an off-by-one offset.