How to build an efficient pagination system

Build a reliable pagination system for any Asp.Net Core web project

Introduction

This article will explain how to use paging for large amounts of data with the help of an efficient pagination system.

Background

Most probably you came across the issue where you need to list a few rows of records from a data source that contains a few thousands or more of records but the noticed that the pagination is an important factor of improving the site performance. Starting from filtering the data to selecting the relevant records from the database to displaying the paging control, there is a few but important steps to consider in order to build a reliable paging system.

Creating the project

I'll use the default ASP.Net Core 2.2 project template that ships with VS2019, so just create the basic Asp.Net Core 2.2 project and continue reading here.

Before we dig into the pagination we need to create a data source for our records. Our data source needs to contain a lot of records so we can see the real benefit of paging control. In order to keep the focus on the pagination topic I will use a list of items (CultureInfo) that already included in the framework.

Open Pages/Index.cshtml.cs and add the data source as below:

public class IndexModel : PageModel
{      
    public CultureInfo[] CulturesList { get; set; }
    private CultureInfo[] Cultures { get; set; }

    public IndexModel()
    {
        //this will act as the main data source for our project
        Cultures = CultureInfo.GetCultures(CultureTypes.AllCultures);
    }

    public void OnGet()
    {
        CulturesList = Cultures;
    }
}

Open Pages/Index.cshtml and add the below code to display the cultures list:

<table class="table table-striped">
    <thead>
        <tr>
            <th>LCID</th>
            <th>English Name</th>
            <th>Native Name</th>
            <th>Culture types</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var c in Model.CulturesList)
        {
            <tr>
                <td>@c.LCID</td>
                <td>@c.EnglishName</td>
                <td>@c.NativeName</td>
                <td>@c.CultureTypes</td>
            </tr>
        }
    </tbody>
</table>

Run the app to see the first results;

Handling paging on the backend

As you noticed we are sending all records to the view, and it is not an efficient way of programming, so we will limit the number of selected records to send a smaller amount of data to the view. Basically we need a two variables for paging;

- Page number : a variable to indicate the requested page number

- Page size : a variable to indicate the total numbers of records that should be selected at once

later we will add more variables for filtering as well.

Back to Pages/Index.cshtml.cs file, deine the variables and modify OnGet, so our new IndexModel will look like below:

public class IndexModel : PageModel
{      
    public IList<CultureInfo> CulturesList { get; set; }
    private CultureInfo[] Cultures { get; set; }

    //page number variable
    [BindProperty(SupportsGet = true)]
    public int P { get; set; } = 1;

    //page size variable
    [BindProperty(SupportsGet = true)]
    public int S { get; set; } = 10;

    public IndexModel()
    {
        //this will act as the main data source for our project
        Cultures = CultureInfo.GetCultures(CultureTypes.AllCultures);
    }

    public void OnGet()
    {
        CulturesList = Cultures
            //make sure to order items before paging
            .OrderBy(x=>x.EnglishName)

            //skip items before current page
            .Skip((P-1)*S)

            //take only 10 (page size) items
            .Take(S)
            .ToList();
    }
}

Run the app, you will see first 10 records only,

Creating paging control

Now I will tell you one bad and one good news. The bad news is; building a reliable paging control requires a lot of work and needs considering some factores like total number of records, maximum displayed page numbers, searching filters, current page index, etc.

The good news, we will use a paging tag helper from LazZiya.TagHelpers nuget package, it will do all the job for us :)

PagingTagHelpr basically requires these parameters:

- page-no : required int variable for current page number

- total-records : required int for total records count in the data source

- query-string-value : string value, required if there is search filters included in the url

- page-size : optional int (10 by default)

- query-string-key-page-no : optional string indicating the query string key name for page number. The default value is "p", we will not use this because we defined the same key name in our backend.

- query-string-key-page-size : optional string indicating the query string key name for page size. The default value is "s", so we will not use this because we defined the same key name in our backend as well.

Adding search filters

The basic listing of records is done, now we will add some search filters to have more functional listing.

First lets add the basic text search logic to the backend:

//variable for text search
[BindProperty(SupportsGet = true)]
public string Q { get; set; } = string.Empty;

public void OnGet()
{
    var query = Cultures
        //search in EnglishName and NativeName
        .Where(x =>
            x.EnglishName.Contains(Q, StringComparison.OrdinalIgnoreCase) ||
            x.NativeName.Contains(Q, StringComparison.OrdinalIgnoreCase));

    //count records that returns after the search
    TotalRecords = query.Count();

    CulturesList = query

        //make sure to order items before paging
        .OrderBy(x => x.EnglishName)

        //skip items before current page
        .Skip((P - 1) * S)

        //take only 10 (page size) items
        .Take(S)
        .ToList();
}

We have defined a string variable named "Q" that will be assigned to a search textbox, additionally we modified our logic so the TotalRecords value returns the count of records after the search.

Now we can add the search form to the frontend:

<form method="get" class="form-inline">
    <input asp-for="Q" class="form-control" />
    <button type="submit" class="btn btn-primary">Search</button>
</form>

Make sure that the form method is "get" because we are targeting OnGet() method in the backend. Run the app and test the search:

The search is working well, but if we click on another page number we will loose the search keyword! As mentioned above, we need to add query-string-value to the tag helper as below :

<paging page-no="Model.P"
        page-size="Model.S"
        total-records="Model.TotalRecords"
        query-string-value="@(Request.QueryString.Value)">
</paging>

Now the search and paging can working well together.

Customize the paging control

Our paging tag helper can be customized by adding more controls like labels for total pages, total records and page size control, modify the paging tag helper code like below to get more details:

<paging page-no="Model.P"
        page-size="Model.S"
        total-records="Model.TotalRecords"
        query-string-value="@(Request.QueryString.Value)"
        show-prev-next="true"
        show-total-pages="true"
        show-total-records="true"
        show-page-size-nav="true"
        show-first-numbered-page="true"
        show-last-numbered-page="true">
</paging>

Now we have more functional paging control:

Improving performance

Till now we are returning a complete set of records, but we are displaying only few fields in our table! so we will create a new class named CultureItem that will contain the displayed fields only to reduce the amount of data being sent over the bandwidth, and we will modify our search logic to return list of CultureItem's instead of CultureInfo:

//object that contains only displayed fields
public class CultureItem
{
    public int LCID { get; set; }
    public string EnglishName { get; set; }
    public string NativeName { get; set; }
    public CultureTypes CultureTypes { get; set; }
}

//return list of CultureItem
public IList<CultureItem> CulturesList { get; set; }

public void OnGet()
{
    var query = Cultures
        //search in EnglishName and NativeName
        .Where(x =>
            x.EnglishName.Contains(Q, StringComparison.OrdinalIgnoreCase) ||
            x.NativeName.Contains(Q, StringComparison.OrdinalIgnoreCase))
            .Select(x => new CultureItem
            {
                LCID = x.LCID,
                EnglishName = x.EnglishName,
                NativeName = x.NativeName,
                CultureTypes = x.CultureTypes
            });

    //count records that returns after the search
    TotalRecords = query.Count();

    CulturesList = query

        //make sure to order items before paging
        .OrderBy(x => x.EnglishName)

        //skip items before current page
        .Skip((P - 1) * S)

        //take only 10 (page size) items
        .Take(S)
        .ToList();
}

Improving search logic

- We used they keyword as one text string in the search, we can imrove our query by splitting the search keywords and removing empty spaces and duplicates

- When using a database like MSSqlDb and doing a search in nullable fields we may get an exception if the searched field is null, in order to avoid searching null field we can add a null check condition to the search logic.

var _keyWords = Q.Split(new[] { ' ', ',', ':' }, StringSplitOptions.RemoveEmptyEntries).Distinct();

var query = Cultures
    //search in EnglishName and NativeName
    .Where(x => _keyWords.Any(kw =>
            (x.EnglishName!=null && x.EnglishName.Contains(kw, StringComparison.OrdinalIgnoreCase)) ||
            (x.NativeName != null && x.NativeName.Contains(kw, StringComparison.OrdinalIgnoreCase))))

- Even more performance improvement can be done by using AsNoTracking() when searching database, so the framework will not keep tracking the selected entities and this will help to free some memory.

Generic search expression

In my next article I will explain how to build a generic search method using Expressions in EF Core.