Paginating authorized lists
Page authorized EF Core queries with ToPageAsync. Index the columns you sort by; SqlOS keeps every index under every node.
ToPageAsync is how SqlOS reads an authorized list, and the only way. One resource is a point check, CheckAccessAsync. There is no authorized count, Any, join, or unpaged list: each of those costs every row the caller can see. Page instead.
The rule is the one every database already has: index the columns you sort by. SqlOS keeps a copy of every index of a protected table (an entity that implements IHasResourceId) under every node of the resource tree, and one of its primary key, so OrderBy(p => p.Id) pages too. You declare nothing to SqlOS.
protected override void OnApplicationModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>(entity =>
{
entity.HasKey(p => p.Id);
entity.HasIndex(p => p.ResourceId).IsUnique();
entity.HasIndex(p => new { p.Price, p.Id }); // (Price, Id)
entity.HasIndex(p => new { p.Status, p.CreatedAt }); // (Status, CreatedAt, Id)
});
}(Price) and one on (Price, Id) are the same order, (Price, Id). Unique, filtered, descending and included columns make no difference, and one index serves ascending and descending pages. Every column of an order runs in one direction.OrderBy(p => p.Name).ThenBy(p => p.City) needs (Name, City) or (Name, City, Id). An index on Name alone does not serve it.ResourceId, and an index whose columns are exactly a foreign key's (the one EF Core adds for each foreign key by convention).HasMaxLength (or an explicit column type). The entity needs a single-column primary key, a mapped ResourceId, and a table of its own: no owned types, views, or TPT/TPC inheritance (TPH is fine; the orders live on the root type). An index or an entity that breaks a rule gets no copy; the model still builds, and startup logs one warning naming it and the fix.SqlOSFgaPage_{Table}_{Columns} in SqlOS's schema (SqlOSFgaPage_Products_Price, SqlOSFgaPage_Products_Id), so your next migration (or a model-driven tool such as EFCore.SchemaSync) creates it. Nothing is added to your own tables.var page = await db.Products
.AsNoTracking()
.Include(p => p.Location) // optional
.Where(p => p.Name.Contains(term)) // optional, any number
.OrderBy(p => p.Price) // required: the order (ThenBy allowed)
.ToPageAsync(subjectId, "product.view", pageSize: 20, cursor: request.Cursor, cancellationToken);
return Results.Ok(page.ToResponse(p => new ProductDto(p)));subjectId is the caller; its active group memberships are included. "product.view" is the permission each row's resource must grant. Both are resolved each time the page runs.
ToPageAsync returns SqlOSCursorPage<T>: Data, PageSize, NextCursor, and HasNextPage. ToResponse(selector) shapes it as { data, pageSize, nextCursor, hasNextPage }. NextCursor is null on the last page; a page that ends exactly at the last row may be followed by an empty one.
Project after the page (page.ToResponse(...) or page.Data.Select(...)), not before it.
Include, tracking and your filters apply, and checked once more. The final check can drop a stale row, never add one.Before step 1, SqlOS resolves the caller's subjects and the permission. Each step is an EF Core query, logged like any other. One Debug line per page under the SqlOS.Fga category records the rounds, the rows each step read, which step produced the page, and the rows the final check dropped.
The cursor is opaque. It carries the last row's order values, bound to the entity, the order and its direction, and you pass it back as it came.
Don't write your own position filter. No .Where(x => x.CreatedAt < lastSeen), no keyset library, no since= version parameter: pass NextCursor back. ToPageAsync seeks under each of the caller's nodes from the cursor. A hand-written position is just another filter there, so every page walks the caller's rows from the start of the order.
For a change feed, the cursor is the sync token the client stores. Keep the last NextCursor you received. NextCursor is null on the last page, so the next read from the stored cursor returns that last page again, followed by anything newer.
SqlOSCursorException. Map it to 400 with SqlOSCursorPagination.BadRequest(exception) or your own response.ArgumentException with ParamName "cursor". A client that changes the sort and keeps the cursor gets this; map it to 400 too.try
{
page = await query.OrderBy(i => i.Price).ToPageAsync(subjectId, "product.view", pageSize, cursor, ct);
}
catch (Exception exception) when (exception is SqlOSCursorException or ArgumentException { ParamName: "cursor" })
{
return Results.BadRequest(new { error = SqlOSCursorException.ErrorCode, message = exception.Message });
}Before ToPageAsync: Where, Include/ThenInclude, AsNoTracking/AsTracking, TagWith, IgnoreQueryFilters, AsSplitQuery, and one OrderBy/OrderByDescending with any number of ThenBy/ThenByDescending in the same direction, each over a mapped scalar property. The ordering comes last.
ToPageAsync throws ArgumentException for a query that does not start from a DbSet of a context deriving from SqlOSDbContext<TContext>; no OrderBy; mixed directions; an order over anything but a mapped, non-nullable scalar property; and Select, Skip, Take, Distinct, GroupBy, Join, SelectMany, Union or Concat in the chain. pageSize is 1 to 1,000.
For an unknown or inactive subject, or an unknown permission, ToPageAsync logs a warning and returns an empty page with no NextCursor, without running the page's queries.
A query ordered by columns no index covers still returns the right pages. It runs step 1 only, reading your query in that order and checking each row until a page is visible, for every caller: one who sees little of a large table reads most of it, as in any database that sorts without an index. The first time a page needs more than one batch, SqlOS logs a warning naming the index to add:
ToPageAsync scanned 1240 rows of Product ordered by CreatedAt, Id asc to fill a page of 20. No index of Products covers that order, so every caller's page reads the table in it. Add modelBuilder.Entity<Product>().HasIndex(e => e.CreatedAt) and a migration; SqlOS keeps a copy of every index under each node of the resource tree.SqlOSDbContext<TContext> keeps every page order current in the same transaction as each save: rows inserted, deleted, or updated in an order column or ResourceId, and resources added, moved, deactivated, reactivated or deleted, whether through your entities, ISqlOSResourceEntity, the resource helpers in SqlOS.Extensions, the dashboard, or the seed service. It reads what the save wrote, so values set by SaveChanges interceptors and SavingChanges handlers, cascade deletes, and order columns the database computes are included. A save's entry statements go to the database as one command; on PostgreSQL they run with JIT off and put the transaction's setting back.
It uses your transaction when there is one, inside a savepoint so that a failed save leaves your transaction as it was before the save; an ambient or enlisted System.Transactions transaction; and otherwise its own, inside the context's execution strategy.
ResourceId on paged tables (HasIndex(p => p.ResourceId)): a save that adds, moves or deletes a resource finds the rows under it by ResourceId.ExecuteUpdate/ExecuteDelete, triggers) do not update entries, and neither does an interceptor that changes paged rows during a save that changes no paged row or resource. Until you call ISqlOSFgaAuthService.RebuildPageOrdersAsync(), rows they insert are missing from pages and rows they change page at their old position. A row the caller may not see is never returned: every page row is checked once more. The rebuild rewrites every page order, one transaction per order, without a command timeout; the cost is the table's rows times the tree depth. Run it while the application is not writing to those tables: a save during a rebuild waits for it, or makes it fail with a deadlock or duplicate key, and running it again fixes that.RebuildPageOrdersAsync() once every instance runs the new version.READ_COMMITTED_SNAPSHOT), or one of the saves fails with a deadlock or duplicate key; retry it.DbContext that implements ISqlOSFgaDbContext itself) get no maintenance and no pages: ToPageAsync refuses their queries. Derive from SqlOSDbContext<TContext> to page.ResourceId, writes one entry per order per level in the save's transaction. Adding, moving, deactivating or reactivating a resource rewrites the entries of the rows under it, for every order; for a new resource that is one ResourceId lookup per order. Every index costs on every write, so index what you sort by, not every column.Grants on the nodes of your tree keep the caller's node set small; a caller with ten thousand single-row grants costs ten thousand seeks per page in step 2. See Designing your resource tree.
Each page checks grants when it runs. If grants change between pages, newly authorized rows appear and newly revoked rows disappear from the next page: the cursor is a position in the order, not a snapshot of the first page. A revoked subject stops seeing rows on the very next page.
The principal set (the subject plus its active group memberships) is resolved each time ToPageAsync runs, and each page's queries check the caller's activity again when they run. Membership and lifecycle changes apply from the next page.
SqlOS's own dashboard and admin APIs use an internal keyset helper with opaque versioned cursors — see Admin cursor pagination. That contract is for SqlOS admin endpoints; application lists page with ToPageAsync.