NHibernate pagination API vs. dialect-generated SQL ordering guarantees

Answer

No NHibernate dialect (including MsSql2012Dialect, PostgreSQLDialect, OracleDialect, etc.) guarantees stable ordering when an explicit ORDER BY clause is omitted, and the LINQ provider does not inject an implicit tie‑breaker such as the primary key. Deterministic paging must therefore be supplied by the application.

Confirmed facts

• NHibernate’s paging API (SetFirstResult/SetMaxResults or LINQ Skip/Take) delegates the generation of OFFSET/FETCH, TOP, or limit/offset syntax to the configured dialect.
• The dialect implementations emit paging syntax around the query exactly as written; they do not add an ORDER BY clause.
• Without a deterministic ORDER BY, the database may return rows in any physical order, which can cause duplicate or missing rows across page boundaries when concurrent inserts/deletes occur.
• The LINQ provider translates Skip/Take to the same dialect‑generated paging clause and does not append any implicit ordering (e.g., primary key) automatically.

Likely explanation

The paging abstraction assumes the caller supplies ordering; the dialect’s responsibility is limited to wrapping the query in the appropriate limit/offset syntax. This separation creates a correctness gap that neither side fills automatically.

Steps to enforce deterministic paging (version‑agnostic)

1. Choose a column (or column set) that is unique, non‑nullable, and stable for the entity – typically the primary key.
2. Centralize the ordering logic in a base repository or query helper so every paged query appends the same tie‑breaker.
3. Example using QueryOver:

   public IList<T> GetPage<T>(int pageIndex, int pageSize) where T : class
   {
       return Session.QueryOver<T>
           .OrderBy(Projections.Property<T>(t => t.Id)).Asc   // primary key as tie‑breaker
           .ThenBy(/* business ordering if needed */)
           .Skip(pageIndex * pageSize)
           .Take(pageSize)
           .List();
   }
   

4. Example using LINQ:

   var page = session.Query<T>
       .OrderBy(e => e.Id)               // deterministic tie‑breaker
       .ThenBy(e => e.SomeColumn)        // optional business order
       .Skip(pageIndex * pageSize)
       .Take(pageSize)
       .ToList();
   

5. Verify the generated SQL (via NHibernate logging or a SQL profiler) to confirm that the final statement contains both the business ordering and the primary‑key tie‑breaker before the limit/offset clause.

Missing diagnostic detail (only ask if it changes the recommendation)

Do you have a stable, unique column (e.g., primary key) that can be used as a tie‑breaker for every entity you page? If not, the recommendation changes to selecting a durable business key or adding a version/timestamp column to guarantee uniqueness.