NHibernate QueryOver API ↔ SQL Server Pagination: ensuring deterministic paging with .OrderBy()

Deterministic Paging with QueryOver

To ensure stable pagination in NHibernate using .OrderBy(), .Skip(), and .Take(), you must order by a unique set of columns. If the sorting criteria are not unique, the database engine is not obligated to maintain the same row order across different page requests, which leads to duplicate or missing records.

Stability Across Dialects

NHibernate does not guarantee that a given .OrderBy() expression yields identical stability across all SQL dialects. NHibernate acts as a translator; it converts the QueryOver API into dialect-specific SQL. For example:

• SQL Server 2012+: Typically translates to OFFSET n ROWS FETCH NEXT m ROWS ONLY.
• Older SQL Server: May use ROW_NUMBER() window functions.
• PostgreSQL/MySQL: Translates to LIMIT m OFFSET n.

While the intent of the order is preserved, the underlying database engine's handling of non-unique sort keys varies. If multiple rows have the same value in the OrderBy column, the database may return them in any order, regardless of the dialect.

Performance and Large Skip Values

Across most relational databases, including PostgreSQL and Oracle, large .Skip() values result in linear performance degradation. This is because the database must typically scan and discard n rows before it can return the requested m rows. As the offset increases, the I/O and CPU cost to reach that offset increases proportionally.

Best Practices for Stable Paging

To prevent duplicates or gaps, follow these implementation rules:

1. Always include a tie-breaker: Never order by a non-unique field (like CreatedDate or Status) alone. Always append a unique identifier (usually the Primary Key) as the final sort criteria.
2. Consistent Direction: Ensure the sort direction (Ascending/Descending) is explicitly defined for all tie-breakers.

// Example of deterministic paging
var results = session.QueryOver<Order>()
    .OrderBy(x => x.OrderDate).Asc
    .OrderBy(x => x.Id).Asc // Unique tie-breaker ensures stability
    .Skip(pageIndex * pageSize)
    .Take(pageSize)
    .List();

Verification Steps

To verify your pagination is deterministic, perform the following:

• Inspect Generated SQL: Use a tool like SQL Server Profiler or NHibernate's log4net output to confirm if the dialect is using OFFSET/FETCH or ROW_NUMBER().
• Test with Collisions: Insert multiple records with the exact same value in your primary sort column and navigate through pages to check for row jumping.

Diagnostic Requirement: To provide a more specific performance recommendation, please specify the version of SQL Server (or other DB) and the NHibernate version currently in use, as the translation strategy for .Skip() changed significantly between older and newer dialects.