Training course. Sorting, filtering and paging with the Entity Framework in an ASP.NET MVC application
This is an extension of a series of articles devoted to developing using the Entity Framework and ASP.NET MVC 3. The first chapters can be found at the following links: Creating the Entity Framework data model for an ASP.NET MVC application and Implementing basic CRUD functionality with the Entity Framework in an ASP application .NET MVC .
In the previous lesson, we implemented pages for performing CRUD operations for Student entities. In this tutorial, we will add sorting, filtering, and pagination, as well as create a page that will have a simple grouping.
The following image shows the final page view. Column headings are links that implement the descending and ascending sorting.
')
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:
The method accepts sortOrder as a parameter from the query string in the URL, which is provided by ASP.NET as a parameter for the method. The parameter is a “Name” or “Date” string with (optionally) a subsequent space and a “desc” string to indicate what needs to be sorted in descending order.
When you first call the Index page, there is no query line, and students are displayed in ascending order of LastName, which is indicated as a default option in the switch. After the user clicks on the column header, the corresponding sortOrder value is added to the query string.
If you want, you can configure the column heading hyperlinks with the appropriate query string values:
This is a ternary statement. The first one states that if sortOrder is null or empty, 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:
In Views \ Student \ Index . cshtml replace the code for <tr> and <th> containers with the following:
This code uses the information in the ViewBag properties to populate the links with appropriate query values. Run the project and click on the headers to make sure the sorting works.
To add filtering, you must add a text box and a submit button to the view and make the appropriate changes in the code of the Index method.
Change method code Index
In Controllers \ StudentController.cs, replace the Index method code.
We have added a searchString parameter to the Index method, a slander in the LINQ statement, which selects only those students whose first or last name contains the search string. The search string is derived 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 the search value is set:
In Views \ Student \ Index . cshtml right before the opening table tag add a header, a text field and a Search button:
Run the project, enter something in the search bar and click on the Search button to make sure that the filtering works.
To do this, you must first install the PagedList NuGet package, then make changes to the Index method and add links to the page view.
The PagedList NuGet package sets the type of the PagedList collection. When you add query results to a collection of this type, you are presented with a set of properties and methods to ensure that the results are broken down into pages.
In Visual Studio, select the project. Then click on the Tools menu item Library Package Manager and then the 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 .
In Controllers \ StudentController.cs, add using PagedList:
using PagedList;
Replace the Index Method Code:
Added the page parameter, which carries information about the parameter that is currently being sorted, and a parameter in the method signature:
public ViewResult Index (string sortOrder, string currentFilter, string searchString, int? page)
When the page is first called up (or if the user has not clicked on one of the page links), the page variable is null. After clicking in this variable, the page number is placed.
The ViewBag property passes the current sorting parameter to the view for saving it when going to other pages:
ViewBag.CurrentSort = sortOrder;
The second ViewBag property passes the filtering string to the view so that when you go to another page, the entered search string is not lost and the filtering settings are saved. In addition, if the search string changes in the case of moving to another page of results, the page number should be dropped to 1, since the new filtering provides a new data set.
At the end of the method, the query is converted instead of the List into a PagedList, after which it can be passed into a view that supports splitting the results into pages.
The ToPagedList method passes the page index value, which is 0, unlike 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 defining the default value for the nullable type, thus, the expression (page ?? 1) returns the value of 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)
In Views \ Student \ Index . cshtml replace the source code with:
The @model statement indicates that the view takes an object of type PagedList as an input instead of an object of type List.
The text string is initialized with the current search string so that the user can move from page to page without losing the search string:
Find by name: Html .TextBox ("SearchString", ViewBag.CurrentFilter as string) & nbsp;
The links in the column headers use the query string to transfer the current search string to the 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 “basement” of the page there is an entry showing the page number and navigation links:
Page [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 transmits the number of the selected page and the current data on filtering and sorting to the controller in the query string, thus allowing you to manage this data during the pagination process.
If there are no results, the inscription "Page 0 of 0" is displayed.
Run the project.
Click links to pages in different sorting modes and enter any search string to make sure everything works in conjunction.
On the About page we will show how many students have signed up for each recording date. This requires grouping and small calculations, for which we must do the following:
Create a ViewModels folder and create an EnrollmentDateGroup file in it . cs with the following content:
In HomeController . cs add necessary using:
Add a variable with the database context:
private SchoolContext db = new SchoolContext ();
Replace the About method code with:
LINQ statements group students' entities by date of record, then calculate the number of entities in each group and save the result in an EnrollmentDateGroup.
Replace the code in the Views \ Home \ About.cshtml file with:
Run the project.
Thanks for the help in the translation of Alexander Belotserkovsky ( ahriman ).
In the previous lesson, we implemented pages for performing CRUD operations for Student entities. In this tutorial, we will add sorting, filtering, and pagination, as well as create a page that will have a simple grouping.
The following image shows the final page view. Column headings are links that implement the descending and ascending sorting.
')
Adding sorting 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 accepts sortOrder as a parameter from the query string in the URL, which is provided by ASP.NET as a parameter for the method. The parameter is a “Name” or “Date” string with (optionally) a subsequent space and a “desc” string to indicate what needs to be sorted in descending order.
When you first call the Index page, there is no query line, and students are displayed in ascending order of LastName, which is indicated as a default option in the switch. After the user clicks on the column header, the corresponding sortOrder value is added to the query string.
If you want, you 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, 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 it is sorted in ascending order by Last Name , the Last Name link should indicate a descending sort by Last Name and an Enrollment Date link for sorting ascending by Date, respectively.
- If it is sorted in descending order by Last Name , the links should indicate sorting in ascending order by both Last Name and Date .
- If sorted in ascending order by Date , the links should indicate sorting in ascending order by Last Name and descending order in Date .
- If sorted in descending order by Date , the links should point to sorting by ascending by Last Name and by increasing by Date .
Adding link headers to the Student Index
In Views \ Student \ Index . cshtml replace the code for <tr> and <th> containers with the following:
<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 populate the links with appropriate query values. Run the project and click on the headers to make sure the sorting works.
Add search
To add filtering, you must add a text box and a submit button to the view and make the appropriate changes in the code of the Index method.
Change method code Index
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 have added a searchString parameter to the Index method, a slander in the LINQ statement, which selects only those students whose first or last name contains the search string. The search string is derived 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 the search value is set:
if (!String.IsNullOrEmpty(searchString)) { students = students.Where(s => s.LastName.ToUpper().Contains(searchString.ToUpper()) || s.FirstMidName.ToUpper().Contains(searchString.ToUpper())); } The Contains implementation of the .NET Framework returns all the 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 the empty string. In addition, the implementation in the .NET Framework makes case-sensitive comparisons, unlike the SQL Server Entity Framework providers, which by default make case-insensitive comparisons.
Add search to view
In Views \ Student \ Index . cshtml right before the opening table tag add a header, a text field and a Search button:
@using (Html.BeginForm()) { <p> Find by name: @Html.TextBox("SearchString") <input type="submit" value="Search" /></p> } Run the project, enter something in the search bar and click on the Search button to make sure that the filtering works.
Add paging
To do this, you must first install the PagedList NuGet package, then make changes to the Index method and add links to the page view.
Install NuGet Package 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 presented with a set of properties and methods to ensure that the results are broken down into pages.
In Visual Studio, select the project. Then click on the Tools menu item Library Package Manager and then the 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 .
Add paging 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)); } Added the page parameter, which carries information about the parameter that is currently being sorted, and a parameter in the method signature:
public ViewResult Index (string sortOrder, string currentFilter, string searchString, int? page)
When the page is first called up (or if the user has not clicked on one of the page links), the page variable is null. After clicking in this variable, the page number is placed.
The ViewBag property passes the current sorting parameter to the view for saving it when going to other pages:
ViewBag.CurrentSort = sortOrder;
The second ViewBag property passes the filtering string to the view so that when you go to another page, the entered search string is not lost and the filtering settings are saved. In addition, if the search string changes in the case of moving to another page of results, 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 the List into a PagedList, after which it can be passed into a view that supports splitting the results into pages.
int pageSize = 3; int pageIndex = (page ?? 1) - 1; return View(students.ToPagedList(pageIndex, pageSize)); The ToPagedList method passes the page index value, which is 0, unlike 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 defining the default value for the nullable type, thus, the expression (page ?? 1) returns the value of 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 links to pages on the view
In Views \ Student \ Index . cshtml replace the source code with:
@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) <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 @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(" ") @:>> } </div> The @model statement indicates that the view takes an object of type PagedList as an input instead of an object of type List.
The text string is initialized with the current search string so that the user can move from page to page without losing the search string:
Find by name: Html .TextBox ("SearchString", ViewBag.CurrentFilter as string) & nbsp;
The links in the column headers use the query string to transfer the current search string to the 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 “basement” of the page there is an entry showing the page number and navigation links:
Page [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 transmits the number of the selected page and the current data on filtering and sorting to the controller in the query string, thus allowing you to manage this data during the pagination process.
If there are no results, the inscription "Page 0 of 0" is displayed.
Run the project.
Click links to pages in different sorting modes and enter any search string to make sure everything works in conjunction.
Creating a page with statistics
On the About page we will show how many students have signed up for 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 of the About method in the Home controller.
- Change the View code for About.
Creating a view 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; } } } Change Home Controller
In HomeController . cs add 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 About method code 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 students' entities by date of record, then calculate the number of entities in each group and save the result in an EnrollmentDateGroup.
Changing the View Code
Replace the code in the Views \ Home \ About.cshtml file with:
@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 project.
Thanks for the help in the translation of Alexander Belotserkovsky ( ahriman ).
Source: https://habr.com/ru/post/133845/
All Articles