Tutorial: Add sorting, filtering, and paging - ASP.Internet MVC with EF Core

  • Article
  • 14 minutes to read

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 uncomplicated group.

The following analogy shows what the page volition wait like when you're washed. The column headings are links that the user tin can click to sort by that cavalcade. Clicking a column heading repeatedly toggles between ascending and descending sort club.

Students index page

In this tutorial, you:

  • Add together column sort links
  • Add a Search box
  • Add paging to Students Index
  • Add paging to Index method
  • Add paging links
  • Create an Almost folio

Prerequisites

  • Implement CRUD Functionality

To add sorting to the Student Index page, you'll change the Alphabetize method of the Students controller and add code to the Student Index view.

Add together sorting Functionality to the Index method

In StudentsController.cs, supercede the Index method with the following lawmaking:

              public async Task<IActionResult> Index(cord sortOrder) {     ViewData["NameSortParm"] = Cord.IsNullOrEmpty(sortOrder) ? "name_desc" : "";     ViewData["DateSortParm"] = sortOrder == "Engagement" ? "date_desc" : "Date";     var students = from south in _context.Students                    select s;     switch (sortOrder)     {         instance "name_desc":             students = students.OrderByDescending(southward => s.LastName);             break;         instance "Appointment":             students = students.OrderBy(s => s.EnrollmentDate);             break;         case "date_desc":             students = students.OrderByDescending(s => s.EnrollmentDate);             break;         default:             students = students.OrderBy(due south => s.LastName);             break;     }     return View(wait 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 equally a parameter to the activeness method. The parameter volition be a string that'southward either "Name" or "Date", optionally followed by an underscore and the cord "desc" to specify descending order. The default sort club is ascending.

The outset fourth dimension the Index page is requested, there'due south no query string. The students are displayed in ascending lodge by last proper noun, which is the default as established by the autumn-through case in the switch statement. When the user clicks a column heading hyperlink, the advisable sortOrder value is provided in the query string.

The ii ViewData elements (NameSortParm and DateSortParm) are used by the view to configure the cavalcade heading hyperlinks with the advisable query string values.

              public async Chore<IActionResult> Alphabetize(cord 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 => southward.LastName);             interruption;         case "Date":             students = students.OrderBy(s => south.EnrollmentDate);             break;         example "date_desc":             students = students.OrderByDescending(s => due south.EnrollmentDate);             break;         default:             students = students.OrderBy(south => s.LastName);             pause;     }     return View(expect students.AsNoTracking().ToListAsync()); }

These are ternary statements. The first 1 specifies that if the sortOrder parameter is nada 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 Concluding Name Hyperlink Date Hyperlink
Final Name ascending descending ascending
Last Proper name descending ascending ascending
Appointment 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 argument, 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 isn't executed until you lot convert the IQueryable object into a collection by calling a method such equally ToListAsync. Therefore, this code results in a single query that's not executed until the return View statement.

This code could get verbose with a large number of columns. The concluding tutorial in this series shows how to write code that lets y'all laissez passer the name of the OrderBy column in a string variable.

Replace the code in Views/Students/Index.cshtml, with the following code to add column heading hyperlinks. The changed lines are highlighted.

              @model IEnumerable<ContosoUniversity.Models.Student>  @{     ViewData["Championship"] = "Index"; }  <h2>Alphabetize</h2>  <p>     <a asp-action="Create">Create New</a> </p> <table class="table">     <thead>         <tr>                 <thursday>                     <a asp-action="Index" asp-route-sortOrder="@ViewData["NameSortParm"]">@Html.DisplayNameFor(model => model.LastName)</a>                 </th>                 <th>                     @Html.DisplayNameFor(model => model.FirstMidName)                 </thursday>                 <thursday>                     <a asp-action="Alphabetize" asp-route-sortOrder="@ViewData["DateSortParm"]">@Html.DisplayNameFor(model => model.EnrollmentDate)</a>                 </th>             <thursday></th>         </tr>     </thead>     <tbody> @foreach (var item in Model) {         <tr>             <td>                 @Html.DisplayFor(modelItem => detail.LastName)             </td>             <td>                 @Html.DisplayFor(modelItem => particular.FirstMidName)             </td>             <td>                 @Html.DisplayFor(modelItem => detail.EnrollmentDate)             </td>             <td>                 <a asp-action="Edit" asp-route-id="@particular.ID">Edit</a> |                 <a asp-action="Details" asp-route-id="@item.ID">Details</a> |                 <a asp-activity="Delete" asp-road-id="@particular.ID">Delete</a>             </td>         </tr> }     </tbody> </table>

This code uses the information in ViewData properties to ready hyperlinks with the appropriate query string values.

Run the app, select the Students tab, and click the Last Name and Enrollment Date cavalcade headings to verify that sorting works.

Students index page in name order

To add filtering to the Students Index page, you'll add together a text box and a submit push to the view and make corresponding changes in the Index method. The text box will allow you enter a string to search for in the outset name and concluding 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 Chore<IActionResult> Alphabetize(string sortOrder, string searchString) {     ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";     ViewData["DateSortParm"] = sortOrder == "Date" ? "date_desc" : "Date";     ViewData["CurrentFilter"] = searchString;      var students = from south in _context.Students                    select s;     if (!String.IsNullOrEmpty(searchString))     {         students = students.Where(south => south.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 => south.EnrollmentDate);             break;         case "date_desc":             students = students.OrderByDescending(due south => s.EnrollmentDate);             break;         default:             students = students.OrderBy(s => southward.LastName);             break;     }     return View(wait students.AsNoTracking().ToListAsync()); }

You've added a searchString parameter to the Index method. The search string value is received from a text box that yous'll add to the Index view. You've likewise added to the LINQ argument a where clause that selects just students whose first proper noun 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-retentivity collection. (For example, suppose you change the reference to _context.Students then that instead of an EF DbSet it references a repository method that returns an IEnumerable collection.) The consequence would usually be the same but in some cases may be different.

For example, the .NET Framework implementation of the Contains method performs a case-sensitive comparing past default, but in SQL Server this is determined by the collation setting of the SQL Server instance. That setting defaults to case-insensitive. Y'all could phone call the ToUpper method to make the test explicitly case-insensitive: Where(due south => s.LastName.ToUpper().Contains(searchString.ToUpper()). That would ensure that results stay the same if you change the lawmaking later to use a repository which returns an IEnumerable collection instead of an IQueryable object. (When you telephone call the Contains method on an IEnumerable collection, you become the .Internet Framework implementation; when y'all call it on an IQueryable object, you lot get the database provider implementation.) However, there'south a performance punishment for this solution. The ToUpper code would put a function in the WHERE clause of the TSQL SELECT statement. That would forbid the optimizer from using an index. Given that SQL is by and large installed as example-insensitive, it's best to avoid the ToUpper code until you migrate to a instance-sensitive data store.

Add a Search Box to the Student Index View

In Views/Student/Index.cshtml, add the highlighted code immediately before the opening tabular array tag in order to create a caption, a text box, and a Search push.

              <p>     <a asp-action="Create">Create New</a> </p>  <form asp-activeness="Index" method="get">     <div class="form-actions no-color">         <p>             Detect 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 Total List</a>         </p>     </div> </class>  <tabular array class="table">

This code uses the <form> tag helper to add the search text box and button. By default, the <course> tag helper submits course data with a Mail service, which ways that parameters are passed in the HTTP bulletin body and non in the URL as query strings. When you specify HTTP GET, the grade 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 doesn't result in an update.

Run the app, select the Students tab, enter a search cord, and click Search to verify that filtering is working.

Students index page with filtering

Notice that the URL contains the search cord.

              http://localhost:5813/Students?SearchString=an

If you bookmark this page, you'll go the filtered listing when you employ the bookmark. Calculation method="go" to the course tag is what acquired the query string to exist generated.

At this stage, if you click a column heading sort link you'll lose the filter value that yous entered in the Search box. You'll fix that in the adjacent section.

Add paging to Students Index

To add paging to the Students Alphabetize page, you'll create a PaginatedList grade that uses Skip and Take statements to filter information on the server instead of ever retrieving all rows of the table. Then you'll make additional changes in the Index method and add together paging buttons to the Index view. The following illustration shows the paging buttons.

Students index page with paging links

In the projection folder, create PaginatedList.cs, and then replace the template code with the following code.

              using Arrangement; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Microsoft.EntityFrameworkCore;  namespace ContosoUniversity {     public form PaginatedList<T> : List<T>     {         public int PageIndex { get; private set; }         public int TotalPages { get; individual 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 > ane);             }         }          public bool HasNextPage         {             get             {                 return (PageIndex < TotalPages);             }         }          public static async Task<PaginatedList<T>> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize)         {             var count = expect source.CountAsync();             var items = wait source.Skip((pageIndex - ane) * pageSize).Have(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 render a Listing containing simply the requested folio. The properties HasPreviousPage and HasNextPage tin exist used to enable or disable Previous and Side by side paging buttons.

A CreateAsync method is used instead of a constructor to create the PaginatedList<T> object considering constructors tin can't run asynchronous code.

Add paging to Index method

In StudentsController.cs, replace the Index method with the following code.

              public async Task<IActionResult> Index(     string sortOrder,     cord currentFilter,     string searchString,     int? pageNumber) {     ViewData["CurrentSort"] = sortOrder;     ViewData["NameSortParm"] = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";     ViewData["DateSortParm"] = sortOrder == "Engagement" ? "date_desc" : "Date";      if (searchString != null)     {         pageNumber = 1;     }     else     {         searchString = currentFilter;     }      ViewData["CurrentFilter"] = searchString;      var students = from s in _context.Students                    select s;     if (!String.IsNullOrEmpty(searchString))     {         students = students.Where(south => s.LastName.Contains(searchString)                                || due south.FirstMidName.Contains(searchString));     }     switch (sortOrder)     {         case "name_desc":             students = students.OrderByDescending(south => s.LastName);             break;         instance "Engagement":             students = students.OrderBy(due south => southward.EnrollmentDate);             break;         case "date_desc":             students = students.OrderByDescending(s => due south.EnrollmentDate);             break;         default:             students = students.OrderBy(s => s.LastName);             break;     }      int pageSize = 3;     return View(look PaginatedList<Student>.CreateAsync(students.AsNoTracking(), pageNumber ?? i, pageSize)); }

This code adds a page number parameter, a current sort order parameter, and a current filter parameter to the method signature.

              public async Job<IActionResult> Index(     string sortOrder,     string currentFilter,     cord searchString,     int? pageNumber)

The starting time fourth dimension the page is displayed, or if the user hasn't clicked a paging or sorting link, all the parameters volition exist aught. If a paging link is clicked, the page variable will incorporate the folio 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 gild the same while paging.

The ViewData element named CurrentFilter provides the view with the current filter string. This value must exist 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 inverse during paging, the page has to be reset to 1, considering the new filter can event in different data to display. The search cord is inverse when a value is entered in the text box and the Submit push is pressed. In that example, the searchString parameter isn't aught.

              if (searchString != null) {     pageNumber = 1; } else {     searchString = currentFilter; }

At the end of the Alphabetize method, the PaginatedList.CreateAsync method converts the student query to a unmarried page of students in a drove type that supports paging. That single page of students is then passed to the view.

              return View(await PaginatedList<Student>.CreateAsync(students.AsNoTracking(), pageNumber ?? 1, pageSize));

The PaginatedList.CreateAsync method takes a page number. The two question marks correspond the nada-coalescing operator. The zilch-coalescing operator defines a default value for a nullable type; the expression (pageNumber ?? one) means render the value of pageNumber if it has a value, or return 1 if pageNumber is null.

In Views/Students/Index.cshtml, supplant the existing lawmaking with the following code. The changes are highlighted.

              @model PaginatedList<ContosoUniversity.Models.Student>  @{     ViewData["Title"] = "Index"; }  <h2>Index</h2>  <p>     <a asp-action="Create">Create New</a> </p>  <class asp-action="Index" method="go">     <div class="class-actions no-color">         <p>             Detect by name: <input type="text" name="SearchString" value="@ViewData["CurrentFilter"]" />             <input blazon="submit" value="Search" class="btn btn-default" /> |             <a asp-action="Index">Dorsum to Full List</a>         </p>     </div> </form>  <table course="table">     <thead>         <tr>             <th>                 <a asp-activeness="Index" asp-route-sortOrder="@ViewData["NameSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Last Name</a>             </th>             <th>                 First Proper name             </th>             <th>                 <a asp-activity="Index" asp-route-sortOrder="@ViewData["DateSortParm"]" asp-route-currentFilter="@ViewData["CurrentFilter"]">Enrollment Date</a>             </th>             <thursday></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 => detail.EnrollmentDate)                 </td>                 <td>                     <a asp-activity="Edit" asp-road-id="@detail.ID">Edit</a> |                     <a asp-action="Details" asp-road-id="@item.ID">Details</a> |                     <a asp-activeness="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-road-pageNumber="@(Model.PageIndex - 1)"    asp-route-currentFilter="@ViewData["CurrentFilter"]"    class="btn btn-default @prevDisabled">     Previous </a> <a asp-activeness="Index"    asp-route-sortOrder="@ViewData["CurrentSort"]"    asp-route-pageNumber="@(Model.PageIndex + 1)"    asp-route-currentFilter="@ViewData["CurrentFilter"]"    class="btn btn-default @nextDisabled">     Adjacent </a>

The @model statement at the peak of the page specifies that the view now gets a PaginatedList<T> object instead of a Listing<T> object.

The column header links use the query string to pass the current search string to the controller so that the user tin sort within filter results:

              <a asp-action="Index" asp-route-sortOrder="@ViewData["DateSortParm"]" asp-road-currentFilter ="@ViewData["CurrentFilter"]">Enrollment Date</a>

The paging buttons are displayed by tag helpers:

              <a asp-action="Index"    asp-road-sortOrder="@ViewData["CurrentSort"]"    asp-road-pageNumber="@(Model.PageIndex - 1)"    asp-route-currentFilter="@ViewData["CurrentFilter"]"    course="btn btn-default @prevDisabled">    Previous </a>

Run the app and get to the Students folio.

Students index page with paging links

Click the paging links in different sort orders to make sure paging works. And so enter a search string and try paging again to verify that paging likewise works correctly with sorting and filtering.

Create an Well-nigh folio

For the Contoso University website's About page, you'll display how many students have enrolled for each enrollment engagement. This requires grouping and uncomplicated calculations on the groups. To accomplish this, you'll do the following:

  • Create a view model grade for the data that you need to pass to the view.
  • Create the Nigh method in the Home controller.
  • Create the About view.

Create the view model

Create a SchoolViewModels folder in the Models folder.

In the new folder, add a class file EnrollmentDateGroup.cs and replace the template code with the following code:

              using Organisation; 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

In HomeController.cs, add the post-obit using statements at the top of the file:

              using Microsoft.EntityFrameworkCore; using ContosoUniversity.Data; using ContosoUniversity.Models.SchoolViewModels; using Microsoft.Extensions.Logging;

Add a class variable for the database context immediately after the opening curly brace for the class, and go an instance of the context from ASP.Internet Cadre DI:

              public class HomeController : Controller {     private readonly ILogger<HomeController> _logger;     individual readonly SchoolContext _context;      public HomeController(ILogger<HomeController> logger, SchoolContext context)     {         _logger = logger;         _context = context;     }

Add together an About method with the following code:

              public async Chore<ActionResult> About() {     IQueryable<EnrollmentDateGroup> data =          from student in _context.Students         group pupil past student.EnrollmentDate into dateGroup         select new EnrollmentDateGroup()         {             EnrollmentDate = dateGroup.Cardinal,             StudentCount = dateGroup.Count()         };     return View(await data.AsNoTracking().ToListAsync()); }

The LINQ statement groups the educatee entities by enrollment date, calculates the number of entities in each group, and stores the results in a collection of EnrollmentDateGroup view model objects.

Create the About View

Add together a Views/Dwelling house/About.cshtml file with the following code:

              @model IEnumerable<ContosoUniversity.Models.SchoolViewModels.EnrollmentDateGroup>  @{     ViewData["Title"] = "Student Body Statistics"; }  <h2>Educatee Body Statistics</h2>  <table>     <tr>         <th>             Enrollment Date         </th>         <thursday>             Students         </th>     </tr>      @foreach (var detail in Model)     {         <tr>             <td>                 @Html.DisplayFor(modelItem => item.EnrollmentDate)             </td>             <td>                 @item.StudentCount             </td>         </tr>     } </table>

Run the app and go to the Nigh page. The count of students for each enrollment date is displayed in a tabular array.

Get the code

Download or view the completed awarding.

Next steps

In this tutorial, y'all:

  • Added column sort links
  • Added a Search box
  • Added paging to Students Index
  • Added paging to Index method
  • Added paging links
  • Created an About page

Advance to the side by side tutorial to learn how to handle information model changes past using migrations.