MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging

The original tutorial series, produced by Tom Dykstra and Rick Anderson ( @RickAndMSFT ) was written using the C# language. My versions keep as close to the originals as possible, changing only the coding language. The narrative text is largely unchanged from the original and is used with permission from Microsoft.

This tutorial series teaches you how to create ASP.NET MVC 5 applications using the Entity Framework 6 and Visual Studio 2013 Express for Web. This tutorial uses the Code First workflow. For information about how to choose between Code First, Database First, and Model First, see Entity Framework Development Workflows.

The tutorial series comprises 12 sections in total. They are intended to be followed sequentially as each section builds on the knowledge imparted in the previous sections. Progress through the sections is reflected in a Visual Studio Express for Web project download that accompanies each section which features the web application that you build through the series.

Download the code

The code for this section is available here. Save the .zip file to a convenient location and then extract the contents. Make sure you have an edition of Visual Studio 2013 installed (Express for Web, Professional, Premium or Ultimate) and double click the .sln file. Once the project is opened in your IDE, press Shift+Ctrl+B to build the solution. This will ensure that all packages are restored from Nuget and may take a while depending on your Internet connection speed.

The navigation path through the series is as follows:

1. Creating an Entity Framework Data Model
2. Implementing Basic CRUD Functionality
3. Sorting, Filtering and Paging
4. Connection Resiliency and Command Interception
5. Code First Migrations and Deployment
6. Creating a More Complex Data Model
7. Reading Related Data
8. Updating Related Data
9. Async and Stored Procedures
10. Handling Concurrency
11. Implementing-Inheritance
12. Advanced Entity Framework Scenarios

Sorting, Filtering an Paging

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.

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

Add Sorting Functionality to the Index Method

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

Function Index(ByVal sortOrder As String) As ActionResult
    ViewBag.NameSortParm = If(String.IsNullOrEmpty(sortOrder), "name_desc", String.Empty)
    ViewBag.DateSortParm = If(sortOrder = "Date", "date_desc", "Date")
    Dim students = From s In db.Students Select s
    Select Case sortOrder
        Case "name_desc"
            students = students.OrderByDescending(Function(s) s.LastName)
        Case "Date"
            students = students.OrderBy(Function(s) s.EnrollmentDate)
        Case "date_desc"
            students = students.OrderByDescending(Function(s) s.EnrollmentDate)
        Case Else
            students = students.OrderBy(Function(s) s.LastName)
    End Select
    Return View(students.ToList())
End Function

This code receives a sortOrder parameter from the query string in the URL. The query string value 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 an underscore and the string "desc" to specify descending order. The default sort order is ascending.

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 Select Case 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 = If(String.IsNullOrEmpty(sortOrder), "name_desc", String.Empty)
ViewBag.DateSortParm = If(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. These two statements enable the view to set the column heading hyperlinks as follows:

The method uses LINQ to Entities to specify the column to sort by. The code creates an IQueryable variable before the Select Case statement, modifies it in the Select Case statement, and calls the ToList method after the Select Case 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.

As an alternative to writing different LINQ statements for each sort order, you can dynamically create a LINQ statement. For information about dynamic LINQ, see Dynamic LINQ.

Add Column Heading Hyperlinks to the Student Index View

In Views\Student\Index.vbhtml, replace the <tr> and <th> elements for the heading row with the highlighted code:

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.ActionLink("Last Name", "Index", New With {.sortOrder = ViewBag.NameSortParm})
        </th>
        <th>
            First Name
        </th>
        <th>
            @Html.ActionLink("Enrollment Date", "Index", New With {.sortOrder = ViewBag.DateSortParm})
        </th>
        <th></th>
    </tr>

@For Each item In Model

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 Last Name and Enrollment Date column headings to verify that sorting works.

After you click the Last Name heading, students are displayed in descending last name order.

Add a Search Box to the Students Index Page

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

Add Filtering Functionality to the Index Method

In Controllers\StudentController.vb, replace the Index method with the following code (the changes are highlighted):

Function Index(ByVal sortOrder As String, searchString As String) As ActionResult
    ViewBag.NameSortParm = If(String.IsNullOrEmpty(sortOrder), "name_desc", String.Empty)
    ViewBag.DateSortParm = If(sortOrder = "Date", "date_desc", "Date")

    Dim students = From s In db.Students Select s
    If Not String.IsNullOrEmpty(searchString) Then
        students = students.Where(Function(s) s.LastName.ToUpper().Contains(searchString.ToUpper()) _
                                              Or s.FirstMidName.ToUpper().Contains(searchString.ToUpper()))
    End If
    Select Case sortOrder
        Case "name_desc"
            students = students.OrderByDescending(Function(s) s.LastName)
        Case "Date"
            students = students.OrderBy(Function(s) s.EnrollmentDate)
        Case "date_desc"
            students = students.OrderByDescending(Function(s) s.EnrollmentDate)
        Case Else
            students = students.OrderBy(Function(s) s.LastName)
    End Select

    Return View(students.ToList())
End Function

You've added a searchString parameter to the Index method. You've also added  to the LINQ statement a Where clause 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 to the Index view.The statement that adds the where clause is executed only if there's a value to search for.

Add a Search Box to the Student Index View

In Views\Student\Index.vbhtml, add the highlighted code immediately before the opening table tag in order to create a caption, a text box, and a Search button.

<p>
    @Html.ActionLink("Create New", "Create")
</p>
@Using Html.BeginForm()
    @<p>
        Find by name: @Html.TextBox("SearchString")
        <input type="submit" value="Search" /></p>
End Using
<table class="table">
    <tr>

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

Notice the URL doesn't contain the "an" search string, which means that if you bookmark this page, you won't get the filtered list when you use the bookmark. You'll change the Search button to use query strings for filter criteria later in the tutorial.

Add Paging to the Students Index Page

To add paging to the Students Index page, you'll start by installing the PagedList.Mvc NuGet package. Then you'll make additional changes in the Index method and add paging links to the Index view. PagedList.Mvc is one of many good paging and sorting packages for ASP.NET MVC, and its use here is intended only as an example, not as a recommendation for it over other options. The following illustration shows the paging links.

Install the PagedList.MVC NuGet Package

The NuGet PagedList.Mvc package automatically installs the PagedList package as a dependency. The PagedList package installs a PagedList collection type and extension methods for IQueryable and IEnumerable collections. The extension methods create a single page of data in a PagedList collection out of your IQueryable or IEnumerable, and the PagedList collection provides several properties and methods that facilitate paging. The PagedList.Mvc package installs a paging helper that displays the paging buttons.

From the Tools menu, select Library Package Manager and then Package Manager Console.

In the Package Manager Console window, make sure the Package source is nuget.org and the Default project is ContosoUniversity, and then enter the following command:

Install-Package PagedList.Mvc 

Add Paging Functionality to the Index Method

In Controllers\StudentController.vb, add an Imports statement for the PagedList namespace:

@Imports PagedList.Mvc

Replace the Index method with the following code:

Function Index(ByVal sortOrder As String, currentFilter As String, searchString As String, page As Integer?) As ActionResult
    ViewBag.CurrentSort = sortOrder
    ViewBag.NameSortParm = If(String.IsNullOrEmpty(sortOrder), "name_desc", String.Empty)
    ViewBag.DateSortParm = If(sortOrder = "Date", "date_desc", "Date")

    If Not searchString Is Nothing Then
        page = 1
    Else
        searchString = currentFilter
    End If

    ViewBag.CurrentFilter = searchString

    Dim students = From s In db.Students Select s
    If Not String.IsNullOrEmpty(searchString) Then
        students = students.Where(Function(s) s.LastName.ToUpper().Contains(searchString.ToUpper()) _
                                      Or s.FirstMidName.ToUpper().Contains(searchString.ToUpper()))
    End If
    Select Case sortOrder
        Case "name_desc"
            students = students.OrderByDescending(Function(s) s.LastName)
        Case "Date"
            students = students.OrderBy(Function(s) s.EnrollmentDate)
        Case "date_desc"
            students = students.OrderByDescending(Function(s) s.EnrollmentDate)
        Case Else
            students = students.OrderBy(Function(s) s.LastName)
    End Select

    Dim pageSize As Integer = 3
    Dim pageNumber As Integer = If(page, 1)
    Return View(students.ToPagedList(pageNumber, pageSize))
End Function

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

Function Index(ByVal sortOrder As String, currentFilter As String, searchString As String, page As Integer?) As ActionResult

The first time the page is displayed, or if the user hasn't clicked a paging or sorting link, all the parameters will be 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 property, ViewBag.CurrentFilter, provides the view with the current filter string. This value must be 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 changed during paging, the page has to be reset to 1, because the new filter can result in different data to display. The search string is changed when a value is entered in the text box and the submit button is pressed. In that case, the searchString parameter is not null.

If Not searchString Is Nothing Then
    page = 1
Else
    searchString = currentFilter
End If

At the end of the method, the ToPagedList extension method on the students IQueryable object converts the student query to a single page of students in a collection type that supports paging. That single page of students is then passed to the view:

Dim pageSize As Integer = 3
Dim pageNumber As Integer = If(page, 1)
Return View(students.ToPagedList(pageNumber, pageSize))

The ToPagedList method takes a page number. The If operator called with two arguments is the equivalent to the C# null-coalescing operator. The operator returns the first non-null value from the two arguments. If page is Nothing, 1 is returned, otherwise the value of page is returned.

Add Paging Links to the Student Index View

In Views\Student\Index.vbhtml, replace the existing code with the following code. The changes are highlighted.

@ModelType PagedList.IPagedList(Of ContosoUniversity.Models.Student)
@Imports PagedList.Mvc
<link href="~/Content/PagedList.css" rel="stylesheet" type="text/css" />
@Code
    ViewBag.Title = "Students"
End Code

<h2>Students</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
@Using Html.BeginForm("Index", "Student", FormMethod.Get)
    @<p>
        Find by name: @Html.TextBox("SearchString", TryCast(ViewBag.CurrentFilter, String))
        <input type="submit" value="Search" /></p>
End Using
<table class="table">
    <tr>
        <th>
            @Html.ActionLink("Last Name", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter})
        </th>
        <th>
            First Name
        </th>
        <th>
            @Html.ActionLink("Enrollment Date", "Index", New With {.sortOrder = ViewBag.DateSortParm, .currentFilter = ViewBag.CurrentFilter})
        </th>
        <th></th>
    </tr>

@For Each item In Model
    @<tr>
        <td>
            @Html.DisplayFor(Function(modelItem) item.LastName)
        </td>
        <td>
            @Html.DisplayFor(Function(modelItem) item.FirstMidName)
        </td>
        <td>
            @Html.DisplayFor(Function(modelItem) item.EnrollmentDate)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", New With {.id = item.ID }) |
            @Html.ActionLink("Details", "Details", New With {.id = item.ID }) |
            @Html.ActionLink("Delete", "Delete", New With {.id = item.ID })
        </td>
    </tr>
Next

</table>
<br />
Page @IIf(Model.PageCount < Model.PageNumber, 0 , Model.PageNumber) of @Model.PageCount
@Html.PagedListPager(Model, Function(page) Url.Action("Index", _ 
      New With {page, .sortOrder = ViewBag.CurrentSort, .currentFilter = ViewBag.CurrentFilter}))

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

The Imports statement for PagedList.Mvc gives access to the MVC helper for the paging buttons. 

The code uses an overload of BeginForm that allows it to specify FormMethod.Get.

@Using Html.BeginForm("Index", "Student", FormMethod.Get)
    @<p>
        Find by name: @Html.TextBox("SearchString", TryCast(ViewBag.CurrentFilter, String))
        <input type="submit" value="Search" /></p>
End Using

The default BeginForm submits form data with a POST, which means that parameters are passed in the HTTP message body and not in the URL as query strings. When you specify HTTP GET, the form data is passed in the URL as query strings, which enables users to bookmark the URL. The W3C guidelines for the use of HTTP GET recommend that you should use GET when the action does not result in an update.

The text box is initialized with the current search string so when you click a new page you can see the current search string.

Find by name: @Html.TextBox("SearchString", TryCast(ViewBag.CurrentFilter, 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 With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter})

The current page and total number of pages are displayed.

Page @IIf(Model.PageCount < Model.PageNumber, 0 , Model.PageNumber) of @Model.PageCount

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

The paging buttons are displayed by the PagedListPager helper:

@Html.PagedListPager(Model, Function(page) Url.Action("Index", _ 
      New With {page, .sortOrder = ViewBag.CurrentSort, .currentFilter = ViewBag.CurrentFilter}))

The PagedListPager helper provides a number of options that you can customize, including URLs and styling.  For more information, see TroyGoode/PagedList on the GitHub site.

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.

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

Create the View Model

Create a ViewModels folder in the project folder. In that folder, add a class file EnrollmentDateGroup.vb and replace the template code with the following code:

Imports System.ComponentModel.DataAnnotations
Namespace ViewModels
    Public Class EnrollmentDateGroup
        <DataType(DataType.Date)>
        Public Property EnrollmentDate As DateTime?
        Public Property StudentCount As Integer
    End Class
End Namespace

Modify the Home Controller

In HomeController.vb, add the following Imports statements at the top of the file:

Imports ContosoUniversity.DAL
Imports ContosoUniversity.ViewModels

Public Class HomeController
    Inherits System.Web.Mvc.Controller

    Dim db As SchoolContext = New SchoolContext()

Replace the About method with the following code:

Function About() As ActionResult
    Dim data As IQueryable(Of EnrollmentDateGroup) = db.Students.GroupBy _
                                                     (Function(s) s.EnrollmentDate).Select _
                                                     (Function(g) New EnrollmentDateGroup With _
                                                                  {.EnrollmentDate = g.Key, .StudentCount = g.Count()})
    Return View(data.ToList())
End Function

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.

Add a Dispose method:

Protected Overrides Sub Dispose(disposing As Boolean)
    db.Dispose()
    MyBase.Dispose(disposing)
End Sub

Modify the About View

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

@ModelType IEnumerable(Of ContosoUniversity.ViewModels.EnrollmentDateGroup)
@Code
    ViewBag.Title = "Student Body Statistics"
End Code

<h2>Student Body Statistics</h2>

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

    @For Each item In Model
    @<tr>
        <td>
            @Html.DisplayFor(Function(modelItem) item.EnrollmentDate)
        </td>
        <td>
            @item.StudentCount
        </td>
    </tr>
    Next
</table>

Run the app and click the About link. The count of students for each enrollment date is displayed in a table.

s

Summary

In this tutorial you've 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.