Pagination for MSSQL & MongoDB

Pagination is (should be) a well-known concept for most of the developers. Whether we want to execute an optimized query on a database that will fetch just a small subset of all available records or increase the web service throughput by returning less data than it’s needed – it all boils down to the efficient usage of the pagination.
I’m going to present my approach to this mechanism, both for the MSSQL and MongoDB databases, yet it can be also used in any other scenario such as in memory pagination.


 

At first, let’s start with some core classes that will be common for all of the operations:

It may look like a lot of code for such simple things but it should be quite easy to understand. The PagedQueryBase is a base class from which derive the specialized query classes (e.g. BrowseUsers) that may have their own specific properties. The PagedResultBase is specified as a separate abstract class, mostly due to the fact that it’s sometimes useful to be able to have access to its properties without taking into consideration the Items. Finally, the PagedResult holds the actual data that is paginated and exposes a set of factory methods.

You may ask why the PagedResult is immutable while PagedQueryBase is not?
Well, because e.g. in an HTTP API I would parse the incoming request directly into the derived type of PagedQueryBase and the setters come in handy in this occasion.

Now, let’s move into the actual implementation of the pagination. I use the extension methods, however, feel free to refactor it into anything else (e.g. some interface or helper class) if you’re not the biggest fan of such approach.

We’ll begin with the MSSQL approach:

You may use this with any library that supports IQueryable. Also you can make it asynchronous e.g. with Entity Framework and ToListAsync() invocation.

Let’s finish with the MongoDB extensions:

This approach is async by default because I use the MongoDB Driver. I’m using the pagination in my open source project Warden for returning a subset of data previously fetched from the database. Here’s the real world example:

Whether you should return such object (envelope) or not from your HTTP API that’s a whole different story about which I’ll write more in the future.

Leave A Comment

Your email address will not be published. Required fields are marked *