How does Pagination work?

Pagination is a technique to load only smaller chunks of data you make visible to the user. Look at Google web search. You only have a certain amount of shown results even though there are millions. So Google has pages (like in a book) where you can go forth and back.

This has multiple advantages: First from a UX point of view you only show a reasonable amount of data to your user. The second benefit is for your API / DB as it only has to load a small page instead of the whole book.

To indicate how many pages you have to offer you have to request the whole amount of pages. That comes with a cost but you could indicate to the user whether or not there are other pages or how many there are in the first place.

Another approach is to load one entry from and after the page the user requested. Then the data is very limited (so faster than the Count approach) and the user still sees if there will be other pages, but he losses the ability to see how many pages there are overall.

Basically there are two easy formulas involved. First we want to get a specific page X (remember pages start with 1 and not with 0). I will write it in simple C# code, but that holds true for SQL as well:

IEnumerable<MyObject> GetPage(int page)
{
    // Just for the sake of demonstration I put it in here,
    // normally you would have it somewhere else
    const int pageSize = 5;
    // As pages start with 1 but indexes start with 0, we have to subtract one
    var skippedEntries = (page - 1) * pageSize;
    return myCollection.Skip(skippedEntries).Take(pageSize);
}

With SQL you would do the same with OFFSET and LIMIT. So there is not so much magic involved here. As said initially what you could do is to also get the total amount of columns (like when Google says to you that it has 1 million results and so and so much pages. On many database server the cost of COUNT(*) is okay-ish^{citation needed}. Well as long as you don't join other tables or have WHERE clause. So if this is expensive, maybe caching would be one thing to tackle that.

Infinite Scrolling

There is another approach which goes in the same direction the so called: "Infinite Scrolling". Instead that the user clicks on a specific "page", the application automatically loads those entries from the database and shows them directly to the user. A famous example would be Twitter or LinkedIn. There are some advantages like less interaction cost for the user as this stuff gets loaded automatically (with less interruptions), but there are also issues in terms of SEO and accessibility. Well this shouldn't be about infinite scrolling so what is my point here? Both pagination and infinite scrolling work more or less the same from an API / Database level, it is just another way to display the information to the user.

.NET helpers

There are libraries like X.PagedList which do the magic for you. It sits directly on top of IEnumerable and IQueryable so you only have to specify the pagesize and which page you want to have. This very blog uses (as I write this article) this library to do the pagination.

var entityPage = await someDbContext.Entity.ToPagedListAsync(page, pageSize);
var isFirstPage = entityPage.IsFirstPage;
var isLastPage = entityPage.IsLastPage;
var totalCountOfPages = entityPage.Count;

Conclusion

I hope you have a better understand what and whatfor we have pagination and how to use it.