Sorting, filtering, paging, and grouping
By Tom Dykstra
The Contoso University sample web application demonstrates how to create ASP.NET Core 1.0 MVC web applications using Entity Framework Core 1.0 and Visual Studio 2015. For information about the tutorial series, see the first tutorial in the series.
In the previous tutorial, you implemented a set of web pages for basic CRUD operations for Student entities. In this tutorial you’ll add sorting, filtering, and paging functionality to the Students Index page. You’ll also create a page that does simple grouping.
The following illustration shows what the page will look like when you’re done. The column headings are links that the user can click to sort by that column. Clicking a column heading repeatedly toggles between ascending and descending sort order.
下图展示了最终的页面。点击列标题后，可按照相应的列进行排序。反复点击列标题可在升序、降序直接切换。
Sections: 章节
- Add Column Sort Links to the Students Index Page 向Students的Index页面增加列排序链接
- Add a Search Box to the Students Index page 向Students的Index页面增加查询框
- Add paging functionality to the Students Index page 向Students的Index页面增加分页功能
- Add paging functionality to the Index method 向Index方法增加分页功能
- Add paging links to the Student Index view 向Student的Index视图增加页链接
- Create an About page that shows Student statistics 增加About页面来显示学生的静态信息
- Summary 总结
Add Column Sort Links to the Students Index Page 在Students Index页面中增加列排序链接
To add sorting to the Student Index page, you’ll change the Index method of the Students controller and add code to the Student Index view.
要给Student的Index页面增加过滤器，要修改Students控制器的Index方法，并向Student的Index视图增加部分代码。
Add sorting Functionality to the Index method 向Index方法增加排序功能
In StudentsController.cs, replace the Index method with the following code:
在StudentsController.cs中，用下列代码替换Index方法：
public async Task<IActionResult> Index(string sortOrder)
{
ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
var students = from s in _context.Students
select s;
switch (sortOrder)
{
case "name_desc":
students = students.OrderByDescending(s => s.LastName);
break;
case "Date":
students = students.OrderBy(s => s.EnrollmentDate);
break;
case "date_desc":
students = students.OrderByDescending(s => s.EnrollmentDate);
break;
default:
students = students.OrderBy(s => s.LastName);
break;
}
return View(await students.AsNoTracking().ToListAsync());
}
This code receives a sortOrder parameter from the query string in the URL. The query string value is provided by ASP.NET Core MVC as a parameter to the action method. The parameter will be a string that’s either “Name” or “Date”, optionally followed by an underscore and the string “desc” to specify descending order. The default sort order is ascending.
此代码从 URL 中的查询字符串接收 sortOrder 参数。查询字符串的值由ASP.NET Core MVC 作为该方法的参数而提供。该参数将是一个字符串，可以是Name或者Date，可选情况是，后面跟着一条下划线和"desc"来指定降序排列。默认排序顺序升序。
The first time the Index page is requested, there’s no query string. The students are displayed in ascending order by last name, which is the default as established by the fall-through case in the switch statement. When the user clicks a column heading hyperlink, the appropriate sortOrder value is provided in the query string.
当索引页第一次被请求时，并没有查询字符串。学生默认情况下按姓氏降序排列，这在 switch 语句中缺省按升序排列。当用户单击列标题的超链接时，查询字符串就提供了适当 sortOrder 值。
The two ViewData elements (NameSortParm and DateSortParm) are used by the view to configure the column heading hyperlinks with the appropriate query string values.
public async Task<IActionResult> Index(string sortOrder)
{
ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
var students = from s in _context.Students
select s;
switch (sortOrder)
{
case "name_desc":
students = students.OrderByDescending(s => s.LastName);
break;
case "Date":
students = students.OrderBy(s => s.EnrollmentDate);
break;
case "date_desc":
students = students.OrderByDescending(s => s.EnrollmentDate);
break;
default:
students = students.OrderBy(s => s.LastName);
break;
}
return View(await students.AsNoTracking().ToListAsync());
}
These are ternary statements. The first one specifies that if the sortOrder parameter is null or empty, NameSortParm should be set to “name_desc”; otherwise, it should be set to an empty string. These two statements enable the view to set the column heading hyperlinks as follows:
| Current sort order | Last Name Hyperlink | Date Hyperlink |
|---|---|---|
| Last Name ascending | descending | ascending |
| Last Name descending | ascending | ascending |
| Date ascending | ascending | descending |
| Date descending | ascending | ascending |
The method uses LINQ to Entities to specify the column to sort by. The code creates an IQueryable variable before the switch statement, modifies it in the switch statement, and calls the ToListAsync method after the switch statement. When you create and modify IQueryable variables, no query is sent to the database. The query is not executed until you convert the IQueryable object into a collection by calling a method such as ToListAsync. Therefore, this code results in a single query that is not executed until the return View statement.
Add column heading hyperlinks to the Student Index view¶
Replace the code in Views/Students/Index.cshtml, with the following code to rearrange the column order and add column heading hyperlinks. The new column headings are highlighted.
@model IEnumerable<ContosoUniversity.Models.Student>
@{
ViewData["Title"] = "Index";
}
<h2>Index</h2>
<p>
<a asp-action="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>
<a asp-action="Index" asp-route-sortOrder="@ViewBag.NameSortParm">@Html.DisplayNameFor(model => model.LastName)</a>
</th>
<th>
@Html.DisplayNameFor(model => model.FirstMidName)
</th>
<th>
<a asp-action="Index" asp-route-sortOrder="@ViewBag.DateSortParm">@Html.DisplayNameFor(model => model.EnrollmentDate)</a>
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.LastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.FirstMidName)
</td>
<td>
@Html.DisplayFor(modelItem => item.EnrollmentDate)
</td>
<td>
<a asp-action="Edit" asp-route-id="@item.ID">Edit</a> |
<a asp-action="Details" asp-route-id="@item.ID">Details</a> |
<a asp-action="Delete" asp-route-id="@item.ID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
This code uses the information in ViewData properties to set up hyperlinks with the appropriate query string values.
Run the page and click the Last Name and Enrollment Date column headings to verify that sorting works.
Add a Search Box to the Students Index page
To add filtering to the Students Index page, you’ll add a text box and a submit button to the view and make corresponding changes in the Index method. The text box will let you enter a string to search for in the first name and last name fields.
Add filtering functionality to the Index method¶
In StudentsController.cs, replace the Index method with the following code (the changes are highlighted).
public async Task<IActionResult> Index(string sortOrder, string searchString)
{
ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";
ViewData["CurrentFilter"] = searchString; var students = from s in _context.Students
select s;
if (!String.IsNullOrEmpty(searchString))
{
students = students.Where(s => s.LastName.Contains(searchString)
|| s.FirstMidName.Contains(searchString));
}
switch (sortOrder)
{
case "name_desc":
students = students.OrderByDescending(s => s.LastName);
break;
case "Date":
students = students.OrderBy(s => s.EnrollmentDate);
break;
case "date_desc":
students = students.OrderByDescending(s => s.EnrollmentDate);
break;
default:
students = students.OrderBy(s => s.LastName);
break;
}
return View(await students.AsNoTracking().ToListAsync());
}
You’ve added a searchString parameter to the Index method. The search string value is received from a text box that you’ll add to the Index view. You’ve also added to the LINQ statement a where clause that selects only students whose first name or last name contains the search string. The statement that adds the where clause is executed only if there’s a value to search for.
Note 注意
Here you are calling the Where method on an IQueryable object, and the filter will be processed on the server. In some scenarios you might be calling the Where method as an extension method on an in-memory collection. (For example, suppose you change the reference to _context.Students so that instead of an EF DbSet it references a repository method that returns an IEnumerable collection.) The result would normally be the same but in some cases may be different.
在这里调用了IQueryable对象的Where方法，在服务器端执行过滤操作。在某些场景下，你或许在内存集合中调用Where方法的扩展方法。（例如：假定你更改了_context.Students的引用以替代EF DbSet，它引用了一个存在的方法，会返回一个IEnumerable集合。）通常情况下，两者的结果是相同的，但是在某些情况下可能是不同的。
For example, the .NET Framework implementation of the Contains method performs a case-sensitive comparison by default, but in SQL Server this is determined by the setting of the SQL Server instance. That setting defaults to case-insensitive. You could call the ToUpper method to make the test explicitly case-insensitive: Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper()). That would ensure that results stay the same if you change the code later to use a repository which returns an IEnumerable collection instead of an IQueryable object. (When you call the Contains method on an IEnumerable collection, you get the .NET Framework implementation; when you call it on an IQueryable object, you get the database provider implementation.) However, there is a performance penalty for this solution. The ToUpper code would put a function in the WHERE clause of the TSQL SELECT statement. That would prevent the optimizer from using an index. Given that SQL is mostly installed as case-insensitive, it’s best to avoid the ToUpper code until you migrate to a case-sensitive data store.
例如，.NET Frameword实施Contains方法时默认执行大小写敏感的对比，但是在SQL Server中，这取决于SQL Server的校验设置。该设置默认为大小写不敏感。你可以调用ToUpper方法使对比检测基于大小写不敏感：Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper())。 如果你后续将代码从IQueryable变成IEnumerable集合，那么使用已存在的数据这样就保证了检索结果的相同。（当你对一个IEnumerable集合执行Contains方法时，是由.Net Frameword实施的；当你对IQueryable对象执行该方法时，是有数据库提供者实施的。）然而，该解决方案有性能的损失。ToUpper会在TSQL的SELECT语句中的WHERE从句内放置一个函数，这样做将会阻止索引的使用。大多数情况下SQL是按照大小写不敏感来安装的，直到你将数据迁移到一个大小写敏感的数据库系统前，最好避免使用ToUpper。
Add a Search Box to the Student Index View
In Views/Student/Index.cshtml, add the highlighted code immediately before the opening table tag in order to create a caption, a text box, and a Search button.
<p>
<a asp-action="Create">Create New</a>
</p> <form asp-action="Index" method="get">
<div class="form-actions no-color">
<p>
Find by name: <input type="text" name="SearchString" value="@ViewData["currentFilter"]" />
<input type="submit" value="Search" class="btn btn-default" /> |
<a asp-action="Index">Back to List</a>
</p>
</div>
</form>
<table class="table">
This code uses the <form> tag helper to add the search text box and button. By default, the <form> tag helper submits form data with a POST, which means that parameters are passed in the HTTP message body and not in the URL as query strings. When you specify HTTP GET, the form data is passed in the URL as query strings, which enables users to bookmark the URL. The W3C guidelines recommend that you should use GET when the action does not result in an update.
Run the page, enter a search string, and click Search to verify that filtering is working.
Notice that the URL contains the search string.
http://localhost:5813/Students?SearchString=an
If you bookmark this page, you’ll get the filtered list when you use the bookmark. Adding method="get" to the form tag is what caused the query string to be generated.
At this stage, if you click a column heading sort link you’ll lose the filter value that you entered in the Search box. You’ll fix that in the next section.
Add paging functionality to the Students Index page
To add paging to the Students Index page, you’ll create a PaginatedList class that uses Skip and Take statements to filter data on the server instead of always retrieving all rows of the table. Then you’ll make additional changes in the Index method and add paging buttons to the Index view. The following illustration shows the paging buttons.
In the project folder create PaginatedList.cs, and then replace the template code with the following code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore; public class PaginatedList<T> : List<T>
{
public int PageIndex { get; private set; }
public int TotalPages { get; private set; } public PaginatedList(List<T> items, int count, int pageIndex, int pageSize)
{
PageIndex = pageIndex;
TotalPages = (int)Math.Ceiling(count / (double)pageSize); this.AddRange(items);
} public bool HasPreviousPage
{
get
{
return (PageIndex > );
}
} public bool HasNextPage
{
get
{
return (PageIndex < TotalPages);
}
} public static async Task<PaginatedList<T>> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize)
{
var count = await source.CountAsync();
var items = await source.Skip((pageIndex - ) * pageSize).Take(pageSize).ToListAsync();
return new PaginatedList<T>(items, count, pageIndex, pageSize);
}
}
The CreateAsync method in this code takes page size and page number and applies the appropriate Skip and Take statements to the IQueryable. When ToListAsync is called on the IQueryable, it will return a List containing only the requested page. The properties HasPreviousPage and `HasNextPage can be used to enable or disable Previous and Next paging buttons.
代码中的 CreateAsync 方法需要页面大小和页编号，并且有可操作 IQueryable 的Sikp和Take语句。当调用 IQueryable类型的ToListAsync时，它将返回仅包含请求页的List。HasPreviousPage 和 HasNextPage属性可用于启用或禁用上一页和下一页分页按钮。
A CreateAsync method is used instead of a constructor to create the PaginatedList<T> object because constructors can’t run asynchronous code.
使用CreateAsync方法，而不是使用构造函数，来创建 PaginatedList <T>对象，因为构造函数不能运行异步代码。
Add paging functionality to the Index method
In StudentsController.cs, replace the Index method with the following code.
public async Task<IActionResult> Index(
string sortOrder,
string currentFilter,
string searchString,
int? page)
{
ViewData["CurrentSort"] = sortOrder;
ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date"; if (searchString != null)
{
page = ;
}
else
{
searchString = currentFilter;
} ViewData["CurrentFilter"] = searchString; var students = from s in _context.Students
select s;
if (!String.IsNullOrEmpty(searchString))
{
students = students.Where(s => s.LastName.Contains(searchString)
|| s.FirstMidName.Contains(searchString));
}
switch (sortOrder)
{
case "name_desc":
students = students.OrderByDescending(s => s.LastName);
break;
case "Date":
students = students.OrderBy(s => s.EnrollmentDate);
break;
case "date_desc":
students = students.OrderByDescending(s => s.EnrollmentDate);
break;
default:
students = students.OrderBy(s => s.LastName);
break;
} int pageSize = ;
return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), page ?? , pageSize));
}
This code adds a page number parameter, a current sort order parameter, and a current filter parameter to the method signature.
public async Task<IActionResult> Index(
string sortOrder,
string currentFilter,
string searchString,
int? page)
The first time the page is displayed, or if the user hasn’t clicked a paging or sorting link, all the parameters will be null. If a paging link is clicked, the page variable will contain the page number to display.
The ViewData element named CurrentSort provides the view with the current sort order, because this must be included in the paging links in order to keep the sort order the same while paging.
The ViewData element named CurrentFilter provides the view with the current filter string. This value must be included in the paging links in order to maintain the filter settings during paging, and it must be restored to the text box when the page is redisplayed.
If the search string is changed during paging, the page has to be reset to 1, because the new filter can result in different data to display. The search string is changed when a value is entered in the text box and the Submit button is pressed. In that case, the searchString parameter is not null.
if (searchString != null)
{
page = ;
}
else
{
searchString = currentFilter;
}
At the end of the Index method, the PaginatedList.CreateAsync method converts the student query to a single page of students in a collection type that supports paging. That single page of students is then passed to the view.
在Index方法末尾，PaginateList.CreateAsync方法将student查询转化为单页students集合类型，该类型支持分页。该单页sutdents被传递到视图。
return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), page ?? , pageSize));
The PaginatedList.CreateAsync method takes a page number. The two question marks represent the null-coalescing operator. The null-coalescing operator defines a default value for a nullable type; the expression (page ?? 1) means return the value of page if it has a value, or return 1 if page is null.
Add paging links to the Student Index view
In Views/Students/Index.cshtml, replace the existing code with the following code. The changes are highlighted.（非高亮的也有更改，请注意）
@model PaginatedList<ContosoUniversity.Models.Student>
@{
ViewData["Title"] = "Students";
}
<h2>Students</h2>
<p>
<a asp-action="Create">Create New</a>
</p>
<form asp-action="Index" method="get">
<div class="form-actions no-color">
<p>
Find by name: <input type="text" name="SearchString" value="@ViewData["CurrentFilter"]" />
<input type="submit" value="Search" class="btn btn-default btn" /> |
<a asp-action="Index">Back to Full List</a>
</p>
</div>
</form>
<table class="table">
<thead>
<tr>
<th>
First Name
</th>
<th>
<a asp-action="Index" asp-route-sortOrder="@ViewData["NameSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Last Name</a>
</th>
<th>
<a asp-action="Index" asp-route-sortOrder="@ViewData["DateSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Enrollment Date</a>
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.FirstMidName)
</td>
<td>
@Html.DisplayFor(modelItem => item.LastName)
</td>
<td>
@Html.DisplayFor(modelItem => item.EnrollmentDate)
</td>
<td>
<a asp-action="Edit" asp-route-id="@item.ID">Edit</a> |
<a asp-action="Details" asp-route-id="@item.ID">Details</a> |
<a asp-action="Delete" asp-route-id="@item.ID">Delete</a>
</td>
</tr>
}
</tbody>
</table>
@{
var prevDisabled = !Model.HasPreviousPage ? "disabled" : "";
var nextDisabled = !Model.HasNextPage ? "disabled" : "";
}
<a asp-action="Index"
asp-route-sortOrder="@ViewData["CurrentSort"]"
asp-route-page="@(Model.PageIndex - 1)"
asp-route-currentFilter="@ViewData["CurrentFilter"]"
class="btn btn-default @prevDisabled btn">
Previous
</a>
<a asp-action="Index"
asp-route-sortOrder="@ViewData["CurrentSort"]"
asp-route-page="@(Model.PageIndex + 1)"
asp-route-currentFilter="@ViewData["CurrentFilter"]"
class="btn btn-default @nextDisabled btn">
Next
</a>
The @model statement at the top of the page specifies that the view now gets a PaginatedList<T> object instead of a List<T> object.
The column header links use the query string to pass the current search string to the controller so that the user can sort within filter results:
列标题链接使用查询字符串传递给控制器的当前搜索字符串，以便用户可以在筛选结果中排序︰
<a asp-action="Index" asp-route-sortOrder="@ViewData["DateSortParm"]" asp-route-currentFilter ="@ViewData["CurrentFilter"]">Enrollment Date</a>
The paging buttons are displayed by tag helpers:
<a asp-action="Index"
asp-route-sortOrder="@ViewData["CurrentSort"]"
asp-route-page="@(Model.PageIndex - 1)"
asp-route-currentFilter="@ViewData["CurrentFilter"]"
class="btn btn-default @prevDisabled btn">
Previous
</a>
Run the page.
Click the paging links in different sort orders to make sure paging works. Then enter a search string and try paging again to verify that paging also works correctly with sorting and filtering.
Create an About page that shows Student statistics
For the Contoso University website’s About page, you’ll display how many students have enrolled for each enrollment date. This requires grouping and simple calculations on the groups. To accomplish this, you’ll do the following:
- Create a view model class for the data that you need to pass to the view. 为要传递给视图的数据创建一个视图模型类
- Modify the About method in the Home controller. 修改Home控制器中的About方法
- Modify the About view. 修改About视图
Create the view model 创建视图模型
Create a SchoolViewModels folder in the Models folder.
在Models文件夹内创建SchoolViewModels文件夹。
In the new folder, add a class file EnrollmentDateGroup.cs and replace the template code with the following code:
在新文件夹中，添加一个类文件EnrollmentDateGroup.cs，然后用下列代码替换模板中的代码：
using System;
using System.ComponentModel.DataAnnotations; namespace ContosoUniversity.Models.SchoolViewModels
{
public class EnrollmentDateGroup
{
[DataType(DataType.Date)]
public DateTime? EnrollmentDate { get; set; } public int StudentCount { get; set; }
}
}
Modify the Home Controller 修改Home控制器
In HomeController.cs, add the following using statements at the top of the file:
在HomeController.cs中，在文件的顶部添加下列using语句：
using Microsoft.EntityFrameworkCore;
using ContosoUniversity.Data;
using ContosoUniversity.Models.SchoolViewModels;
Add a class variable for the database context immediately after the opening curly brace for the class, and get an instance of the context from ASP.NET Core DI:
在打开类的大括号之后，立即为数据库上下文添加一个类变量，然后从ASP.NET Core DI中取得上下文实例：
public class HomeController : Controller
{
private readonly SchoolContext _context; public HomeController(SchoolContext context)
{
_context = context;
}
Replace the About method with the following code:
public async Task<ActionResult> About()
{
IQueryable<EnrollmentDateGroup> data =
from student in _context.Students
group student by student.EnrollmentDate into dateGroup
select new EnrollmentDateGroup()
{
EnrollmentDate = dateGroup.Key,
StudentCount = dateGroup.Count()
};
return View(await data.AsNoTracking().ToListAsync());
}
The LINQ statement groups the student entities by enrollment date, calculates the number of entities in each group, and stores the results in a collection of EnrollmentDateGroup view model objects.
Note
In the 1.0 version of Entity Framework Core, the entire result set is returned to the client, and grouping is done on the client. In some scenarios this could create performance problems. Be sure to test performance with production volumes of data, and if necessary use raw SQL to do the grouping on the server. For information about how to use raw SQL, see the last tutorial in this series.
Modify the About View
Replace the code in the Views/Home/About.cshtml file with the following code:
@model IEnumerable<ContosoUniversity.Models.SchoolViewModels.EnrollmentDateGroup>
@{
ViewBag.Title = "Student Body Statistics";
}
<h2>Student Body Statistics</h2>
<table>
<tr>
<th>
Enrollment Date
</th>
<th>
Students
</th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.EnrollmentDate)
</td>
<td>
@item.StudentCount
</td>
</tr>
}
</table>
Run the app and click the About link. The count of students for each enrollment date is displayed in a table.