NHibernate Server-Side Pagination with ICriteria SetMaxResults and SetFirstResult

Loading every row just to show one page

When a screen displays a list of products, orders, or customers, it is tempting to fetch all matching rows and slice them in memory. On a small table this works, but as the table grows the application server spends memory and CPU materializing objects the user will never see. NHibernate can avoid this by pushing the paging logic to the database.

What SetMaxResults and SetFirstResult do

ICriteria is NHibernate's object-oriented query API. SetFirstResult tells NHibernate how many rows to skip, and SetMaxResults tells it how many rows to return. NHibernate translates these calls into dialect-specific SQL: LIMIT and OFFSET on MySQL, PostgreSQL, and SQLite, or ROW_NUMBER-based paging on older SQL Server dialects. This keeps memory pressure low because only the requested page is materialized as entities.

These settings must be applied before the query executes, and they do not guarantee a stable row order unless you also add an explicit ORDER BY through AddOrder.

Worked example: paging a product catalog

Assume an NHibernate session factory is already configured and mapped to a Product entity with properties such as Category and Name. This example runs in a C# application using NHibernate 5.x.

using NHibernate;
using NHibernate.Criterion;

// Open a read-only session. No transaction rollback is needed because
// this example only reads data.
using (var session = sessionFactory.OpenSession())
{
    var criteria = session.CreateCriteria()
        .Add(Expression.Eq("Category", "Books"))
        .AddOrder(Order.Asc("Name"));

    // Page 2: skip the first 20 rows, return the next 20.
    criteria.SetFirstResult(20);
    criteria.SetMaxResults(20);

    var page = criteria.List();
}

sessionFactory is a placeholder for your configured ISessionFactory. Run this code in your application's data-access layer, not directly in a database console. Required permissions are the same as any NHibernate query: the database user needs SELECT on the mapped table.

NHibernate does not execute a separate count query automatically when you use SetMaxResults and SetFirstResult alone. If you need total page counts, you must issue a second query or projection, which adds its own cost on large tables.

Diagnostic decision: verify the generated SQL

Before relying on this in production, confirm that NHibernate is producing the paging clause you expect. Enable SQL logging or attach a profiler such as NHibernate Profiler, then execute the query. Look for a statement similar to:

SELECT ... FROM Product this_
WHERE this_.Category = @p0
ORDER BY this_.Name ASC
LIMIT 20 OFFSET 20

If the SQL lacks LIMIT, OFFSET, or the equivalent ROW_NUMBER construct, check your dialect configuration and ensure the criteria is not wrapped in a fetch mode that forces client-side loading. Eagerly loading large collections can negate the benefit of server-side paging because NHibernate may load related rows for every entity in the page.

Trade-offs and limitations

• Non-deterministic order without ORDER BY. Without AddOrder, the database may return rows in any physical order, so page two can contain rows that appeared on page one after an insert or delete.
• OFFSET cost grows with page depth. Skipping 10,000 rows to reach page 501 forces the database to scan and discard those rows. For deep paging, key-set pagination (filtering on the last seen ID or timestamp) is often more efficient, but it requires a different query shape and cannot be expressed directly with SetFirstResult.
• Count queries are separate. Displaying "page 1 of 50" requires an additional count against the same criteria, which can be expensive on unindexed or heavily joined tables.

Actionable closing

1. Always pair SetFirstResult and SetMaxResults with an explicit AddOrder so pages are deterministic.
2. Set both values before calling List or UniqueResult.
3. Profile the generated SQL to confirm LIMIT/OFFSET or the dialect equivalent appears.
4. Add indexes on filtered and sorted columns, such as Category and Name, to keep the underlying query fast.
5. Consider key-set pagination if users routinely page beyond the first few hundred results.

Server-side paging with ICriteria is a practical way to limit memory use, but it is not a universal fix. Verify the SQL, index the query predicates, and reassess if your users need to page deep into large result sets.