Entity Framework in ASP.NET MVC Application (三)

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.

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

Adding Sorting Functionality to the Index Method

In Controllers\StudentController.cs, replace the Index method with the following code:

public ViewResult 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, which 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 a space and the string "desc" to specify descending order.

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.

There are four possibilities, depending on how the data is currently sorted:

• If the current order is Last Name ascending, the Last Name link must specify Last Name descending, and the Enrollment Date link must specify Date ascending.
• If the current order is Last Name descending, the links must indicate Last Name ascending (that is, empty string) and Date ascending.
• If the current order is Date ascending, the links must indicate Last Name ascending and Date descending.
• If the current order is Date descending, the links must indicate Last Name ascending and Date 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.

Adding 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 following code:

<tr>

    <th></th>

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

</tr>

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 column headings to verify that sorting works.

Adding a Search Box to the Students Index Page

To add filtering to the Student 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.

Adding Filtering Functionality to the Index Method

In Controllers\StudentController.cs, replace the Index method with the following code:

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 a where clause to the LINQ statement that 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 later to the Index view. The statement that adds the where clause is executed only if there's a value to search for:

if (!String.IsNullOrEmpty(searchString))

{

    students = students.Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper())

                           || s.FirstMidName.ToUpper().Contains(searchString.ToUpper()));

}

Note   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.)

Adding a Search Box to the Student Index View

In Views\Student\Index.cshtml, add a caption, a text box, and a Search button immediately before the opening table tag:

@using (Html.BeginForm())

{

    <p>

        Find by name: @Html.TextBox("SearchString") &nbsp;

        <input type="submit" value="Search" /></p>

}

Run the page, enter a search string, and click Search to verify that filtering is working.

Adding Paging to the Students Index Page

To add paging to the Student Index page, you'll start by installing the PagedList NuGet package. Then you'll make additional changes in the Index method and add paging links to the Index view. The following illustration shows the paging links.

Installing the PagedList NuGet Package

The NuGet PagedList package installs a PagedList collection type. When you put query results in a PagedList collection, several properties and methods are provided that facilitate paging.

In Visual Studio, make sure the project (not the solution) is selected. From the Tools menu, select Library Package Manager and then Add Library Package Reference.

In the Add Library Package Reference dialog box, click the Online tab on the left and then enter "pagedlist" in the search box. When you see the PagedList package, click Install.

Adding 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 (Request.HttpMethod == "GET")

            {

                searchString = currentFilter;

            }

            else

            {

                page = 1;

            }

            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:

                    students = students.OrderBy(s => s.LastName);

                    break;

            }



            int pageSize = 3;

            int pageIndex = (page ?? 1) - 1;

            return View(students.ToPagedList(pageIndex, pageSize));

        }

This code adds a page parameter, a current sort order parameter, and a current filter parameter to the method signature, as shown here:

public ViewResult 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 link, the page variable is null. If a paging link is clicked, the page variable will contain the page number to display.

A ViewBag property 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:

ViewBag.CurrentSort = sortOrder;

Another ViewBag property provides the view with the current filter string, because this string must be restored to the text box when the page is redisplayed. In addition, the string must be included in the paging links in order to maintain the filter settings during paging. Finally, 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, hence the original page might not even exist anymore.

            if (Request.HttpMethod == "GET")

            {

                searchString = currentFilter;

            }

            else

            {

                page = 1;

            }

            ViewBag.CurrentFilter = searchString;

At the end of the method, the student query is converted to a PagedList instead of to a List so that it will be passed to the view in a collection that supports paging. This is the code:

int pageSize = 3;

int pageIndex = (page ?? 1) - 1;

return View(students.ToPagedList(pageIndex, pageSize));

The ToPagedList method takes a page index value, which is zero-based, rather than a page number, which is one-based. Therefore, the code subtracts 1 from the page number in order to get the page index. (The two question marks represent an operator that 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. In other words, set pageIndex to page - 1 if page is not null, or set it to 1 - 1 if page is null.)

Adding Paging Links to the Student Index View

In Views\Student\Index.cshtml, replace the existing code with the following code:

@model PagedList.IPagedList<ContosoUniversity.Models.Student>



@{

    ViewBag.Title = "Students";

}



<h2>Students</h2>



<p>

    @Html.ActionLink("Create New", "Create")

</p>

@using (Html.BeginForm())

{

    <p>

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

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



<div>

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

    of @Model.PageCount

    &nbsp;

    @if (Model.HasPreviousPage)

    {

        @Html.ActionLink("<<", "Index", new { page = 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  })

        @Html.Raw("&nbsp;");

        @Html.ActionLink("< Prev", "Index", new { page = Model.PageNumber - 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  })

    }

    else

    {

        @:<<

        @Html.Raw("&nbsp;");

        @:< Prev

    }

    &nbsp;

    @if (Model.HasNextPage)

    {

        @Html.ActionLink("Next >", "Index", new { page = Model.PageNumber + 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  })

        @Html.Raw("&nbsp;");

        @Html.ActionLink(">>", "Index", new { page = Model.PageCount, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  })

    }

    else

    {

        @:Next >

        @Html.Raw("&nbsp;")

        @:>>

    }

</div>

The @model statement at the top of the page specifies that the view now gets a PagedList object instead of a List object.

The text box is initialized with the current search string so that the user can page through filter results without the search string disappearing:

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

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:

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

On one line at the bottom of the page, this code displays the following navigation UI:

Page [current page number] of [total number of pages]    <<   <  Prev Next  >   >>

The << symbol is a link to the first page, <  Prev is a link to the previous page, and so on. If the user is currently on page 1, the links to move backward are disabled; similarly, if the user is on the last page, the links to move forward are disabled. Each paging link passes the new page number and the current sort order and search string to the controller in the query string. This lets you maintain the sort order and filter results during paging.

If there are no pages to display, "Page 0 of 0" is shown. (In that case the page number is greater than the page count because Model.PageNumber is 1, and Model.PageCount is 0.)

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.

Creating 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.
• Modify the About view.

Creating the View Model

Create a ViewModels folder. In that folder, create EnrollmentDateGroup.cs and replace the existing code with the following code:

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;



namespace ContosoUniversity.ViewModels

{

    public class EnrollmentDateGroup

    {

        [DisplayFormat(DataFormatString = "{0:d}")]

        public DateTime? EnrollmentDate { get; set; }



        public int StudentCount { get; set; }

    }

}

Modifying the Home Controller

In HomeController.cs, add the following using statements:

using ContosoUniversity.DAL;

using ContosoUniversity.Models;

using ContosoUniversity.ViewModels;

Add a class variable for the database context:

private SchoolContext db = new SchoolContext();

Replace the About method with the following code:

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

}

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.

Modifying the About View

Replace the code in the Views\Home\About.cshtml file with the following code:

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

            @String.Format("{0:d}", item.EnrollmentDate)

        </td>

        <td>

            @item.StudentCount

        </td>

    </tr>

}

</table>

Run the page. The count of students for each enrollment date is displayed in a table.

You've now 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 at the end of the last tutorial in this series.