Pagination in ASP.NET Core With EF Core: When Skip and Take Stop Scaling

Pagination is easy to add when an application has a small amount of data. Returning every record from an API may even work during early development. As the dataset grows, though, the database has more rows to process, responses become larger, and clients have more data to handle.

In ASP.NET Core with EF Core, Skip() and Take() are a common way to implement pagination. They work well for many applications, especially when users move through normal page ranges. The issue usually appears with deeper pages, where the database has to work through a larger offset before returning the requested records.

This article looks at how Skip() and Take() work, where large offsets can become a concern, why count queries add extra work, and when keyset pagination may make more sense.

RETURNING TOO MANY RECORDS FROM AN API
Consider an Orders API that returns every order from the database:

var orders = await db.Orders
      .ToListAsync();

This may be fine with a small dataset. As the number of orders increases, the database has to retrieve more records, the API response gets larger, and the client has more data to process.

A simple way to limit the result is:

var orders = await db.Orders
    .Take(20)
    .ToListAsync();

Pagination builds on this idea by letting the client request only part of a larger dataset. This is common for orders, customers, products, search results, and admin dashboards where returning every record is unnecessary.

HOW SKIP AND TAKE PAGINATION WORKS
Offset pagination uses Skip() to move past a number of records and Take() to return the requested page size.

A typical EF Core query looks like this:

var orders = await db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

With a page size of 20:

Page 1 → Skip(0)
Page 2 → Skip(20)
Page 3 → Skip(40)

The approach is simple and works well for many page-based interfaces.

The OrderBy() matters too. Without a predictable order, records can appear on different pages between requests, especially when the underlying data changes.

WHEN SKIP AND TAKE STOP SCALING
The performance impact of offset pagination can become more noticeable on deeper pages.

For example:

var orders = await db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Skip(50000)
    .Take(20)
    .ToListAsync();

The API only needs 20 records, but the database may still need to process rows before reaching that offset. As the offset gets larger, that work can increase.

That does not mean Skip() and Take() are always slow. The actual result depends on the database engine, indexes, query structure, and execution plan.

The main concern is the combination of large offsets, deep pagination, and a dataset that continues to grow.

THE HIDDEN COST OF COUNT
Many pagination APIs return the total number of records so the frontend can calculate the total number of pages.

That usually requires a count query:

var totalCount = await query.CountAsync();
var orders = await query
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

The request now performs a count and a query for the current page.

For large or filtered datasets, the count can add more work to the database. An exact count makes sense when the UI needs to show total pages or total records. But some APIs do not need that information.

If the client only needs to know whether another page is available, running a full count may not be necessary.

KEYSET PAGINATION FOR LARGE DATASETS
Keyset pagination, also known as cursor pagination, uses the last record seen instead of telling the database how many rows to skip.

For example:

var orders = await db.Orders
    .Where(o => o.CreatedAt < cursor)
    .OrderByDescending(o => o.CreatedAt)
    .Take(pageSize)
    .ToListAsync();

The next request uses the cursor from the previous result and continues from there instead of using a larger offset.

This approach can be useful for large datasets, infinite scrolling, and APIs where users normally move forward through results rather than jumping to a specific page.

Keyset pagination also needs careful cursor and ordering logic, so it is not a replacement for offset pagination in every situation.

KEEPING PAGINATION STABLE WITH ORDERING
Pagination needs stable ordering. A timestamp such as CreatedAt may not be unique, so multiple records can have the same value.

A secondary field such as Id can make the ordering deterministic:

var orders = await db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .ThenByDescending(o => o.Id)
    .Take(pageSize)
    .ToListAsync();

The same idea applies to a keyset query:

query = query.Where(o =>
    o.CreatedAt < cursorDate ||
   (o.CreatedAt == cursorDate && o.Id < cursorId));

Using both fields gives the query a consistent way to decide which records belong on the next page.

PAGINATION WITH FILTERING AND SORTING
Most real APIs do more than return a list. Users may filter orders by status, customer, or date range before moving through the results.

A common pattern is:

Filter → Order → Paginate

var query = db.Orders.AsQueryable();
if (status.HasValue)
{
query = query.Where(o => o.Status == status.Value);
}
var orders = await query
    .OrderByDescending(o => o.CreatedAt)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

Filtering first means pagination is applied to the relevant result set. The same filtering and sorting patterns should also be considered when deciding which indexes the query needs.

MAKING PAGINATION SAFER FOR PUBLIC APIs
Pagination values usually come from the API consumer, so they should be validated.

if (page < 1) 
    page = 1;
if (pageSize < 1)
    pageSize = 20;
if (pageSize > 100)
    pageSize = 100;

A maximum page size prevents a client from requesting an unnecessarily large number of records in one request.

Pagination is not only about database queries. It also helps control API response size and resource usage.

INDEXES AND PAGINATION PERFORMANCE
Indexes are especially relevant when pagination queries also filter or sort data.

For example:

builder.HasIndex(o => o.CreatedAt)
   .IsDescending();

For a query that commonly filters by status and sorts by creation date:

builder.HasIndex(o => new
{
    o.Status,
    o.CreatedAt
});

Indexes should match the queries the application actually runs. Adding indexes without looking at the workload can add unnecessary database overhead.

When investigating a slow pagination query, execution plans and actual workload measurements can help show whether an index is being used effectively.

TESTING PAGINATION WITH REALISTIC DATA
Pagination problems do not always show up with a small test dataset.

Test different cases:

• Page 1
• Page 10
• Page 100
• Deep pagination
• Large pageSize
• Filtered results
• Sorted results

Measure query execution time and inspect execution plans when needed. Testing with realistic data volumes gives a better idea of how pagination will behave as the dataset gets larger.

OUR TAKE
At Qodors, we’ve seen pagination work well with smaller datasets and become a concern as the data grows. Skip() and Take() are useful, but large offsets and unnecessary count queries can add extra database work.

Good pagination does not need to be complicated. Stable ordering, suitable indexes, reasonable page sizes, and realistic testing go a long way.

For large datasets or deep pagination, keyset pagination is worth considering when it fits the way the API is used.

QUICK REFERENCE

• Limit API collection responses.
• Use Skip() and Take() where offset pagination fits.
• Watch for deep offsets.
• Avoid unnecessary count queries.
• Use stable ordering.
• Apply filters before pagination.
• Set reasonable page-size limits.
• Use indexes based on actual query patterns.
• Consider keyset pagination for large datasets.
• Test with realistic data.

Pagination that works today does not automatically mean it will perform the same way as the application grows.

Measure how pagination behaves with more data and deeper requests. Find where the extra database work comes from, then focus on the parts that actually need improvement.

DotNetCore #EFCore #BackendDevelopment #WebDevelopment #API #Pagination #DatabasePerformance #Scalability #QodorsEdge

Written by the team at Qodors — practical insights on ASP.NET Core, EF Core, and building scalable backend systems. → https://www.qodors.com/?utm_source=devto&utm_medium=post&utm_campaign=efcore_pagination