Training course. Sort, filter, and paginate with Entity Framework in an ASP.NET MVC application

This is an extension to the Entity Framework and ASP.NET MVC 3 development article series. You can find the first chapters at the following links: Creating an Entity Framework Data Model for an ASP.NET MVC Application and Implementing Basic CRUD Functionality with Entity Framework in an ASP Application the MVC .NET .

In the previous lesson, we implemented pages for CRUD operations for Student entities. In this lesson, we will add sorting, filtering and pagination, and also create a page that will have a simple grouping.

The following image shows the final page view. Column headers are links that sort in descending and ascending order.

Adding Sorter Headers to Columns on the Students Index Page

To add sorting, you need to change the Index method of the Student controller and add code to the Student Index view.

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

The method takes sortOrder as a parameter from the query string into the URL that ASP.NET provides as a parameter for the method. The parameter is a string “Name” or “Date” with (optionally) a subsequent space and a string “desc” to indicate what needs to be sorted in descending order.

When the Index page is called for the first time, there is no query line, and students are displayed in ascending order LastName, which is indicated as the default option in switch. After the user clicks on the column heading, the corresponding sortOrder value is added to 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";

This is a ternary statement. The first one states that if sortOrder is null or empty, then the value of ViewBag.NameSortParam is set to “Name desc”, otherwise it is set to an empty string.

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

• If sorted ascending of Last the Name , Link of Last the Name should point to sort descending of Last the Name and link Enrollment link Date ascending sort by Date , respectively.
• If sorted descending by Last Name , links should indicate ascending sorting by Last Name and Date .
• If sorted ascending by Date , links should indicate sorting ascending by Last Name and descending by Date .
• If sorted descending by Date , links should indicate sorting ascending by Last Name and ascending by Date .

The method uses LINQ to Entities to indicate the column to be sorted. In the code before the switch, a variable is created, then this variable is changed under the switch conditions, and the ToList method is called before closing the switch. When you create and modify IQueryable variables, no queries are made to the database. The query is not executed until you convert your IQueryable to a collection using a call like ToList. This method returns a single request, which is executed on return View.

Adding Link Headers to Student Index

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

 
     
     
        @Html.ActionLink("Last Name", "Index", new { sortOrder=ViewBag.NameSortParm }) 
     
     
        First Name 
     
     
        @Html.ActionLink("Enrollment Date", "Index", new { sortOrder=ViewBag.DateSortParm }) 
     

This code uses the information in the ViewBag properties to populate links with appropriate query values. Run the project and click on the headers to make sure the sorting works.

Adding a Search

To add filtering, you must add a text field and a submit button to the view and make the appropriate changes to the code of the Index method.

Changing the Index Method Code

In Controllers \ StudentController.cs, replace the Index method 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()); 
}

We added the searchString parameter to the Index method, a slogan in the LINQ statement, with which only students whose first or last name contains a search string are selected. The search string is obtained from a text field that you later add to the view. The code that adds the where clause to the query is executed only if a value is specified for the search:

if (!String.IsNullOrEmpty(searchString)) 
{ 
    students = students.Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper()) 
                           || s.FirstMidName.ToUpper().Contains(searchString.ToUpper())); 
}

The Contains .NET Framework implementation returns all records when you pass an empty string to it, but the Entity Framework provider for SQL Server Compact 4.0 returns an empty set for an empty string. In addition, the implementation in the .NET Framework performs case-insensitive comparisons, unlike Entity Framework SQL Server providers, which perform case-insensitive comparisons by default.

Adding Search to a View

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

@using (Html.BeginForm()) 
{ 
    
 
        Find by name: @Html.TextBox("SearchString")   
        
 
}

Run the project, enter something in the search bar and click on the Search button to verify that the filtering works.

Adding pagination

To do this, you must first install the PagedList NuGet package, then make changes to the Index method and add page links to the view.

Install the NuGet Package

The PagedList NuGet package sets the type of the PagedList collection. When you add query results to a collection of this type, you are provided with a set of properties and methods to ensure pagination of the results.

In Visual Studio, select the project. Then click on the Tools menu item Library Package Manager and then Add Library Package Reference .

In the Add Library Package Reference, click the Online tab on the left and enter "pagedlist" in the search bar. As soon as the PagedList package appears, click Install .

Adding pagination functionality to the Index method

In Controllers \ StudentController.cs add using PagedList:

using PagedList;

Replace the index method 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)); 
        }

The page parameter has been added that carries information about the parameter that is currently being sorted, and the parameter in the method signature:

public ViewResult Index (string sortOrder, string currentFilter, string searchString, int? Page)

When the page is first called (or if the user didn’t click to one of the page links), the page variable is null. After clicking this variable, the page number is placed.

The ViewBag property passes to the view the current sorting parameter to save it when going to other pages:

ViewBag.CurrentSort = sortOrder;

The second property of the ViewBag passes the filter string to the view so that when you go to another page, the entered search string is not lost and the filter settings are saved. In addition, if the search bar changes in case of switching to another results page, the page number should be dropped to 1, since the new filtering provides a new data set.

            if (Request.HttpMethod == "GET") 
            { 
                searchString = currentFilter; 
            } 
            else 
            { 
                page = 1; 
            } 
            ViewBag.CurrentFilter = searchString;

At the end of the method, the query is converted instead of List to a PagedList, after which it can be passed to a view that supports pagination of results.

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

The ToPagedList method passes the value of the page index, which is 0, in contrast to the page number, which is 1. Therefore, the code extracts 1 from the page number to get the page index values ​​(two question marks indicate the operator that defines the default value for the type nullable, thus, the expression (page ?? 1) returns page if it has a value, or 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 it is null)

Adding page links to a view

In Views \ Student \ Index . cshtml replace the source code with:

@model PagedList.IPagedList 
@{ 
    ViewBag.Title = "Students"; 
} 
Students
 
    @Html.ActionLink("Create New", "Create") 
 
@using (Html.BeginForm()) 
{ 
    
 
        Find by name: @Html.TextBox("SearchString", ViewBag.CurrentFilter as string)   
        
 
} 
 
@foreach (var item in Model) { 
     
} 
 
        @Html.ActionLink("Last Name", "Index", new { sortOrder=ViewBag.NameSortParm, currentFilter=ViewBag.CurrentFilter }) 
    
 
        First Name 
    
 
        @Html.ActionLink("Enrollment Date", "Index", new { sortOrder = ViewBag.DateSortParm, currentFilter = ViewBag.CurrentFilter }) 
    
 
            @Html.ActionLink("Edit", "Edit", new { id=item.StudentID }) | 
            @Html.ActionLink("Details", "Details", new { id=item.StudentID }) | 
            @Html.ActionLink("Delete", "Delete", new { id=item.StudentID }) 
        
 
            @Html.DisplayFor(modelItem => item.LastName) 
        
 
            @Html.DisplayFor(modelItem => item.FirstMidName) 
        
 
            @Html.DisplayFor(modelItem => item.EnrollmentDate) 
        
 
    Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) 
    of @Model.PageCount 
    @if (Model.HasPreviousPage) 
    { 
        @Html.ActionLink("<<", "Index", new { page = 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  }) 
        @Html.Raw(" "); 
        @Html.ActionLink("< Prev", "Index", new { page = Model.PageNumber - 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  }) 
    } 
    else 
    { 
        @:<< 
        @Html.Raw(" "); 
        @:< Prev 
    } 
    @if (Model.HasNextPage) 
    { 
        @Html.ActionLink("Next >", "Index", new { page = Model.PageNumber + 1, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  }) 
        @Html.Raw(" "); 
        @Html.ActionLink(">>", "Index", new { page = Model.PageCount, sortOrder = ViewBag.CurrentSort, currentFilter=ViewBag.CurrentFilter  }) 
    } 
    else 
    { 
        @:Next > 
        @Html.Raw(" ") 
        @:>> 
    } 

The @model statement shows that the view accepts an object of type PagedList instead of an object of type List.

The text string is initialized with the current search string so that the user can navigate from page to page without losing the search string:

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

Links in the column headers use the query string to pass the current search string to controller so that the user can sort the results returned by the filter mechanism:

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

In the "footer" of the page is an entry showing the page number and navigation links:

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

<< - link to the first page, < Prev - link to the previous page, and so on. If the user is on page number 1, links to previous pages are not available, and also for the last page. Each link to the page passes the number of the selected page and the current data on filtering and sorting to the controller in the query line, thus allowing you to manage this data in the process of pagination.

If there are no results, "Page 0 of 0" is displayed.

Run the project.



Click links to pages in various sorting modes and enter some search string to make sure everything works in conjunction.

Creating a statistics page

On the About page, we will show how many students have enrolled at each recording date. This requires grouping and small calculations, for which we must do the following:

• Create a class with a view model for the data that we will pass to the view
• Change the code for the About method in the Home controller.
• Change the code for the About view.

Creating a Presentation Model

Create a ViewModels folder and create an EnrollmentDateGroup file in it . cs with the following content:

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 necessary using:

using ContosoUniversity.DAL; 
using ContosoUniversity.Models; 
using ContosoUniversity.ViewModels;

Add a variable with the database context:

private SchoolContext db = new SchoolContext ();

Replace the code for the About method with:

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 statements group student entities by recording date, then calculate the number of entities in each group and store the result in an EnrollmentDateGroup.

Changing the About view code

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

@model IEnumerable 
@{ 
    ViewBag.Title = "Student Body Statistics"; 
} 
Student Body Statistics
 
 
@foreach (var item in Model) { 
     
} 
 
            Enrollment Date 
        
 
            Students 
        
 
            @String.Format("{0:d}", item.EnrollmentDate) 
        
 
            @item.StudentCount 
        

Run the project.



Thank you for your help in translating Alexander Belotserkovsky ( ahriman ).