使用ASP .NET Core Razor页面，Web API和实体框架进行分页和排序
如何实现分页和排序以产生良好的性能
如何使用.NETCoreRazor页面，WebAPI和实体框架实现分页和排序以产生良好的性能。
该项目的特点是：
选择页面大小
页面导航
显示记录号
列排序
核心课程
第一件事是定义用户可以要求应用程序获取的内容：
页面大小
页码
字段排序
排序方向
代码如下所示：
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 }1234567891011121314复制代码类型：[html]
接下来，我们定义应用程序应返回的内容，如下所示：
记录总数
总页数
前一页编号-用户导航到前一页时的编号
下一页编号-用于导航到下一页
当前页面上的第一个记录号
当前页面上的最后一条记录号
代码如下所示：
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; } }123456789复制代码类型：[html]
使用user参数和定义的结果，我们创建PageList<T>继承自的类，List<T>以便将结果存储在中List。该类将使用参数并找到结果。
下面显示了代码以及该GetData()方法所需的逻辑。从数据库获取记录的行是对的调用ToListAsync()，它将跳过调用不需要的记录，Skip()而仅通过调用获取所需的记录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 } }123456789101112131415161718192021222324252627282930313233343536373839404142复制代码类型：[html]
数据层
的定义Customer在数据层项目中定义：
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; } }12345678910复制代码类型：[html]
我们将定义常规CRUD的接口和实现，唯一的区别是该Get()方法将使用PageList<T>该类来仅获取所需的记录，从而通过将工作推送到数据库来提高性能。下面是界面：
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); }12345678复制代码类型：[html]
并执行：
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; } }123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657复制代码类型：[html]
在DbContext从实体框架是简单的：
public class StarterDbContext : DbContext { public DbSet<Customer> Customer { get; set; } public StarterDbContext(DbContextOptions<StarterDbContext> options) : base(options) { } }123456789复制代码类型：[html]
API
在WebAPI项目中，我们定义GetCustomers()将接受PageSortParam作为参数的Get()方法，在数据层中调用该方法，从PageSortResult响应标头中添加来自的元数据（例如记录总数，页面总数等）。，并在响应正文中提供实际记录：
[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)); } }123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051复制代码类型：[html]
剃刀页
我们定义用户可以选择的页面大小：
public IEnumerable<SelectListItem> PageSizeList { get; set; } = new SelectList(new List<int> { 5, 10, 25, 50 });12复制代码类型：[html]
然后，我们[BindProperty(SupportsGet=true)]将值传递给HTML页面，然后又将值取回。对于每次旅行，我们都需要从HTML页面传递以下属性：
PageSize–用户请求的页面大小
PageNumber–用户当前所在的页码
SortField–用户要求进行排序的列
SortDir–应该排序的方向
SortDirNext–用户单击列链接时的下一个排序方向
[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; }123456789101112131415复制代码类型：[html]
该OnGet()方法将获取每个属性的值，构建参数并将其传递给API，然后显示记录并显示有关页面的元数据。以下是完整的代码：
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()); } } }123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869复制代码类型：[html]
html页面将通过使用http提交表单get或单击链接来接受用户的输入。请注意，参数是在每个动作中传递的。在列标题链接中仅指定排序列的名称和方向：
<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>123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122复制代码类型：[html]
就这样！希望您发现这对构建页面调度和排序应用程序很有用。
赞 (0)