Implementing a Paged List API with OData in ASP.NET Core

A conference demo may show 25 products, while a production catalogue can contain 50,000 records. Returning everything increases API latency, memory usage and database work. A paged endpoint returns a manageable slice while keeping each record discoverable through filtering, sorting and controlled navigation.

OData gives ASP.NET Core a common query contract for collections. A client can use $top, $skip, $count, $filter, $orderby and $select in the URL rather than relying on a custom body format. The server can validate supported options, pass them through Entity Framework Core and shape results using OData conventions.

Convenience still needs boundaries. The application should define a default and maximum page size, prevent clients from ordering by unindexed or sensitive fields, and return clear validation errors. Paging without a stable sort can repeat or miss records, while an unrestricted $count can trigger an expensive scan across a large dataset.

Australian conditions sharpen these choices. Customers and teams may sit in Sydney, Melbourne, Brisbane, Perth or regional centres, so payload size and cloud-region latency matter. Australian understatement can be useful in discussion—a team may invite someone to “have a go”—but an API contract still needs precise names, limits and errors.

Why server-side paging matters

Offset pagination asks for a position and a size: take 25 records, then skip 50. It suits familiar numbered screens and modest datasets, but the database may still process discarded rows as $skip grows. Large files or high write volumes also make repeated offsets vulnerable to page drift when records are inserted or removed.

Pagination must follow a total order. Sort by the requested business field, then append a unique key such as Id as a tie-breaker. That prevents two products with the same name from swapping positions between requests. Indexes aligned with common filters and sort orders keep the generated SQL efficient, while cached or immutable data can reduce consistency problems. Test query plans against production-like cardinality rather than a small development dataset.

Defining query options with OData

For example, a request for the third 25-item page can use /api/Products?$top=25&$skip=50&$count=true&$orderby=Name%20asc,Id%20asc. Register only the capabilities the endpoint supports and expose the API through an OData route component. A typical service configuration looks like this:

builder.Services.AddControllers().AddOData(options =>
{
    options.EnableQueryParams(
            PageSize,
            Skip,
            SkipToken,
            Count,
            Filter,
            OrderBy,
            Select)
        .AddRouteComponents(
            "api",
            new ODataRouteComponent(typeof(Program).Assembly));

    options.MaxTop = 100;
});

PageSize supplies the server default, while options.MaxTop prevents a client requesting the entire catalogue through $top. Keep the maximum modest enough to protect memory and response time, then tune it using payload measurements. Include SkipToken only when the endpoint has a deliberate continuation-token design.

Package APIs have changed across OData releases, so match imports and setup to the installed version. The underlying contract remains consistent: parse supported options, validate values and sort properties, compose the database query, and return either OData results or a structured OData error.

Selecting a pagination model

Different paging styles solve different navigation and consistency problems:

Offset paging remains a practical default for a catalogue with thousands of records and modest concurrent writes. Clients gain direct access to numbered pages, while the server enforces a ceiling on $top. Deep navigation should still be discouraged because performance can deteriorate as the skipped row count rises.

Token paging works better for large, mutable feeds. The token should represent the last sort key and unique tie-breaker rather than exposing a database position. Treat it as opaque, bind it to the selected sort and filter, and return a specific error when it is invalid or expired. A numbered interface can combine offset paging for its first few pages with continuation links for deeper traversal.

Building the ASP.NET Core query pipeline

The controller should expose an IQueryable<ProductSummary> over an authorised, projected data set. This lets the OData query component add filtering, ordering and paging before materialisation:

[HttpGet]
[EnableQuery(PageSize = 25, MaxPageSize = 100)]
public IActionResult Get()
{
    IQueryable<ProductSummary> query = _context.Products
        .AsNoTracking()
        .Where(p => !p.IsDiscontinued)
        .Select(p => new ProductSummary(
            p.Id, p.Name, p.Category, p.Price));

    return Ok(query);
}

Apply tenant isolation before returning the queryable result. In a multi-tenant application, derive the tenant identifier from authenticated claims and include it in the base predicate. Never allow an OData filter to become the only authorisation boundary, and project the response to a DTO so internal fields are unavailable to client-supplied $select expressions.

Entity Framework Core normally translates supported operations into SQL, so avoid materialising the result before OData processes it. Enforce deterministic ordering, restrict sortable properties and test the generated execution plan. Query duration, cancellation, result size and rejected values should be logged without recording sensitive filter contents.

Designing responses for real clients

An OData response commonly contains the page in a value property and, when requested, a total in @odata.count. Exact $count=true values can produce a separate database count, so always measure their cost. Return next or previous links when using continuation tokens, and give clients a clear way to detect an empty page without confusing it with an expired token.

Australian implementation details also belong in the contract. The Privacy Act 1988 and Australian Privacy Principles may constrain customer, employee or health-related fields, while organisations using service provider IDs can face additional health-privacy duties. Return timestamps in UTC because Brisbane has no daylight-saving change and Sydney and Melbourne shift during different periods. Store money as a decimal with an explicit AUD currency and clear GST treatment.

Validation failures should use a consistent OData error payload with a stable code, readable message, target field and trace identifier. Map unsupported sorts, excessive page sizes and malformed tokens to suitable 400 Bad Request responses without exposing SQL, stack traces or internal service names. Authentication and authorisation failures should remain distinguishable from invalid query syntax.

For a printable export, teams can use this QuestPDF generator example as a separate server-side workflow; fetch a bounded page, render it, and return a document without coupling PDF generation to JSON pagination.

Practical recommendations for Australian teams

The following production defaults protect both database performance and the client contract:

• Set a measured default page size and a firm maximum, then reject $top values above that ceiling with a useful validation message.
• Require deterministic ordering and include a unique tie-breaker so equal sort values cannot move between pages.
• Restrict $filter, $orderby and $select to an allow-list of queryable DTO fields.
• Apply tenant and authorisation predicates before exposing the queryable result to OData.
• Use projections and AsNoTracking, then verify the generated SQL and indexes with realistic data volumes.
• Run counts only when requested, measure their cost, and cache them briefly when the underlying data tolerates staleness.
• Measure query duration, returned row count, rejected options and continuation-token failures for operational visibility.

Together, these controls make paging a bounded database workload and a predictable contract for clients. The endpoint then behaves consistently as the catalogue grows across Australian cloud regions and business environments.