Razor Pages with EF Core in ASP.NET Core – Sort, Filter, Paging – 3 of 8

[ad_1]

By Tom Dykstra, Rick Anderson, and Jon P Smith

The Contoso University web app demonstrates how to create Razor Pages web apps using EF Core and Visual Studio. For information about the tutorial series, see the first tutorial.

In this tutorial, sorting, filtering, grouping, and paging, functionality is added.

The following illustration shows a completed page. The column headings are clickable links to sort the column. Clicking a column heading repeatedly switches between ascending and descending sort order.

Students index page

If you run into problems you can’t solve, download the completed app.

Add sorting to the Index page

Add strings to the Students/Index.cshtml.cs PageModel to contain the sorting parameters:

public class IndexModel : PageModel
{
    private readonly SchoolContext _context;

    public IndexModel(SchoolContext context)
    
        _context = context;
    

    public string NameSort  get; set; 
    public string DateSort  get; set; 
    public string CurrentFilter  get; set; 
    public string CurrentSort  get; set; 

Update the Students/Index.cshtml.cs OnGetAsync with the following code:

public async Task OnGetAsync(string sortOrder)

    NameSort = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
    DateSort = sortOrder == "Date" ? "date_desc" : "Date";

    IQueryable<Student> studentIQ = from s in _context.Student
                                    select s;

    switch (sortOrder)
    
        case "name_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.LastName);
            break;
        case "Date":
            studentIQ = studentIQ.OrderBy(s => s.EnrollmentDate);
            break;
        case "date_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.EnrollmentDate);
            break;
        default:
            studentIQ = studentIQ.OrderBy(s => s.LastName);
            break;
    

    Student = await studentIQ.AsNoTracking().ToListAsync();

The preceding code receives a sortOrder parameter from the query string in the URL. The URL (including the query string) is generated by the Anchor Tag Helper

The sortOrder parameter is either “Name” or “Date.” The sortOrder parameter is optionally followed by “_desc” to specify descending order. The default sort order is ascending.

When the Index page is requested from the Students link, there’s no query string. The students are displayed in ascending order by last name. Ascending order by last name is the default (fall-through case) in the switch statement. When the user clicks a column heading link, the appropriate sortOrder value is provided in the query string value.

NameSort and DateSort are used by the Razor Page to configure the column heading hyperlinks with the appropriate query string values:

public async Task OnGetAsync(string sortOrder)

    NameSort = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
    DateSort = sortOrder == "Date" ? "date_desc" : "Date";

    IQueryable<Student> studentIQ = from s in _context.Student
                                    select s;

    switch (sortOrder)
    
        case "name_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.LastName);
            break;
        case "Date":
            studentIQ = studentIQ.OrderBy(s => s.EnrollmentDate);
            break;
        case "date_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.EnrollmentDate);
            break;
        default:
            studentIQ = studentIQ.OrderBy(s => s.LastName);
            break;
    

    Student = await studentIQ.AsNoTracking().ToListAsync();

The following code contains the C# conditional ?: operator:

NameSort = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
DateSort = sortOrder == "Date" ? "date_desc" : "Date";

The first line specifies that when sortOrder is null or empty, NameSort is set to “name_desc.” If sortOrder is not null or empty, NameSort is set to an empty string.

The ?: operator is also known as the ternary operator.

These two statements enable the page to set the column heading hyperlinks as follows:

Current sort order Last Name Hyperlink Date Hyperlink
Last Name ascending descending ascending
Last Name descending ascending ascending
Date ascending ascending descending
Date descending ascending ascending

The method uses LINQ to Entities to specify the column to sort by. The code initializes an IQueryable<Student> before the switch statement, and modifies it in the switch statement:

public async Task OnGetAsync(string sortOrder)

    NameSort = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
    DateSort = sortOrder == "Date" ? "date_desc" : "Date";

    IQueryable<Student> studentIQ = from s in _context.Student
                                    select s;

    switch (sortOrder)
    
        case "name_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.LastName);
            break;
        case "Date":
            studentIQ = studentIQ.OrderBy(s => s.EnrollmentDate);
            break;
        case "date_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.EnrollmentDate);
            break;
        default:
            studentIQ = studentIQ.OrderBy(s => s.LastName);
            break;
    

    Student = await studentIQ.AsNoTracking().ToListAsync();

When anIQueryable is created or modified, no query is sent to the database. The query isn’t executed until the IQueryable object is converted into a collection. IQueryable are converted to a collection by calling a method such as ToListAsync. Therefore, the IQueryable code results in a single query that’s not executed until the following statement:

Student = await studentIQ.AsNoTracking().ToListAsync();

OnGetAsync could get verbose with a large number of sortable columns.

Replace the code in Students/Index.cshtml, with the following highlighted code:

@page
@model ContosoUniversity.Pages.Students.IndexModel

@
    ViewData["Title"] = "Index";


<h2>Index</h2>
<p>
    <a asp-page="Create">Create New</a>
</p>

<table class="table">
    <thead>
        <tr>
            <th>
                <a asp-page="./Index" asp-route-sortOrder="@Model.NameSort">
                    @Html.DisplayNameFor(model => model.Student[0].LastName)
                </a>
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Student[0].FirstMidName)
            </th>
            <th>
                <a asp-page="./Index" asp-route-sortOrder="@Model.DateSort">
                    @Html.DisplayNameFor(model => model.Student[0].EnrollmentDate)
                </a>
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.Student)
        
                    <a asp-page="./Details" asp-route-id="@item.ID">Details</a> 
    </tbody>
</table>

The preceding code:

  • Adds hyperlinks to the LastName and EnrollmentDate column headings.
  • Uses the information in NameSort and DateSort to set up hyperlinks with the current sort order values.

To verify that sorting works:

  • Run the app and select the Students tab.
  • Click Last Name.
  • Click Enrollment Date.

To get a better understanding of the code:

  • In Student/Index.cshtml.cs, set a breakpoint on switch (sortOrder).
  • Add a watch for NameSort and DateSort.
  • In Student/Index.cshtml, set a breakpoint on @Html.DisplayNameFor(model => model.Student[0].LastName).

Step through the debugger.

Add a Search Box to the Students Index page

To add filtering to the Students Index page:

  • A text box and a submit button is added to the Razor Page. The text box supplies a search string on the first or last name.
  • The page model is updated to use the text box value.

Add filtering functionality to the Index method

Update the Students/Index.cshtml.cs OnGetAsync with the following code:

public async Task OnGetAsync(string sortOrder, string searchString)

    NameSort = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
    DateSort = sortOrder == "Date" ? "date_desc" : "Date";
    CurrentFilter = searchString;

    IQueryable<Student> studentIQ = from s in _context.Student
                                    select s;
    if (!String.IsNullOrEmpty(searchString))
     s.FirstMidName.Contains(searchString));
    

    switch (sortOrder)
    
        case "name_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.LastName);
            break;
        case "Date":
            studentIQ = studentIQ.OrderBy(s => s.EnrollmentDate);
            break;
        case "date_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.EnrollmentDate);
            break;
        default:
            studentIQ = studentIQ.OrderBy(s => s.LastName);
            break;
    

    Student = await studentIQ.AsNoTracking().ToListAsync();

The preceding code:

  • Adds the searchString parameter to the OnGetAsync method. The search string value is received from a text box that’s added in the next section.
  • Added to the LINQ statement a Where clause. The Where clause selects only students whose first name or last name contains the search string. The LINQ statement is executed only if there’s a value to search for.

Note: The preceding code calls the Where method on an IQueryable object, and the filter is processed on the server. In some scenarios, the app might be calling the Where method as an extension method on an in-memory collection. For example, suppose _context.Students changes from EF Core DbSet to a repository method that returns an IEnumerable collection. The result would normally be the same but in some cases may be different.

For example, the .NET Framework implementation of Contains performs a case-sensitive comparison by default. In SQL Server, Contains case-sensitivity is determined by the collation setting of the SQL Server instance. SQL Server defaults to case-insensitive. ToUpper could be called to make the test explicitly case-insensitive:

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

The preceding code would ensure that results are case-insensitive if the code changes to use IEnumerable. When Contains is called on an IEnumerable collection, the .NET Core implementation is used. When Contains is called on an IQueryable object, the database implementation is used. Returning an IEnumerable from a repository can have a significant performance penality:

  1. All the rows are returned from the DB server.
  2. The filter is applied to all the returned rows in the application.

There’s a performance penalty for calling ToUpper. The ToUpper code adds a function in the WHERE clause of the TSQL SELECT statement. The added function prevents the optimizer from using an index. Given that SQL is installed as case-insensitive, it’s best to avoid the ToUpper call when it’s not needed.

Add a Search Box to the Student Index page

In Pages/Students/Index.cshtml, add the following highlighted code to create a Search button and assorted chrome.

@page
@model ContosoUniversity.Pages.Students.IndexModel

@
    ViewData["Title"] = "Index";


<h2>Index</h2>

<p>
    <a asp-page="Create">Create New</a>
</p>

<form asp-page="./Index" method="get">
    <div class="form-actions no-color">
        <p>
            Find by name:
            <input type="text" name="SearchString" value="@Model.CurrentFilter" />
            <input type="submit" value="Search" class="btn btn-default" /> |
            <a asp-page="./Index">Back to full List</a>
        </p>
    </div>
</form>

<table class="table">

The preceding code uses the <form> tag helper to add the search text box and button. By default, the <form> tag helper submits form data with a POST. With POST, the parameters are passed in the HTTP message body and not in the URL. When HTTP GET is used, the form data is passed in the URL as query strings. Passing the data with query strings enables users to bookmark the URL. The W3C guidelines recommend that GET should be used when the action doesn’t result in an update.

Test the app:

  • Select the Students tab and enter a search string.
  • Select Search.

Notice that the URL contains the search string.

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

If the page is bookmarked, the bookmark contains the URL to the page and the SearchString query string. The method="get" in the form tag is what caused the query string to be generated.

Currently, when a column heading sort link is selected, the filter value from the Search box is lost. The lost filter value is fixed in the next section.

Add paging functionality to the Students Index page

In this section, a PaginatedList class is created to support paging. The PaginatedList class uses Skip and Take statements to filter data on the server instead of retrieving all rows of the table. The following illustration shows the paging buttons.

Students index page with paging links

In the project folder, create PaginatedList.cs with the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace ContosoUniversity

    public class PaginatedList<T> : List<T>
    
        public int PageIndex  get; private set; 
        public int TotalPages  get; private 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 > 1);
            
        

        public bool HasNextPage
        
            get
            
                return (PageIndex < TotalPages);
            
        

        public static async Task<PaginatedList<T>> CreateAsync(
            IQueryable<T> source, int pageIndex, int pageSize)
        
            var count = await source.CountAsync();
            var items = await source.Skip(
                (pageIndex - 1) * pageSize)
                .Take(pageSize).ToListAsync();
            return new PaginatedList<T>(items, count, pageIndex, pageSize);
        
    

The CreateAsync method in the preceding 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 returns a List containing only the requested page. The properties HasPreviousPage and HasNextPage are used to enable or disable Previous and Next paging buttons.

The CreateAsync method is used to create the PaginatedList<T>. A constructor can’t create the PaginatedList<T> object, constructors can’t run asynchronous code.

Add paging functionality to the Index method

In Students/Index.cshtml.cs, update the type of Student from IList<Student> to PaginatedList<Student>:

public PaginatedList<Student> Student  get; set; 

Update the Students/Index.cshtml.cs OnGetAsync with the following code:

public async Task OnGetAsync(string sortOrder,
    string currentFilter, string searchString, int? pageIndex)

    CurrentSort = sortOrder;
    NameSort = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
    DateSort = sortOrder == "Date" ? "date_desc" : "Date";
    if (searchString != null)
    
        pageIndex = 1;
    
    else
    
        searchString = currentFilter;
    

    CurrentFilter = searchString;

    IQueryable<Student> studentIQ = from s in _context.Student
                                    select s;
    if (!String.IsNullOrEmpty(searchString))
     s.FirstMidName.Contains(searchString));
    
    switch (sortOrder)
    
        case "name_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.LastName);
            break;
        case "Date":
            studentIQ = studentIQ.OrderBy(s => s.EnrollmentDate);
            break;
        case "date_desc":
            studentIQ = studentIQ.OrderByDescending(s => s.EnrollmentDate);
            break;
        default:
            studentIQ = studentIQ.OrderBy(s => s.LastName);
            break;
    

    int pageSize = 3;
    Student = await PaginatedList<Student>.CreateAsync(
        studentIQ.AsNoTracking(), pageIndex ?? 1, pageSize);

The preceding code adds the page index, the current sortOrder, and the currentFilter to the method signature.

public async Task OnGetAsync(string sortOrder,
    string currentFilter, string searchString, int? pageIndex)

All the parameters are null when:

  • The page is called from the Students link.
  • The user hasn’t clicked a paging or sorting link.

When a paging link is clicked, the page index variable contains the page number to display.

CurrentSort provides the Razor Page with the current sort order. The current sort order must be included in the paging links to keep the sort order while paging.

CurrentFilter provides the Razor Page with the current filter string. The CurrentFilter value:

  • Must be included in the paging links in order to maintain the filter settings during paging.
  • Must be restored to the text box when the page is redisplayed.

If the search string is changed while paging, the page is reset to 1. The page has to be reset to 1 because the new filter can result in different data to display. When a search value is entered and Submit is selected:

  • The search string is changed.
  • The searchString parameter isn’t null.
if (searchString != null)

    pageIndex = 1;

else

    searchString = currentFilter;

The PaginatedList.CreateAsync method converts the student query to a single page of students in a collection type that supports paging. That single page of students is passed to the Razor Page.

Student = await PaginatedList<Student>.CreateAsync(
    studentIQ.AsNoTracking(), pageIndex ?? 1, pageSize);

The two question marks in PaginatedList.CreateAsync represent the [null-coalescing operator](https://docs.microsoft.com/ dotnet/csharp/language-reference/operators/null-conditional-operator). The null-coalescing operator defines a default value for a nullable type. The expression (pageIndex ?? 1) means return the value of pageIndex if it has a value. If pageIndex doesn’t have a value, return 1.

Update the markup in Students/Index.cshtml. The changes are highlighted:

@page
@model ContosoUniversity.Pages.Students.IndexModel

@
    ViewData["Title"] = "Index";


<h2>Index</h2>

<p>
    <a asp-page="Create">Create New</a>
</p>

<form asp-page="./Index" method="get">
    <div class="form-actions no-color">
        <p>
            Find by name: <input type="text" name="SearchString" value="@Model.CurrentFilter" />
            <input type="submit" value="Search" class="btn btn-default" /> |
            <a asp-page="./Index">Back to full List</a>
        </p>
    </div>
</form>

<table class="table">
    <thead>
        <tr>
            <th>
                <a asp-page="./Index" asp-route-sortOrder="@Model.NameSort"
                   asp-route-currentFilter="@Model.CurrentFilter">
                    @Html.DisplayNameFor(model => model.Student[0].LastName)
                </a>
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Student[0].FirstMidName)
            </th>
            <th>
                <a asp-page="./Index" asp-route-sortOrder="@Model.DateSort"
                   asp-route-currentFilter="@Model.CurrentFilter">
                    @Html.DisplayNameFor(model => model.Student[0].EnrollmentDate)
                </a>
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.Student)
        
                    <a asp-page="./Details" asp-route-id="@item.ID">Details</a> 
    </tbody>
</table>

@
    var prevDisabled = !Model.Student.HasPreviousPage ? "disabled" : "";
    var nextDisabled = !Model.Student.HasNextPage ? "disabled" : "";


<a asp-page="./Index"
   asp-route-sortOrder="@Model.CurrentSort"
   asp-route-pageIndex="@(Model.Student.PageIndex - 1)"
   asp-route-currentFilter="@Model.CurrentFilter"
   class="btn btn-default @prevDisabled">
    Previous
</a>
<a asp-page="./Index"
   asp-route-sortOrder="@Model.CurrentSort"
   asp-route-pageIndex="@(Model.Student.PageIndex + 1)"
   asp-route-currentFilter="@Model.CurrentFilter"
   class="btn btn-default @nextDisabled">
    Next
</a>

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

<a asp-page="./Index" asp-route-sortOrder="@Model.NameSort"
   asp-route-currentFilter="@Model.CurrentFilter">
    @Html.DisplayNameFor(model => model.Student[0].LastName)
</a>

The paging buttons are displayed by tag helpers:


<a asp-page="./Index"
   asp-route-sortOrder="@Model.CurrentSort"
   asp-route-pageIndex="@(Model.Student.PageIndex - 1)"
   asp-route-currentFilter="@Model.CurrentFilter"
   class="btn btn-default @prevDisabled">
    Previous
</a>
<a asp-page="./Index"
   asp-route-sortOrder="@Model.CurrentSort"
   asp-route-pageIndex="@(Model.Student.PageIndex + 1)"
   asp-route-currentFilter="@Model.CurrentFilter"
   class="btn btn-default @nextDisabled">
    Next
</a>

Run the app and navigate to the students page.

  • To make sure paging works, click the paging links in different sort orders.
  • To verify that paging works correctly with sorting and filtering, enter a search string and try paging.

students index page with paging links

To get a better understanding of the code:

  • In Student/Index.cshtml.cs, set a breakpoint on switch (sortOrder).
  • Add a watch for NameSort, DateSort, CurrentSort, and Model.Student.PageIndex.
  • In Student/Index.cshtml, set a breakpoint on @Html.DisplayNameFor(model => model.Student[0].LastName).

Step through the debugger.

Update the About page to show student statistics

In this step, Pages/About.cshtml is updated to display how many students have enrolled for each enrollment date. The update uses grouping and includes the following steps:

  • Create a view model for the data used by the About Page.
  • Update the About page to use the view model.

Create the view model

Create a SchoolViewModels folder in the Models folder.

In the SchoolViewModels folder, add a EnrollmentDateGroup.cs with the following code:

using System;
using System.ComponentModel.DataAnnotations;

namespace ContosoUniversity.Models.SchoolViewModels

    public class EnrollmentDateGroup
    
        [DataType(DataType.Date)]
        public DateTime? EnrollmentDate  get; set; 

        public int StudentCount  get; set; 
    

Update the About page model

Update the Pages/About.cshtml.cs file with the following code:

using ContosoUniversity.Models.SchoolViewModels;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using ContosoUniversity.Models;

namespace ContosoUniversity.Pages

    public class AboutModel : PageModel
    
        private readonly SchoolContext _context;

        public AboutModel(SchoolContext context)
        
            _context = context;
        

        public IList<EnrollmentDateGroup> Student  get; set; 

        public async Task OnGetAsync()
        
            IQueryable<EnrollmentDateGroup> data =
                from student in _context.Student
                group student by student.EnrollmentDate into dateGroup
                select new EnrollmentDateGroup()
                
                    EnrollmentDate = dateGroup.Key,
                    StudentCount = dateGroup.Count()
                ;

            Student = await data.AsNoTracking().ToListAsync();
        
    

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.

Note: The LINQ group command isn’t currently supported by EF Core. In the preceding code, all the student records are returned from SQL Server. The group command is applied in the Razor Pages app, not on the SQL Server. EF Core 2.1 will support this LINQ group operator, and the grouping occurs on the SQL Server. See Relational: Support translating GroupBy() to SQL. EF Core 2.1 will be released with .NET Core 2.1. For more information, see the .NET Core Roadmap.

Modify the About Razor Page

Replace the code in the Pages/About.cshtml file with the following code:

@page
@model ContosoUniversity.Pages.AboutModel

@
    ViewData["Title"] = "Student Body Statistics";


<h2>Student Body Statistics</h2>

<table>
    <tr>
        <th>
            Enrollment Date
        </th>
        <th>
            Students
        </th>
    </tr>

    @foreach (var item in Model.Student)
    
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.EnrollmentDate)
            </td>
            <td>
                @item.StudentCount
            </td>
        </tr>
    
</table>

Run the app and navigate to the About page. The count of students for each enrollment date is displayed in a table.

If you run into problems you can’t solve, download the completed app for this stage.

About page

Additional resources

In the next tutorial, the app uses migrations to update the data model.

[ad_2]

source_link
https://www.asp.net