Mise Paging and Ordering

Paging and ordering

OrderBy determines row order. Offset skips rows and Limit caps the returned count. Use a stable ordering when exposing pages to application consumers.

Build a page

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .Select($"{CourseTbl.CapacityCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .OrderBy($"{CourseTbl.TitleCol:raw} ASC")
    .OrderBy($"{CourseTbl.CourseIdCol:raw} ASC")
    .Offset(20)
    .Limit(10);

The course ID breaks ties between equal titles. Without a unique tie-breaker, rows with equal sort keys can move between pages even when their values do not change.

Repeated OrderBy calls add sort expressions in call order. Paging values must be nonnegative and each can be set only once. SQL Server requires ORDER BY for paging and rejects a zero limit.

Select a sort option

Choose between known SQL fragments instead of interpolating a client-provided column name or direction with :raw:

if (sortByCapacity)
{
    query.OrderBy($"{CourseTbl.CapacityCol:raw} DESC");
}
else
{
    query.OrderBy($"{CourseTbl.TitleCol:raw} ASC");
}

query.OrderBy($"{CourseTbl.CourseIdCol:raw} ASC");

Apply this choice to a query that has not already added ordering. sortByCapacity is an application boolean selected from your supported sort options.

Dialect behavior

SQL Server emits OFFSET and FETCH. PostgreSQL, MySQL, and MariaDB emit LIMIT and OFFSET. SQLite uses LIMIT and OFFSET, inserting LIMIT -1 when only an offset is requested.

The paging clauses affect SQL, rather than merely discarding rows after materialization. Use Readers to execute the query.

Seek from a known row

Large offsets can require the database to scan many skipped rows. For sequential traversal, filter from a known key and use a limit:

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .Select($"{CourseTbl.CapacityCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .Where($"{CourseTbl.CourseIdCol:raw} > {lastCourseId}")
    .OrderBy($"{CourseTbl.CourseIdCol:raw}")
    .Limit(10);

lastCourseId identifies the last row from the previous page. A multi-column sort needs a predicate accounting for each sort key. Concurrent inserts and updates still affect what a later query sees; transaction isolation and the pagination contract determine the consistency you need.