Pagination is essential when an ASP.NET Core Web API exposes large datasets. Returning an entire database table in a single response can result in excessive database processing, application memory consumption, network traffic, serialization overhead, and poor client performance.
A common implementation uses parameters such as:
page=1&pageSize=50
However, simply adding these parameters is not enough. The API should ensure that filtering, sorting, projection, and pagination are performed by the database query itself rather than retrieving the entire dataset into memory and paginating afterward.
For example, with Entity Framework Core, the query should generally be composed so that the database performs the filtering and ordering before applying Skip and Take for offset-based pagination. Projection should also be used to retrieve only the fields required by the API response.
Offset pagination is relatively straightforward and works well for many business applications. However, for very large datasets, high offsets can become increasingly expensive, and changes to the dataset between requests can cause records to be skipped or duplicated.
This is where keyset or cursor-based pagination becomes important. Instead of saying "give me records 10001–10050," the client can request records after a specific stable ordering key. This approach can provide more predictable performance for large datasets when appropriate indexed columns are available.
A production-grade pagination design should consider:
-
Maximum allowed page size
-
Default page size
-
Stable and deterministic sorting
-
Database indexes
-
Filtering before pagination
-
Cursor or continuation-token design
-
Authorization and tenant boundaries
-
Dataset changes between requests
-
Response metadata
-
API performance monitoring
-
Protection against excessive or abusive requests
Developers should also avoid exposing database implementation details unnecessarily through the API contract. A well-designed response can provide the requested data along with appropriate pagination information while keeping the underlying storage implementation independent.
For high-volume enterprise APIs, the choice between offset pagination and cursor/keyset pagination should be driven by dataset size, access patterns, consistency requirements, indexing strategy, and client experience—not simply by which implementation is easier to write.
The ultimate objective is to create an API that remains predictable, performant, and scalable as both the dataset and number of consumers grow.