How to Build an Efficient Pagination in ASP.NET Core

• Download sample - 1.6 MB

Introduction

This article will explain how to use paging to retrieve only required amount of records, and display a paging control with reference to the total records.

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 then 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 are 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 must contain a lot of records so we can see the real benefit of the paging control. In order to keep the focus on the pagination topic, I will use a list of items (CultureInfo) that is 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 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, define 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)
            
            //call ToList() at the end to execute the query and return the result set
            .ToList();
    }
}

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

Creating Paging UI Control

Bootstrap offers a very nice Pagination UI Control, but it only renders the HTML elements and it still need a lot of work to know what and how to render inside the control, e.g., Total number of records, maximum displayed page numbers, searching filters, enable/disable previous-next buttons depending on the current page index,... etc.

I will use a paging tag helper from LazZiya.TagHelpers nuget package, it will do all the hard job for us. :)

PagingTagHelper 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 are 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.

Read more about PagingTagHelper here.

So, before adding the paging tag helper, we need to add one more variable for handling total records number in the back end:

//total number of records
public int TotalRecords { get; set; } = 0;

public void OnGet()
{
    TotalRecords = Cultures.Count();

    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)
        
        //call ToList() at the end to execute the query and return the result set
        .ToList();
}

Now we are ready to deal with the paging tag helper.

Install LazZiya.TagHelpers nuget package using package manager console (make sure to download the latest version):

Install-Package LazZiya.TagHelpers -Version 2.2.1

Or by using nuget package manager UI:

Add LazZiya.TagHelpers to _ViewImports.cshtml page:

@addTagHelper *, LazZiya.TagHelpers

Add the paging tag helper code to Index.cshtml view below the table:

<paging page-no="Model.P"

        page-size="Model.S"

        total-records="Model.TotalRecords">
</paging>

Later, we will add the query-string-value after adding some search filters, for now, run the app and test the paging control in its basic setup:

Adding Search Filters

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

First, let's 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)
        
        //call ToList() at the end to execute the query and return the result set
        .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, this will give us the possibility to share the URL of any numbered page.

Run the app and test the search:

The search is working well, but if we click on another page number, we will lose the search keyword! In order to keep all query string parameters included in the numbered pages URLs, 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 work well together.

Customize the Paging UI 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 list of CultureInfo items, but we are displaying only few fields in our table! So we can improve our memory/bandwidth usage by returning a list of objects that contain the displayed fields only.

Create a new class named CultureItem and modify the search logic to return list of CultureItems 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))
            
            //map the selected fields to our new object
            .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)
        
        //call ToList() at the end to execute the query and return the result set</span>
        .ToList();
}

Improving Search Logic

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

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

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 in null field, we can add a null check condition to the search logic.

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 pagination and search method using generic expressions in EF Core.

History

• 21^st September, 2019: Initial version