Click here to Skip to main content
14,734,433 members
Articles » Web Development » ASP » Code Project scripts
Technical Blog
Posted 12 Jan 2021

Stats

4.9K views
7 bookmarked

Paging and Sorting using ASP .NET Core Razor Page, Web API, and Entity Framework

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
15 Jan 2021CPOL
How to implement paging and sorting to yield good performance
How to implement paging and sorting using the .NET Core Razor Page, the Web API, and the Entity Framework to yield good performance.

This project features:

  • Selection of Page Size
  • Navigation of Pages
  • Display of Record Numbers
  • Sorting of Columns

You can see the source and the latest updates to this project here.

Core Classes

The first thing is to define what the user can ask the application to fetch:

  • Page size
  • Page number
  • Field to sort
  • Sort Direction

The code is shown below:

public class PageSortParam
    {
        public int PageSize { get; set; } = 10;  //default page size
        public int CurrentPage { get; set; } = 1;

        public string SortField { get; set; } = null;
        public SortDirection SortDir { get; set; } 
    }

    public enum SortDirection
    {
        Ascending = 0,   //default as ascending
        Decending
    }

Next, we define what the application should return, which are below:

  • Total number of records
  • Total number of pages
  • Previous page number — for when the user navigates to the previous page
  • Next page number — for navigating to the next page
  • First record number on the current page
  • Last record number on the current page

The code is shown below:

public class PageSortResult
    {
        public int TotalCount { get; set; } = 0;
        public int TotalPages { get; set; } = 1;
        public int? PreviousPage { get; set; }
        public int? NextPage { get; set; }
        public int FirstRowOnPage { get; set; }
        public int LastRowOnPage { get; set; }
    }

With the user parameter and the result defined, we create the PageList<T> class that inherits from List<T> so that we can store the result in the List. The class will take the parameter and find the result.

The code is shown below with the logic needed in the GetData() method. The line that gets the records from the database is the call to ToListAsync(), it will skip the records not needed by calling Skip() and get only the records needed by calling Take():

public class PageList<T> : List<T>
    {
        public PageSortParam Param { get; }
        public PageSortResult Result { get; }

        public PageList(PageSortParam param)
        {
            Param = param;
            Result = new PageSortResult();
        }

        public async Task GetData(IQueryable<T> query)
        {
            //get the total count
            Result.TotalCount = await query.CountAsync();
            //find the number of pages
            Result.TotalPages = (int)Math.Ceiling(Result.TotalCount / (double)Param.PageSize);
            //find previous and next page number
            if (Param.CurrentPage - 1 > 0)
                Result.PreviousPage = Param.CurrentPage - 1;
            if (Param.CurrentPage + 1 <= Result.TotalPages)
                Result.NextPage = Param.CurrentPage + 1;
            //find first row and last row on the page
            if (Result.TotalCount == 0)  //if no record found
                Result.FirstRowOnPage = Result.LastRowOnPage = 0;
            else
            {
                Result.FirstRowOnPage = (Param.CurrentPage - 1) * Param.PageSize + 1;
                Result.LastRowOnPage = 
                  Math.Min(Param.CurrentPage * Param.PageSize, Result.TotalCount);
            }

            //if has sorting criteria
            if (Param.SortField != null)
                query = query.OrderBy(Param.SortField + 
                 (Param.SortDir == SortDirection.Ascending ? " ascending" : " descending"));

            List<T> list = await query.Skip((Param.CurrentPage - 1) * 
                           Param.PageSize).Take(Param.PageSize).ToListAsync();
            AddRange(list);  //add the list of items
        }
    }

The Data Layer

The definition of the Customer is defined in the Data Layer Project:

public class Customer
    {
        [Required]
        public int CustomerId { get; set; }

        [Required, StringLength(80)]
        public string FirstName { get; set; }
        [Required, StringLength(80)]
        public string LastName { get; set; }
    }

We will define the interface and the implementation of the usual CRUD, the only difference being that the Get() method will use the PageList<T> class to get only the records needed, thereby increasing the performance by pushing the work to the database. Below is the interface:

public interface ICustomerData
    {
        Task<PageList<Customer>> Get(PageSortParam pageSort);
        Task<Customer> GetCustomerById(int customerId);
        Task<Customer> Update(int customerId, Customer customer);
        Task<Customer> Add(Customer customer);
        Task<int> Delete(int customerId);
    }

And the implementation:

public class SqlCustomerData : ICustomerData
    {
        public StarterDbContext DbContext { get; }

        public SqlCustomerData(StarterDbContext dbContext)
        {
            DbContext = dbContext;
        }

        public async Task<Customer> Add(Customer customer)
        {
            DbContext.Add(customer);
            await DbContext.SaveChangesAsync();
            return customer;
        }

        public async Task<int> Delete(int customerId)
        {
            Customer c = await this.GetCustomerById(customerId);
            if (c != null)
            {
                this.DbContext.Remove(c);
                await DbContext.SaveChangesAsync();
                return customerId;
            }
            return -1;
        }

        public async Task<PageList<Customer>> Get(PageSortParam pageSortParam)
        {
            PageList<Customer> list = new PageList<Customer>(pageSortParam);
            
            await list.GetData(DbContext.Customer);
            return list;
        }

        public async Task<Customer> GetCustomerById(int customerId)
        {
            Customer c = await this.DbContext.Customer.FindAsync(customerId);
            if (c != null)
                return c;
            return null;
        }

        public async Task<Customer> Update(int customerId, Customer customer)
        {
            Customer c = await GetCustomerById(customerId);
            if (c != null)
            {
                c.FirstName = customer.FirstName;
                c.LastName = customer.LastName;
                await DbContext.SaveChangesAsync();
                return c;
            }
            return null;
        }
    }

The DbContext from the Entity Framework is simply:

public class StarterDbContext : DbContext
    {
        public DbSet<Customer> Customer { get; set; }

        public StarterDbContext(DbContextOptions<StarterDbContext> options)
            : base(options)
        {
        }
    }

The API

In the Web API project, we define the GetCustomers() method that will accept the PageSortParam as the parameter, call the Get() method in the Data Layer, add the meta data from the PageSortResult in the response header (such as total number of records, total pages, etc.), and provide the actual records in the response body:

[Route("api/[controller]")]
    [ApiController]
    public class CustomerController : ControllerBase
    {
        public ICustomerData CustomerData { get; }

        public CustomerController(ICustomerData customerData)
        {
            CustomerData = customerData;
        }

        // GET: api/Customer
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Customer>>> 
               GetCustomers([FromQuery] PageSortParam pageSortParam)
        {
            PageList<Customer> list = await this.CustomerData.Get(pageSortParam);
            //return result metadata in the header
            Response.Headers.Add("X-PageSortResult", JsonSerializer.Serialize(list.Result));
            return Ok(list);
        }

        // GET: api/Customer/5
        [HttpGet("{customerId}")]
        public async Task<ActionResult<Customer>> GetCustomer(int customerId)
        {
            return Ok(await this.CustomerData.GetCustomerById(customerId));
        }

        // PUT: api/Customer/5
        [HttpPut("{customerId}")]
        public async Task<ActionResult<Customer>> 
               PutCustomer(int customerId, Customer customer)
        {
            return Ok(await this.CustomerData.Update(customerId, customer));
        }

        // POST: api/Customer
        [HttpPost]
        public async Task<ActionResult<Customer>> PostCustomer(Customer customer)
        {
            return Ok(await this.CustomerData.Add(customer));
        }

        // DELETE: api/Customer/5
        [HttpDelete("{customerId}")]
        public async Task<ActionResult<int>> DeleteCustomer(int customerId)
        {
            return Ok(await this.CustomerData.Delete(customerId));
        }
    }

The Razor Page

We define the page size that the user can select:

public IEnumerable<SelectListItem> PageSizeList { get; set; } = 
                                   new SelectList(new List<int> { 5, 10, 25, 50 });

And we use [BindProperty(SupportsGet=true)] to pass the value to the HTML page and also get the value back. For each trip, we need to pass and get properties below from the HTML page:

  • PageSize – user requested page size
  • PageNumber – the current page number the user is on
  • SortField – the column that the user requested to sort on
  • SortDir – the direction it should sort
  • SortDirNext – the next sort direction when the user clicks on the column link
[BindProperty(SupportsGet = true)]
        public int? PageSize { get; set; }

        [BindProperty(SupportsGet = true)]
        public int PageNumber { get; set; } = 1;

        [BindProperty(SupportsGet = true)]
        public string SortField { get; set; }  

        [BindProperty(SupportsGet = true)]
        public SortDirection SortDir { get; set; }

        //for the next sort direction when the user clicks on the header
        [BindProperty(SupportsGet = true)]
        public SortDirection? SortDirNext { get; set; }

The OnGet() method will take in the value of each property, build the parameter and pass it to the API, follow by displaying the records and showing the meta data about the page. Below is the complete code:

public class ListModel : PageModel
    {
        public IEnumerable<Dto.Customer> CustomerList { get; set; }
        private readonly IConfiguration config;

        public IEnumerable<SelectListItem> 
           PageSizeList { get; set; } = new SelectList(new List<int> { 5, 10, 25, 50 });

        public PageSortParam PageSortParam { get; set; } = new PageSortParam();
        public PageSortResult PageSortResult { get; set; }

        [BindProperty(SupportsGet = true)]
        public int? PageSize { get; set; }

        [BindProperty(SupportsGet = true)]
        public int PageNumber { get; set; } = 1;

        [BindProperty(SupportsGet = true)]
        public string SortField { get; set; }  

        [BindProperty(SupportsGet = true)]
        public SortDirection SortDir { get; set; }

        //for the next sort direction when the user clicks on the header
        [BindProperty(SupportsGet = true)]
        public SortDirection? SortDirNext { get; set; }

        public ListModel(IConfiguration config)
        {
            this.config = config;
        }

        public async Task OnGet()
        {
            if (PageSize.HasValue)
                PageSortParam.PageSize = (int)PageSize;

            PageSortParam.CurrentPage = PageNumber;

            //if never sorted
            if (SortField == null)
                SortDir = new SortDirection();
            else if (SortDirNext != null)  //if requested new sort direction
                SortDir = (SortDirection)SortDirNext;

            //SortDirNext will be the reverse of SortDir
            SortDirNext = SortDir == SortDirection.Ascending ? 
                          SortDirection.Decending : SortDirection.Ascending;
            
            PageSortParam.SortField = SortField;
            PageSortParam.SortDir = SortDir;
            
            HttpResponseMessage response = await new HttpClient().GetAsync
              (this.config["APIurl"] + "Customer?PageSize=" + PageSortParam.PageSize
                                     + "&CurrentPage=" + PageSortParam.CurrentPage
                                     + "&SortField=" + PageSortParam.SortField
                                     + "&SortDir=" + PageSortParam.SortDir);
            //display the list of customers
            if (response.IsSuccessStatusCode)
                CustomerList = await response.Content.ReadAsAsync<IEnumerable<Dto.Customer>>();
            //get the paging meta data from the header
            IEnumerable<string> headerValue;
            if (response.Headers.TryGetValues("X-PageSortResult", out headerValue))
            {
                PageSortResult = JsonConvert.DeserializeObject<PageSortResult>
                                 (headerValue.First());
            }
        }
    }

The html page will take the input from the user, either by submitting the form using http get, or clicking on the link. Notice that the parameters are passed in each action. Only the sort column name and directions are specified in the column header link:

<div>
    <div>
        <table class="table table-bordered table-hover table-sm w-auto">
            <caption>Items @Model.PageSortResult.FirstRowOnPage 
                     to @Model.PageSortResult.LastRowOnPage</caption>
            <thead class="thead-light">
                <tr>
                    <th scope="col">
                        <a asp-page="./Edit" asp-route-customerId="0">
                            <i class="material-icons icon">add_box</i>
                        </a>
                    </th>
                    <th scope="colgroup" colspan="4" class="text-right">
                        <form method="get">
                            Page Size:
                            @Html.DropDownListFor(m => m.PageSize, Model.PageSizeList, 
                                  "-Select-", new { onchange = "submit()" })
                            <input type="hidden" name="PageNumber" value="1" /> 
                            <input type="hidden" name="SortField" value="@Model.SortField" />
                            <input type="hidden" name="SortDir" value="@Model.SortDir" />
                        </form>
                    </th>
                </tr>
                <tr>
                    <th scope="col" class="pl-2 pr-2">
                        <a asp-page="./List"
                           asp-route-SortField="CustomerId"
                           asp-route-SortDir="@Model.SortDir"
                           asp-route-SortDirNext="@Model.SortDirNext"
                           asp-route-PageSize="@Model.PageSize"
                           asp-route-PageNumber="@Model.PageNumber">
                            Customer ID
                        </a>
                    </th>
                    <th scope="col" class="pl-2 pr-2">
                        <a asp-page="./List"
                           asp-route-SortField="FirstName"
                           asp-route-SortDir="@Model.SortDir"
                           asp-route-SortDirNext="@Model.SortDirNext"
                           asp-route-PageSize="@Model.PageSize"
                           asp-route-PageNumber="@Model.PageNumber">
                            First Name
                        </a>
                    </th>
                    <th scope="col" class="pl-2 pr-2">
                        <a asp-page="./List"
                           asp-route-SortField="LastName"
                           asp-route-SortDir="@Model.SortDir"
                           asp-route-SortDirNext="@Model.SortDirNext"
                           asp-route-PageSize="@Model.PageSize"
                           asp-route-PageNumber="@Model.PageNumber">
                            Last Name
                        </a>
                    </th>
                    <th scope="col"></th>
                    <th scope="col"></th>
                </tr>
            </thead>
            <tbody>
                @foreach (var c in Model.CustomerList)
                {
                    <tr>
                        <td class="pl-2 pr-2">@c.CustomerId</td>
                        <td class="pl-2 pr-2">@c.FirstName</td>
                        <td class="pl-2 pr-2">@c.LastName</td>
                        <td class="td-center pl-2 pr-2">
                            <a asp-page="./Edit" asp-route-customerId="@c.CustomerId">
                                <i class="material-icons icon">edit</i>
                            </a>
                        </td>
                        <td class="td-center pl-2 pr-2">
                            <a asp-page="./Delete" asp-route-customerId="@c.CustomerId">
                                <i class="material-icons icon">delete</i>
                            </a>
                        </td>
                    </tr>
                }
            </tbody>
        </table>
    </div>
</div>
<div>
    @{
        var prev = Model.PageSortResult.PreviousPage.HasValue ? "" : "disabled";
        var next = Model.PageSortResult.NextPage.HasValue ? "" : "disabled";
        var first = Model.PageNumber != 1 ? "" : "disabled";
        var last = Model.PageNumber != Model.PageSortResult.TotalPages ? "" : "disabled";
    }
</div>
<a asp-page="./List"
   asp-route-pageNumber="1"
   asp-route-PageSize="@Model.PageSize"
   asp-route-SortField="@Model.SortField"
   asp-route-SortDir="@Model.SortDir"
   class="btn @first">
    <i class="material-icons icon">first_page</i>
</a>
<a asp-page="./List"
   asp-route-pageNumber="@Model.PageSortResult.PreviousPage" 
   asp-route-PageSize="@Model.PageSize"
   asp-route-SortField="@Model.SortField"
   asp-route-SortDir="@Model.SortDir"
   class="btn @prev">
    <i class="material-icons icon">chevron_left</i>
</a>
Page @Model.PageNumber of @Model.PageSortResult.TotalPages
<a asp-page="./List"
   asp-route-pageNumber="@Model.PageSortResult.NextPage"
   asp-route-PageSize="@Model.PageSize"
   asp-route-SortField="@Model.SortField"
   asp-route-SortDir="@Model.SortDir"
   class="btn @next">
    <i class="material-icons icon">chevron_right</i>
</a>
<a asp-page="./List"
   asp-route-pageNumber="@Model.PageSortResult.TotalPages"
   asp-route-PageSize="@Model.PageSize"
   asp-route-SortField="@Model.SortField"
   asp-route-SortDir="@Model.SortDir"
   class="btn @last">
    <i class="material-icons icon">last_page</i>
</a>

And that’s all! Hope you find this useful in building your paging and sorting applications.

History

  • 12th January, 2021: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

DotNetLead.com
United States United States
No Biography provided

Comments and Discussions

 
Suggestionsuggestion: Skip & Take instead of PageSize & PageNumber Pin
Red Feet13-Jan-21 6:53
MemberRed Feet13-Jan-21 6:53 
GeneralRe: suggestion: Skip & Take instead of PageSize & PageNumber Pin
DotNetLead.com14-Jan-21 16:35
MemberDotNetLead.com14-Jan-21 16:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.