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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
public abstract class PagedQueryBase { public int Page { get; set; } public int Results { get; set; } } public abstract class PagedResultBase { public int CurrentPage { get; } public int ResultsPerPage { get;} public int TotalPages { get; } public long TotalResults { get; } protected PagedResultBase() { } protected PagedResultBase(int currentPage, int resultsPerPage, int totalPages, long totalResults) { CurrentPage = currentPage > totalPages ? totalPages : currentPage; ResultsPerPage = resultsPerPage; TotalPages = totalPages; TotalResults = totalResults; } } public class PagedResult<T> : PagedResultBase { public IEnumerable<T> Items { get; } protected PagedResult() { Items = Enumerable.Empty<T>(); } protected PagedResult(IEnumerable<T> items, int currentPage, int resultsPerPage, int totalPages, long totalResults) : base(currentPage, resultsPerPage, totalPages, totalResults) { Items = items; } public static PagedResult<T> Create(IEnumerable<T> items, int currentPage, int resultsPerPage, int totalPages, long totalResults) => new PagedResult<T>(items, currentPage, resultsPerPage, totalPages, totalResults); public static PagedResult<T> Empty => new PagedResult<T>(); } |
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
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
public static class Pagination { public static PagedResult<T> Paginate<T>(this IOrderedQueryable<T> collection, PagedQueryBase query) => collection.Paginate(query.Page, query.Results); public static PagedResult<T> Paginate<T>(this IOrderedQueryable<T> collection, int page = 1, int resultsPerPage = 10) { return collection.AsQueryable().Paginate(page, resultsPerPage); } public static PagedResult<T> Paginate<T>(this IQueryable<T> collection, PagedQueryBase query) => collection.Paginate(query.Page, query.Results); public static PagedResult<T> Paginate<T>(this IQueryable<T> collection, int page = 1, int resultsPerPage = 10) { if (page <= 0) page = 1; if (resultsPerPage <= 0) resultsPerPage = 10; var isEmpty = collection == null || !collection.Any(); if (isEmpty) return PagedResult<T>.Empty; var totalResults = collection.Count(); var totalPages = (int)Math.Ceiling((double)totalResults / resultsPerPage); var data = collection.Skip((page - 1) * resultsPerPage).Take(resultsPerPage).ToList(); return PagedResult<T>.Create(data, page, resultsPerPage, totalPages, totalResults); } } |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
public static class Pagination { public static async Task<PagedResult<T>> PaginateAsync<T>(this IMongoQueryable<T> collection, PagedQueryBase query) => await collection.PaginateAsync(query.Page, query.Results); public static async Task<PagedResult<T>> PaginateAsync<T>(this IMongoQueryable<T> collection, int page = 1, int resultsPerPage = 10) { if (page <= 0) page = 1; if (resultsPerPage <= 0) resultsPerPage = 10; var isEmpty = await collection.AnyAsync() == false; if (isEmpty) return PagedResult<T>.Empty; var totalResults = await collection.CountAsync(); var totalPages = (int) Math.Ceiling((double) totalResults/resultsPerPage); var data = await collection.Limit(page, resultsPerPage).ToListAsync(); return PagedResult<T>.Create(data, page, resultsPerPage, totalPages, totalResults); } public static IMongoQueryable<T> Limit<T>(this IMongoQueryable<T> collection, PagedQueryBase query) => collection.Limit(query.Page, query.Results); public static IMongoQueryable<T> Limit<T>(this IMongoQueryable<T> collection, int page = 1, int resultsPerPage = 10) { if (page <= 0) page = 1; if (resultsPerPage <= 0) resultsPerPage = 10; var skip = (page - 1)*resultsPerPage; var data = collection.Skip(skip) .Take(resultsPerPage); return data; } } |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
public class BrowseWardenIterations : PagedQueryBase { public string WatcherName { get; set; } public string WatcherTypeName { get; set; } public DateTime? From { get; set; } public DateTime? To { get; set; } public ResultType ResultType { get; set; } } public enum ResultType { All = 0, Valid = 1, Invalid = 2 } public class MongoDbDataStorage : IDataStorage { private const string CollectionName = "Iterations"; private readonly IMongoDatabase _database; public MongoDbDataStorage(IMongoDatabase database) { _database = database; } public async Task<PagedResult<WardenIterationDto>> GetIterationsAsync(BrowseWardenIterations query) { var iterations = _database.GetCollection<WardenIterationDto>(CollectionName) .AsQueryable(); //Filter results ... return await iterations .OrderByDescending(x => x.CompletedAt) .PaginateAsync(query); } } |
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.