/DB

Sorting and Pagination

Sort results and page through large datasets with OrderBy, OrderByDesc, Limit, and Offset.

updated 5 Jun 20263 min readv0.3.2View as Markdown

Overview

The query builder exposes .OrderBy(), .OrderByDesc(), .Limit(), and .Offset() for sort order and result paging. .OrderBy() and .OrderByDesc() are available on regular queries only. .Limit() and .Offset() work on regular queries and JOIN queries. Set-operation builders support .Limit() and .Offset() on the combined result but not sorting.

Sorting

Ascending Order

Pass an array of columns to .OrderBy(). PostgreSQL sorts every listed column in ascending order.

// sort by Name ascending, then by CreatedAt ascending
List<User> users = await User.Query()
    .OrderBy(x => new object?[] { x.Name, x.CreatedAt })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "name" ASC, "created_at" ASC

Descending Order

// sort by Score descending
List<User> topScorers = await User.Query()
    .OrderByDesc(x => new object?[] { x.Score })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "score" DESC

Combining Ascending and Descending

The builder holds a single ORDER BY clause: .OrderBy() and .OrderByDesc() each replace it, and the call's direction applies to every column in the array. Calling them more than once does not append; the last call wins. To sort columns in mixed directions, pass one array to a single .OrderBy() call and wrap individual columns with the OrderBy.Desc(...) / OrderBy.Asc(...) helpers (from Socigy.OpenSource.DB.Core.SyntaxHelper.DB).

using static Socigy.OpenSource.DB.Core.SyntaxHelper.DB;

// sort by Score DESC, then by Name ASC
List<User> results = await User.Query()
    .OrderBy(x => new object?[] { OrderBy.Desc(x.Score), x.Name })
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "score" DESC, "name"

Pagination with Limit and Offset

Limit

.Limit(n) emits a SQL LIMIT clause and caps the number of rows returned. .Top(n) is an alias.

// fetch at most 20 rows
List<User> page = await User.Query()
    .Limit(20)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();

Offset

.Offset(n) emits a SQL OFFSET clause and skips the first n rows. It is almost always used with .Limit().

// fetch page 3 (zero-based pages of 20)
int pageSize = 20;
int pageNumber = 2; // 0-indexed, page 3

List<User> page3 = await User.Query()
    .OrderBy(x => new object?[] { x.CreatedAt })
    .Limit(pageSize)
    .Offset(pageNumber * pageSize)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: ORDER BY "created_at" ASC LIMIT 20 OFFSET 40
WARNING
Always pair Offset with OrderBy. Without a deterministic sort order the database may return rows in any sequence, making page boundaries unpredictable and risking rows that appear on multiple pages or none at all.

Cursor-Based Pagination

Offset pagination slows down on large tables because PostgreSQL scans and skips the first n rows on every request. Cursor-based pagination avoids this by filtering on the last-seen key value.

// cursor-based next page using the last seen Id
Guid lastSeenId = previousPage.Last().Id;

List<User> nextPage = await User.Query(x => x.Id > lastSeenId)
    .OrderBy(x => new object?[] { x.Id })
    .Limit(20)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
TIP
Cursor-based pagination needs a stable, sortable, unique column (an auto-incrementing integer or a time-ordered UUID). It cannot jump to arbitrary pages, but it scales to billions of rows where OFFSET would time out.

Full Pagination Example

using static Socigy.OpenSource.DB.Core.SyntaxHelper.DB;

// typical paged API endpoint
async Task<List<User>> GetPageAsync(DbConnection conn, int page, int pageSize)
{
    return await User.Query(x => x.DeletedAt == null)
        .OrderBy(x => new object?[] { OrderBy.Desc(x.CreatedAt), x.Id })
        .Limit(pageSize)
        .Offset(page * pageSize)
        .WithConnection(conn)
        .ExecuteAsync()
        .ToListAsync();
}

Pagination on Set Operations

.Limit() and .Offset() are available on set-operation builders (Union, Intersect, Except, and so on) and apply to the combined result. Sorting on the combined result is not supported. Apply OrderBy to each sub-query before combining, or sort in C# after materializing.

// paginate a UNION result
var lhs = User.Query(x => x.Active == true);
var rhs = User.Query(x => x.IsPremium == true);

List<User> combined = await lhs.Union(rhs)
    .Limit(20)
    .Offset(0)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();

See Also