Why do we default to offset pagination on EF Core?

Pagination is one of the most important things that you’re API should have. It is crucial for performance. I think for every .NET projects this is present, so having a good understanding on how to create a good pagination is a must.

Offset pagination - requires two values as input: the page number and the page size, and it will use this information to query the data.

It uses the Skip method (FETCH NEXT/OFFSET in SQL) to return the data. This approach supports random access pagination, which means the user can jump to any page he wants.

Behind the scenes, the OFFSET specifies the first record position, and the LIMIT / FETCH NEXT specifies the number of records you want to fetch.

eg. if your database contains 500 records, and you request the records for page number 4 and page size 100, the records from position 301 up to 400 will be returned.

Keyset pagination - (also known as seek-based pagination or cursor pagination) is an alternative to the offset pagination, and it uses a WHERE clause to skip rows instead of using an offset(SKIP).

the keyset pagination requires two properties as input: a reference value (which can be some sequential identifier for the last returned value) and the page size.

For example, assuming the reference is the last returned Id and your database contains 500 records, when you make a request with the reference value equals 300 and the page size equal 100, it will filter the records that only have the Id bigger than 300 and will take the next 100 records:

This kind of pagination is more performant than the offset pagination, because when a query is executed, the database does not need to process all the previous rows before reaching the row number that needs to be retrieved.

When we deal with EF Core we are going to use offset pagination. But, according to other developers this has a downside, because in order for records to show from rows 301 up to 400.

If you would implement an offset pagination in EF core it would be something like this.

very straightforward, this will skip and then return 10 rows.

The downside of the offset pagination, is that if the database contains 10,000 records and you need to return records from rows 2000 to 3000. The database must still process the first 1 - 1999 even if they are not SELECTED on your query. It will perform a scan for these rows. The higher the number of rows you need to skip then a performance on your query will be noticeable.

So in order to return a 3000 records, the execution plan would read 5000 including the 2000 records that are being skipped. That sounds like a real problem right?

Now enter the keyset pagination, now before we talk about the the theoretical complications. The problem seems so simple right, we just need to find a way to bookmark the rows to avoid the scans of an offset pagination. Although there are like other complications by this offset pagination like inconsistency of data where you can read here. But that’s for you to dig. I will be just walking you on how to solve the said problem.

In order to enhance this using a keyset pagination first we need an ordered set. In our case it’s an integer ID. In order to not scan the skipped 2000 records we will add a lastId parameter where will serve as the bookmark. Downside is that keyset pagination doesn’t support a random access.

Here is the keyset pagination where we add the LastID in order to skip the 2000 records that are needed. And looking at our execution plan it just reads a total of 3000 records instead of 5000 records.

Now that we did surely speed up our queries just by looking on the execution plan, I’m sure it’s much faster because we are doing an Index Seek instead of an Index Scan.

You can experiment and implement it by doing like this, where you declare additional parameters to support backward compatibility then building your expression trees using a condition. I hope you do get the idea.

When using keyset pagination it’s best to OrderBy one property or use a tiebreaker.

Adding multiple column such as CreatedAt will make your pagination more robust.

Now when we created a composite Index CreatedAt_Id(CreatedAt , Id) , we expect a much more performance right. This is all according to EF Core documentation https://learn.microsoft.com/en-us/ef/core/querying/pagination.

Now that we follow the EF core implementation of keyset pagination. I think that utilizing this tiebreaker. I’m using Microsoft MSSQL here and by analyzing the execution plan following the EF Core documentation I get an Index scan and a 10,000 records scan. It’s like doing a full table scan. Now adding the tiebreakers would just lose you performance at this point, but this is an interesting video where Milan show this same problem and was able to solve it at 10:49 because of POSTGRESQL supporting tuple comparisons.

We can always use the offset pagination with cursor pagination to get the best of both worlds for sure.That’s all and be sure to check out the sources for your further reading about this topic.

Further reading:

https://www.reddit.com/r/dotnet/comments/1jcinvz/best_way_to_implement_pagination_an_option_on_web/

https://use-the-index-luke.com/no-offset

https://henriquesd.medium.com/pagination-in-a-net-web-api-with-ef-core-2e6cb032afb7

https://www.youtube.com/watch?v=X8zRvXbirMU&t=220s

https://use-the-index-luke.com/sql/partial-results/window-functions

https://roxeem.com/2025/10/11/strategic-pagination-patterns-for-net-apis/

https://learn.microsoft.com/en-us/ef/core/querying/pagination

https://www.slideshare.net/slideshow/p2d2-pagination-done-the-postgresql-way/22210863#10