/DB

Sorting and Pagination

How to sort results and paginate through large datasets using OrderBy, OrderByDesc, Limit, and Offset in Socigy.OpenSource.DB v0.1.82.

updated 3 May 20263 min readv0.1.82

Overview

The query builder exposes .OrderBy(), .OrderByDesc(), .Limit(), and .Offset() for controlling 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 all listed columns in ascending order.

// C# — sort by Name ascending, then by CreatedAt ascending
var 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

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

Combining Ascending and Descending

Chain multiple .OrderBy() and .OrderByDesc() calls. Columns are appended to the ORDER BY clause in the order the calls appear.

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

Pagination with Limit and Offset

Limit

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

// C# — fetch at most 20 rows
var page = await User.Query()
    .Limit(20)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();

Offset

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

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

var 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 is free to return rows in any sequence, making page boundaries unpredictable and potentially causing rows to appear on multiple pages or not at all.

Cursor-Based Pagination

Offset pagination becomes slow on large tables because PostgreSQL must scan and skip the first n rows on every request. Cursor-based pagination avoids this by filtering on the last-seen key value.

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

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

Full Pagination Example

// C# — typical paged API endpoint
async Task<List<User>> GetPageAsync(DbConnection conn, int page, int pageSize)
{
    return await User.Query(x => x.DeletedAt == null)
        .OrderByDesc(x => new object[] { x.CreatedAt })
        .OrderBy(x => new object[] { 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, etc.) and apply to the combined result. Sorting on the combined result is not supported — apply OrderBy to each sub-query individually before combining, or sort in C# after materializing.

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

var combined = await lhs.Union(rhs)
    .Limit(20)
    .Offset(0)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();

See Also