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;
public int CurrentPage { get; set; } = 1;
public string SortField { get; set; } = null;
public SortDirection SortDir { get; set; }
}
public enum SortDirection
{
Ascending = 0,
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)
{
Result.TotalCount = await query.CountAsync();
Result.TotalPages = (int)Math.Ceiling(Result.TotalCount / (double)Param.PageSize);
if (Param.CurrentPage - 1 > 0)
Result.PreviousPage = Param.CurrentPage - 1;
if (Param.CurrentPage + 1 <= Result.TotalPages)
Result.NextPage = Param.CurrentPage + 1;
if (Result.TotalCount == 0)
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 (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);
}
}
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;
}
[HttpGet]
public async Task<ActionResult<IEnumerable<Customer>>>
GetCustomers([FromQuery] PageSortParam pageSortParam)
{
PageList<Customer> list = await this.CustomerData.Get(pageSortParam);
Response.Headers.Add("X-PageSortResult", JsonSerializer.Serialize(list.Result));
return Ok(list);
}
[HttpGet("{customerId}")]
public async Task<ActionResult<Customer>> GetCustomer(int customerId)
{
return Ok(await this.CustomerData.GetCustomerById(customerId));
}
[HttpPut("{customerId}")]
public async Task<ActionResult<Customer>>
PutCustomer(int customerId, Customer customer)
{
return Ok(await this.CustomerData.Update(customerId, customer));
}
[HttpPost]
public async Task<ActionResult<Customer>> PostCustomer(Customer customer)
{
return Ok(await this.CustomerData.Add(customer));
}
[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; }
[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; }
[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 (SortField == null)
SortDir = new SortDirection();
else if (SortDirNext != null)
SortDir = (SortDirection)SortDirNext;
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);
if (response.IsSuccessStatusCode)
CustomerList = await response.Content.ReadAsAsync<IEnumerable<Dto.Customer>>();
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