Can Skip/Take reliably paginate many‑to‑many queries?

Answer

No – using Skip() and Take() on an IQueryable that includes a fetch="join" for a many‑to‑many collection does not reliably return the requested page of distinct root entities.

Why it fails (confirmed facts)

• NHibernate translates Skip/Take to an OFFSET … FETCH clause that is applied after the FROM and JOIN but before any client‑side distinct root filtering.
• When the join expands rows (one root can match many collection rows), the offset/fetch page may cut through those rows, causing either missing roots or duplicate roots after NHibernate applies DistinctRootEntityResultTransformer.
• StatelessSession does not change this behavior; it still generates the same SQL and suffers from the same duplication problem.

Steps to paginate safely (recommended pattern)

1. Query only the distinct root identifiers with Skip/Take (no fetch join). Example using HQL or Criteria:

var idQuery = session.CreateQuery(
    "select e.Id from Entity e join e.Collection c where …")
    .SetFirstResult(page * pageSize)
    .SetMaxResults(pageSize);
var ids = idQuery.List<int>(); // or long depending on Id type

2. Load the full entities for those IDs, allowing the fetch join to populate collections without affecting pagination:

var entities = session.CreateQuery(
    "from Entity e join fetch e.Collection where e.Id in (:ids)")
    .SetParameterList("ids", ids)
    .List<Entity>();

3. If you prefer the Criteria API, use a detached criteria for the ID subquery, then add a Restrictions.In on the identifier.

StatelessSession note

StatelessSession works with the same IQueryable extensions; it does not eliminate the duplicate‑root issue, so apply the ID‑subquery pattern above when using it as well.

Missing diagnostic detail

If you observe that the join cardinality is always 1‑to‑1 (each root has at most one collection row), the naive Skip/Take with fetch join would be safe. Confirm the maximum rows per root before deciding to skip the subquery step.