Do not page without deterministic ordering

Pagination needs more than an OrderBy. It needs an order that uniquely identifies the position of every row.

Sorting only by CreatedAt is not deterministic when several rows share the same timestamp. The database is free to return those tied rows in a different order on the next query. A row can then appear twice or be skipped as the client moves between pages.

Add a stable, unique tie-breaker:

var orders = await db.Orders
    .AsNoTracking()
    .OrderByDescending(order => order.CreatedAt)
    .ThenByDescending(order => order.Id)
    .Skip(pageIndex * pageSize)
    .Take(pageSize)
    .ToListAsync(cancellationToken);

The combined CreatedAt and Id order is fully unique as long as Id is unique. Apply the same columns and directions everywhere that fetches a page, builds a cursor, or depends on a stable sequence. The corresponding database index should follow the query’s filter and ordering shape.

Prefer non-null sort keys for pagination. If a sort key can be null, define its null ordering explicitly and apply the same rule when comparing cursor values.

Do not rely on insertion order or assume that a relational database returns primary-key order automatically. Without an explicit OrderBy, there is no ordering contract.

A deterministic order does not make offset pagination immune to concurrent inserts and deletes. Rows can still shift before the requested offset between two queries. When users mostly move forward or backward through a large list, keyset pagination is usually the stronger choice. It uses the last seen sort values instead of counting past earlier rows.

Use offset pagination when random page access is a real requirement. Whichever pagination method you choose, make the order fully unique first.