MVC中使用EF(3)：实现排序，过滤，分页

  在 ASP.NET MVC 程序中使用EntityFramework 实现排序、过滤和分页

原文链接:http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/sorting-filtering-and-paging-with-the-entity-framework-in-an-asp-net-mvc-application

By Tom Dykstra|July 30, 2013

Translated by litdwg

Contoso University示例网站演示如何使用Entity Framework 5创建ASP.NET MVC 4应用程序。

Entity Framework有三种处理数据的方式: Database First, Model First, and Code First. 本指南使用代码优先。其它方式请查询资料。

示例程序是为Contoso University建立一个网站。功能包括：学生管理、课程创建、教师分配。 本系列指南逐步讲述如何实现这一网站程序。

本示例程序基于 ASP.NET MVC.如果使用 ASP.NET Web Forms model, 请查看 Model Binding and Web Forms系列指南和 ASP.NET Data Access Content Map.

如有问题，可在这些讨论区提问： ASP.NET Entity Framework forum, the Entity Framework and LINQ to Entities forum, or StackOverflow.com.

如有问题，可在这些讨论区提问： ASP.NET Entity Framework forum, the Entity Framework and LINQ to Entities forum, or StackOverflow.com.

上一指南中你已经实现了一系列页面完成对Student 实体的 CRUD 操作.本指南将完成对数据的排序、过滤和分页。 还会创建一个实现了分组的简单页面.

下图显示做完之后的效果，点击列名可对数据进行排序.

为 Students Index页面添加排序

为了添加排序功能 需要改变Student 控制器的 Index方法 向其添加代码.

为 Index 方法添加排序功能

在 Controllers\StudentController.cs, 使用如下代码替换 Index 方法:

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());}

代码接收URL中的sortOrder 参数. 通过 ASP.NET MVC 作为参数传递到行为方法。 参数值将为 "Name" 或"Date", 或后面跟着"_desc"。. 默认排序为升序 .

页面第一次加载时没有参数，因此switch 执行到最后默认 按 LastName的升序排列. 当用户点击了列标题之后， sortOrder 值将被传递到方法中.

 ViewBag 变量记录了下次排序应该传递的排序参数:

ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";ViewBag.DateSortParm = sortOrder == "Date" ? "Date_desc" : "Date";

这是交替语句. 第一句的意思是 sortOrder 参数为NULL或者空值,ViewBag.NameSortParm 应设为 "name_desc"; 否则,将其设为空值. 这两条语句使得当前排序和当前排序连接的关系如下:

当前排序Last Name 链接Date 链接Last Name ascendingdescendingascendingLast Name descendingascendingascendingDate ascendingascendingdescendingDate descendingascendingascending

方法使用 LINQ to Entities 指明排序的列. 在switch 之前创建了一个 IQueryable 变量, 并在 switch 语句中修改此变量, 然后调用 ToList 方法. 在创建和修改 IQueryable 变量时, 不会向数据库发送查询命令. 直到将 IQueryable 对象通过如 ToList方法转成集合才会执行查询命令. 因此，代码只生成了一条查询语句且在 return View 语句才执行.

在Student Index 视图添加列标题排序链接

在Views\Student\Index.cshtml, 使用高亮部分的代码替换 <tr> 和<th> 元素:

<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)    {

代码使用 ViewBag 属性中的值设置链接中的排序参数.

运行并测试排序是否成功.

在Students Index 页面添加查询

添加一个文本框和一个按钮用于查询。

为 Index 方法添加过滤功能

在 Controllers\StudentController.cs, 如下代码替换 Index 方法 (改动处已设为高亮):

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());}

为Index 添加了searchString 参数，同时添加了where过滤条件.  where 语句只有在searchString 有值的时候执行.

注意：很多情况下在Entity Framework实体集或在内存集合中作为扩展方法调用同样的方法，通常结果是一样的，但有时结果会不同。比如，.NET Framework中如果给Contains方法传递一个空字符串作为参数将返回所有行，但Entity Framework provider for SQL Server Compact 4.0 返回零行。因此本例的代码（把Where语句放在If语句内）确保在SQL Server所有版本中得到同样的结果。而且.NET Framework 的Contains方法是大小写敏感的，但Entity Framework provider for SQL Server 默认大小写不敏感。因此，调用ToUpper 方法确保在你使用repository修改了代码之后得到的结果不会有变化，repository将返回一个IEnumerable集合而不是IQueryable对象。（当在IEnumerable 调用Contains方法，由 .NET Framework来处理；当在IQueryable 调用Contains时，是由 database provider来处理的。）

为 Student Index 视图添加搜索框

在 Views\Student\Index.cshtml, 在table标签之前添加如下高亮代码：

<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>

运行页面，测试过滤功能是否完成.

注意URL中并不包括查询字符串, 也就是说当你添加书签之后，再次使用此书签不会出现过滤.随后你将修改查询按钮使用关键词过滤信息.

为 Students Index 添加分页

为实现分页，请安装 PagedList.Mvc NuGet 包。在Index 方法做些改动，在Index 视图添加分页链接。 PagedList.Mvc 是很好用的分页排序包之一，这里使用只是为了示例并不是说 PagedList.Mvc就是最好的。

安装 PagedList.MVC NuGet 包

 NuGet PagedList.Mvc 自动安装其依赖的 PagedList包.PagedList包安装 PagedList 集合类型和相应的针对IQueryable 或IEnumerable 的扩展方法 . 扩展方法根据IQueryable 或IEnumerable生成一个 PagedList 集合 ,  PagedList collection 提供分页相关的属性和方法。PagedList.Mvc安装一个 paging helper 用来显示分页按钮.

从工具菜单, 选择Library Package Manager -> Manage NuGet Packages.

在 Manage NuGet Packages 对话框, 选择 Online 然后在搜索框输入 "paged" .浏览到PagedList.Mvc 包, 点击安装.

在选择项目对话框选中项目, 点击 OK.

为 Index 方法添加分页功能

在 Controllers\StudentController.cs, 添加 using 语句引入 PagedList 命名空间:

using PagedList;

使用如下代码替换Index 方法:

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 = students.OrderBy(s => s.LastName);         break;   }   int pageSize = 3;   int pageNumber = (page ?? 1);   return View(students.ToPagedList(pageNumber, pageSize));}

代码为方法添加 page 参数, 当前排序参数, 当前过滤参数:

public ActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)

页面第一次显示,或者用户没有点击分页链接或排序, 所有参数都是 null.  如果点击分页链接,  page 变量的值是要显示页的页码.

 ViewBag 属性记录当前排序, 为了保障分页数据一致，分页链接中需要包含排序信息:

ViewBag.CurrentSort = sortOrder;

另一个属性 ViewBag.CurrentFilter, 存储当前过滤参数. 页码链接必须包含此参数以保证分页数据对过滤有效, 当页面重新加载也能重置搜索框内的信息.如果过滤字符串在分页时发生变化, 页面跳转到第一页, 因为新的过滤导致数据不一样了. 当搜索框内容改变而且点击了提交按钮之后过滤才会改变. 在这种情况下,  searchString 参数的值不再是 null.

if (searchString != null)        page = 1;    else        searchString = currentFilter;

方法最后, IQueryable的ToPagedList 扩展方法将 student 查询结果转为支持分页的集合中的一页. 这一页内容将传递给视图:

int pageSize = 3;int pageNumber = (page ?? 1);return View(students.ToPagedList(pageNumber, pageSize));

ToPagedList 方法需要一个页码参数.  (page ?? 1) 的意思是如果 page 不为null则返回, 如果 page是 null则返回1.

为 Student Index 视图添加分页链接

在Views\Student\Index.cshtml, 使用如下代码:

@model PagedList.IPagedList<ContosoUniversity.Models.Student>@using PagedList.Mvc; <link href="~/Content/PagedList.css" rel="stylesheet" type="text/css" />@{    ViewBag.Title = "Students";}<h2>Students</h2><p>    @Html.ActionLink("Create New", "Create")</p>@using (Html.BeginForm("Index", "Student", FormMethod.Get)){    <p>        Find by name: @Html.TextBox("SearchString", ViewBag.CurrentFilter as string)          <input type="submit" value="Search" />    </p>}<table><tr>    <th></th>    <th>        @Html.ActionLink("Last Name", "Index", new { sortOrder=ViewBag.NameSortParm, currentFilter=ViewBag.CurrentFilter })    </th>    <th>        First Name    </th>    <th>        @Html.ActionLink("Enrollment Date", "Index", new { sortOrder = ViewBag.DateSortParm, currentFilter = ViewBag.CurrentFilter })    </th></tr>@foreach (var item in Model) {    <tr>        <td>            @Html.ActionLink("Edit", "Edit", new { id=item.StudentID }) |            @Html.ActionLink("Details", "Details", new { id=item.StudentID }) |            @Html.ActionLink("Delete", "Delete", new { id=item.StudentID })        </td>        <td>            @Html.DisplayFor(modelItem => item.LastName)        </td>        <td>            @Html.DisplayFor(modelItem => item.FirstMidName)        </td>        <td>            @Html.DisplayFor(modelItem => item.EnrollmentDate)        </td>    </tr>}</table><br />Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @[e-mail]( Model, page => Url.Action("Index", new { page, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter }) )

 @model 语句表明使用 PagedList 对象而不再是List 对象.

  using 语句引入 PagedList.Mvc 以便为分页按钮使用MVC helper . 

此代码覆盖了之前的，BeginForm 标明使用 FormMethod.Get.

@using (Html.BeginForm("Index", "Student", FormMethod.Get)){    <p>        Find by name: @Html.TextBox("SearchString", ViewBag.CurrentFilter as string)          <input type="submit" value="Search" />    </p>} 

 默认 BeginForm 提交使用 POST, 也就是说提交内容包含在 HTTP 消息中而不是 URL中. 如果指明 HTTP GET, 参数将通过UR传递, 添加书签之后 URL才有效.  W3C guidelines for the use of HTTP GET规范建议如果不会引起更新应使用 GET.

文本框的内容设置了默认值，这样当点击分页链接之后搜索关键词不会丢失.

 Find by name: @Html.TextBox("SearchString", ViewBag.CurrentFilter as string)  

排序链接中包含了过滤关键词以便保证重新排序时依然过滤:

 @Html.ActionLink("Last Name", "Index", new { sortOrder=ViewBag.NameSortParm, currentFilter=ViewBag.CurrentFilter })

当前页和总页数也有显示

Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount

如果没有内容, 将显示"Page 0 of 0". (这种情况 page number大于 page count，因为 Model.PageNumber 是1,Model.PageCount 是0.)

页码按钮通过 PagedListPager helper显示:

@Html.PagedListPager( Model, page => Url.Action("Index", new { page }) )

 PagedListPager helper 提供很多自定义功能，详细信息请查看TroyGoode  / PagedList .

运行页面.

测试分页和过滤功能是否有效.

创建 About 页面显示 Student 统计信息

About将显示每个登记日期有多少学生进行登记 . 这需要使用分组. 通过以下步骤可实现:

• 创建 一个视图模型类，包含要传递给视图的数据.
• 修改Home 控制器的 About 方法.
• 修改 About 视图.

创建View Model

新建 ViewModels 文件夹. 在此文件夹添加 EnrollmentDateGroup.cs 代码如下:

using System;using System.ComponentModel.DataAnnotations;namespace ContosoUniversity.ViewModels{    public class EnrollmentDateGroup    {        [DataType(DataType.Date)]        public DateTime? EnrollmentDate { get; set; }        public int StudentCount { get; set; }    }}

修改 Home Controller

在HomeController.cs, 添加如下 using 语句:

using ContosoUniversity.DAL;using ContosoUniversity.ViewModels;

添加数据上下文变量:

    public class HomeController : Controller    {        private SchoolContext db = new SchoolContext();

 About 方法的代码如下:

public ActionResult About(){    var data = from student in db.Students               group student by student.EnrollmentDate into dateGroup               select new EnrollmentDateGroup()               {                   EnrollmentDate = dateGroup.Key,                   StudentCount = dateGroup.Count()               };    return View(data);}

 LINQ语句按登记日期对学生进行分组, 计算每个分组中的学生人数, 将结果存在 EnrollmentDateGroup视图模型变量中.

添加 Dispose 方法:

protected override void Dispose(bool disposing){    db.Dispose();    base.Dispose(disposing);}

修改 About 视图

 Views\Home\About.cshtml 代码如下:

@model IEnumerable<ContosoUniversity.ViewModels.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>

运行程序查看结果.

可选内容: 将程序部署到 Windows Azure（此部分暂不翻译:)）

程序目前是在 IIS Express上运行 .  如果希望其他人通过Internet访问，需要部署到 web hosting provider. 本部分介绍如何部署 Windows Azure Web Site. 

使用 Code First Migrations 部署数据库

To deploy the database you'll use Code First Migrations.  When you create the publish profile that you use to configure settings for deploying from Visual Studio, you'll select a check box that is labeled Execute Code First Migrations (runs on application start).  This setting causes the deployment process to automatically configure the application Web.config file on the destination server so that Code First uses theMigrateDatabaseToLatestVersion initializer class.

Visual Studio does not do anything with the database during the deployment process. When the deployed application accesses the database for the first time after deployment, Code First automatically creates the database or updates the database schema to the latest version. If the application implements a Migrations Seedmethod, the method runs after the database is created or the schema is updated.

Your Migrations Seed method inserts test data. If you were deploying to a production environment, you would have to change the Seed method so that it only inserts data that you want to be inserted into your production database. For example, in your current data model you might want to have real courses but fictional students in the development database. You can write a Seed method to load both in development, and then comment out the fictional students before you deploy to production. Or you can write a Seed method to load only courses, and enter the fictional students in the test database manually by using the application's UI.

Get a Windows Azure account

You'll need a Windows Azure account. If you don't already have one, you can create a free trial account in just a couple of minutes. For details, see Windows Azure Free Trial.

Create a web site and a SQL database in Windows Azure

Your Windows Azure Web Site will run in a shared hosting environment, which means it runs on virtual machines (VMs) that are shared with other Windows Azure clients. A shared hosting environment is a low-cost way to get started in the cloud. Later, if your web traffic increases, the application can scale to meet the need by running on dedicated VMs. If you need a more complex architecture, you can migrate to a Windows Azure Cloud Service. Cloud services run on dedicated VMs that you can configure according to your needs.

Windows Azure SQL Database is a cloud-based relational database service that is built on SQL Server technologies. Tools and applications that work with SQL Server also work with SQL Database.

1. In the Windows Azure Management Portal, click Web Sites in the left tab, and then click New.

   

2. Click CUSTOM CREATE.

   

   The New Web Site - Custom Create wizard opens.
   
3. In the New Web Site step of the wizard, enter a string in the URL box to use as the unique URL for your application. The complete URL will consist of what you enter here plus the suffix that you see next to the text box. The illustration shows "ConU", but that URL is probably taken so you will have to choose a different one.

   

4. In the Region drop-down list, choose a region close to you. This setting specifies which data center your web site will run in. 
   
5. In the Database drop-down list, choose Create a free 20 MB SQL database.

   

6. In the DB CONNECTION STRING NAME, enter SchoolContext.

   

   
   
7. Click the arrow that points to the right at the bottom of the box. The wizard advances to the Database Settings step.
8. In the Name box, enter ContosoUniversityDB.
9. In the Server box, select New SQL Database server. Alternatively, if you previously created a server, you can select that server from the drop-down list.
10. Enter an administrator LOGIN NAME and PASSWORD. If you selected New SQL Database server you aren't entering an existing name and password here, you're entering a new name and password that you're defining now to use later when you access the database. If you selected a server that you created previously, you’ll enter credentials for that server. For this tutorial, you won't select the Advanced  check box. The Advanced options enable you to set  the database collation.
11. Choose the same Region that you chose for the web site.
    
    
12. Click the check mark at the bottom right of the box to indicate that you're finished. 
    
    
    
    
    The following image shows using an existing SQL Server and Login. 
    
    
    
    The Management Portal returns to the Web Sites page, and the Status column shows that the site is being created. After a while (typically less than a minute), the Status column shows that the site was successfully created. In the navigation bar at the left, the number of sites you have in your account appears next to theWeb Sites icon, and the number of databases appears next to the SQL Databases icon.

 

Deploy the application to Windows Azure

1. In Visual Studio, right-click the project in Solution Explorer and select Publish from the context menu.
   
   
   
   
2. In the Profile tab of the Publish Web wizard, click Import.
   
   
   
   

3. If you have not previously added your Windows Azure subscription in Visual Studio, perform the following steps. In these steps you add your subscription so that the drop-down list under Import from a Windows Azure web site will include your web site.

   a. In the Import Publish Profile dialog box, click Import from a Windows Azure web site, and then clickAdd Windows Azure subscription.

   

   b. In the Import Windows Azure Subscriptions dialog box, click Download subscription file.

   

   c. In your browser window, save the .publishsettings file.

   

   Security Note: The publishsettings file contains your credentials (unencoded) that are used to administer your Windows Azure subscriptions and services. The security best practice for this file is to store it temporarily outside your source directories (for example in the Libraries\Documents folder), and then delete it once the import has completed. A malicious user who gains access to the .publishsettings file can edit, create, and delete your Windows Azure services.

   d. In the Import Windows Azure Subscriptions dialog box, click Browse and navigate to the .publishsettingsfile.

   

   e. Click Import.

   

4. In the Import Publish Profile dialog box, select Import from a Windows Azure web site, select your web site from the drop-down list, and then click OK.
   
   

5. In the Connection tab, click Validate Connection to make sure that the settings are correct.
   
   
   
   
6. When the connection has been validated, a green check mark is shown next to the Validate Connectionbutton. Click Next.
   
   
   
   
7. Open the Remote connection string drop-down list under SchoolContext and select the connection string for the database you created.
8. Select Execute Code First Migrations (runs on application start).
   
9. Uncheck Use this connection string at runtime for the UserContext (DefaultConnection), since this application is not using the membership database. 
   
   
   
   
10. Click Next.
11. In the Preview tab, click Start Preview.
    
    
    
    The tab displays a list of the files that will be copied to the server. Displaying the preview isn't required to publish the application but is a useful function to be aware of. In this case, you don't need to do anything with the list of files that is displayed. The next time you deploy this application, only the files that have changed will be in this list.
    
    
    
    
12. Click Publish.
    Visual Studio begins the process of copying the files to the Windows Azure server.
13. The Output window shows what deployment actions were taken and reports successful completion of the deployment.
    
    
    
    
14. Upon successful deployment, the default browser automatically opens to the URL of the deployed web site.
    The application you created is now running in the cloud. Click the Students tab.
    
    

At this point your SchoolContext database has been created in the Windows Azure SQL Database because you selected Execute Code First Migrations (runs on app start). The Web.config file in the deployed web site has been changed so that the MigrateDatabaseToLatestVersion initializer would run the first time your code reads or writes data in the database (which happened when you selected the Students tab):

The deployment process also created a new connection string (SchoolContext_DatabasePublish) for Code First Migrations to use for updating the database schema and seeding the database.

The DefaultConnection connection string is for the membership database (which we are not using in this tutorial). The SchoolContext connection string is for the ContosoUniversity database.

You can find the deployed version of the Web.config file on your own computer inContosoUniversity\obj\Release\Package\PackageTmp\Web.config. You can access the deployed Web.config file itself by using FTP. For instructions, see ASP.NET Web Deployment using Visual Studio: Deploying a Code Update. Follow the instructions that start with "To use an FTP tool, you need three things: the FTP URL, the user name, and the password."

Note: The web app doesn't implement security, so anyone who finds the URL can change the data. For instructions on how to secure the web site, see Deploy a Secure ASP.NET MVC app with Membership, OAuth, and SQL Database to a Windows Azure Web Site. You can prevent other people from using the site by using the Windows Azure Management Portal or Server Explorer in Visual Studio to stop the site. 

Code First Initializers

In the deployment section you saw the MigrateDatabaseToLatestVersion initializer being used. Code First also provides other initializers that you can use, including CreateDatabaseIfNotExists (the default), DropCreateDatabaseIfModelChanges and DropCreateDatabaseAlways. The DropCreateAlways initializer can be useful for setting up conditions for unit tests. You can also write your own initializers, and you can call an initializer explicitly if you don't want to wait until the application reads from or writes to the database. For a comprehensive explanation of initializers, see chapter 6 of the book Programming Entity Framework: Code First by Julie Lerman and Rowan Miller.

Summary

In this tutorial you've seen how to create a data model and implement basic CRUD, sorting, filtering, paging, and grouping functionality. In the next tutorial you'll begin looking at more advanced topics by expanding the data model.

Links to other Entity Framework resources can be found in the ASP.NET Data Access Content Map.