[转]Sorting, Filtering, and Paging with the Entity Framework in an ASP.NET MVC Application (3 of 10)
The Contoso University sample web application demonstrates how to create ASP.NET MVC 4 applications using the Entity Framework 5 Code First and Visual Studio 2012. For information about the tutorial series, see the first tutorial in the series. You can start the tutorial series from the beginning or download a starter project for this chapter and start here.
If you run into a problem you can’t resolve, download the completed chapter and try to reproduce your problem. You can generally find the solution to the problem by comparing your code to the completed code. For some common errors and how to solve them, see Errors and Workarounds.
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.
Add Column Sort Links to the Students Index Page
To add sorting to the Student Index page, you'll change the Index
method of the Student
controller and add code to the Student
Index view.
Add Sorting Functionality to the Index Method
In Controllers\StudentController.cs, replace the Index
method with the following code:
public ActionResult Index(string sortOrder)
{
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";
ViewBag.DateSortParm = sortOrder == "Date" ? "Date_desc" : "Date";
var students = from s in db.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(students.ToList());
}
This code receives a sortOrder
parameter from the query string in the URL. The query string value is provided by ASP.NET 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.
The first time the Index page is requested, there's no query string. The students are displayed in ascending order by LastName
, 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.
The two ViewBag
variables are used so that the view can configure the column heading hyperlinks with the appropriate query string values:
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";
ViewBag.DateSortParm = sortOrder == "Date" ? "Date_desc" : "Date";
These are ternary statements. The first one specifies that if the sortOrder
parameter is null or empty, ViewBag.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 ToList
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 ToList
. 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
In Views\Student\Index.cshtml, replace the <tr>
and <th>
elements for the heading row with the highlighted code:
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table>
<tr>
<th>
@Html.ActionLink("Last Name", "Index", new { sortOrder = ViewBag.NameSortParm })
</th>
<th>First Name
</th>
<th>
@Html.ActionLink("Enrollment Date", "Index", new { sortOrder = ViewBag.DateSortParm })
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
This code uses the information in the ViewBag
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.
After you click the Last Name heading, students are displayed in descending last name order.
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 Controllers\StudentController.cs, replace the Index
method with the following code (the changes are highlighted):
public ViewResult Index(string sortOrder, string searchString)
{
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";
var students = from s in db.Students
select s;
if (!String.IsNullOrEmpty(searchString))
{
students = students.Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper())
|| s.FirstMidName.ToUpper().Contains(searchString.ToUpper()));
}
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(students.ToList());
}
You've added a searchString
parameter to the Index
method. You've also added to the LINQ statement a where
clausethat selects only students whose first name or last name contains the search string. The search string value is received from a text box that you'll add to the Index view.The statement that adds the where clause is executed only if there's a value to search for.
Note In many cases you can call the same method either on an Entity Framework entity set or as an extension method on an in-memory collection. The results are normally the same but in some cases may be different. For example, the .NET Framework implementation of the Contains
method returns all rows when you pass an empty string to it, but the Entity Framework provider for SQL Server Compact 4.0 returns zero rows for empty strings. Therefore the code in the example (putting the Where
statement inside an if
statement) makes sure that you get the same results for all versions of SQL Server. Also, the .NET Framework implementation of the Contains
method performs a case-sensitive comparison by default, but Entity Framework SQL Server providers perform case-insensitive comparisons by default. Therefore, calling the ToUpper
method to make the test explicitly case-insensitive ensures that results do not change when you change the code later to use a repository, which will return 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.)
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>
@Html.ActionLink("Create New", "Create")
</p>
@using (Html.BeginForm())
{
<p>
Find by name: @Html.TextBox("SearchString")
<input type="submit" value="Search" /></p>
}
<table>
<tr>
Run the page, enter a search string, and click Search to verify that filtering is working.
Notice the URL doesn't contain the "an" search string, which means that if you bookmark this page, you won't get the filtered list when you use the bookmark. You'll change the Search button to use query strings for filter criteria later in the tutorial.
Add Paging to the Students Index Page
To add paging to the Students Index page, you'll start by installing the PagedList.Mvc NuGet package. Then you'll make additional changes in the Index
method and add paging links to the Index
view. PagedList.Mvc is one of many good paging and sorting packages for ASP.NET MVC, and its use here is intended only as an example, not as a recommendation for it over other options. The following illustration shows the paging links.
Install the PagedList.MVC NuGet Package
The NuGet PagedList.Mvc package automatically installs the PagedList package as a dependency. The PagedList package installs a PagedList
collection type and extension methods for IQueryable
and IEnumerable
collections. The extension methods create a single page of data in a PagedList
collection out of your IQueryable
or IEnumerable
, and the PagedList
collection provides several properties and methods that facilitate paging. The PagedList.Mvc package installs a paging helper that displays the paging buttons.
From the Tools menu, select Library Package Manager and then Manage NuGet Packages for Solution.
In the Manage NuGet Packages dialog box, click the Online tab on the left and then enter "paged" in the search box. When you see the PagedList.Mvc package, click Install.
In the Select Projects box, click OK.
Add Paging Functionality to the Index Method
In Controllers\StudentController.cs, add a using
statement for the PagedList
namespace:
using PagedList;
Replace the Index
method with the following code:
public ViewResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
ViewBag.CurrentSort = sortOrder;
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewBag.CurrentFilter = searchString;
var students = from s in db.Students
select s;
if (!String.IsNullOrEmpty(searchString))
{
students = students.Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper())
|| s.FirstMidName.ToUpper().Contains(searchString.ToUpper()));
}
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: // Name ascending
students