Basic Sorter and Paging Tutorial

This tutorial aims to teach you how to use the Sorter functionality in your Entity Framework and ASP.NET MVC application. It assumes you already know Entity Framework and ASP.NET MVC and is written in C#.

You can follow along in Visual Studio if you like. However, the tutorial doesn't go into insane nitty-gritty detail like using statements, etc. You will need to fill in some blanks yourself. We recommend at least having Visual Studio open and using the Object Browser to look at the XML documentation of the classes provided by this project. That will give you more information about the purpose of the classes used in this tutorial.

The Sorter gives you the ability to easily let your users sort a table of entities that you have presented to them in the browser. They can click the column header cells to resort the columns. Multiple column sorting works out of the box. The page, as viewed, can be bookmarked with the sort order preserved as the order is placed in the URL. If they go away and come back to the same page within the same session, but without the URL parameter present, the sort order will be retrieved from the session.

The Paging functionality lets you easily write a UI that lets the user page through data. This is good, because you don't want to load all the data you've got in the DB and dump it on one page. Paging lets you cut it up into manageable chunks.

In this tutorial we will use an example entity class called "Employee" which has three properties on it: "FirstName", "LastName", and "Email". We are looking at a three layered application style. The UI layer is done in ASP.NET MVC, the business layer performs the business functionality (pretty much nothing in this tutorial), and the Data Access layer is the Entity Framework. Some may view this way of doing it as a slight merging between the Business layer and the Data Access layer, but that's okay.

Data Access layer

If you're wanting to try to implement this tutorial, create a database with a single table that represents the employee entity. Then create an EDMX for this database with the Employee entity.

Writing the Business Layer

In your business layer, you'll have a class that deals with employees. It'll look like this:

public class EmployeeManager
{
    private readonly int _PageSize;

    public EmployeeManager(int pageSize)
    {
        _PageSize = pageSize;
    }
}

_PageSize stores the number of Employees you want returned in each "page" of data.

You will have a method returns all employees that looks like this:

public IList<Employee> GetEmployees(int page, Sorter<Employee> sorter)
{
    using (MyEntitiesContext context = new MyEntitiesContext())
    {
        context.Employees.MergeOption = MergeOption.NoTracking;

        return context.Employees.OrderBy(sorter).Page(page, _PageSize).ToList();
    }
}

Notice that the method takes the page number, and the Sorter to use for sorting. The actual ordering and paging are done by calling the extension methods added to IQueryable<T>. They work in the same manner as the existing LINQ extension methods. We set the Employees ObjectQuery's MergOption to NoTracking, so that the objects returned from the query will be detached from the ObjectContext. We want this because the objects will be used outside of the lifecycle of the ObjectContext, which is disposed when the method returns.

We're also going to need a method that counts the number of employees (for paging):

public int CountEmployees()
{
    using (MyEntitiesContext context = new MyEntitiesContext())
    {
        context.Employees.MergeOption = MergeOption.NoTracking;
        return CompiledQueries.CountEmployees[MergeOption.NoTracking](context);
    }
}

That method is calling on a compiled query that we will create. Compiled queries are a great way of speeding up LINQ queries that don't change (if you're shaky on Compiled Queries, I discuss them on my blog). We aren't going to forget that the first MergeOption used on a compiled query is baked in, so we'll use a CompiledQueryReplicator to store the compiled query. We'll create a private static inner class of EmployeeManager called CompiledQueries to hold these compiled queries:

#region Compiled Queries

private static class CompiledQueries
{
    public static readonly CompiledQueryReplicator<Func<MyEntitiesContext, int>, MergeOption>
        CountEmployees = EfCompiledQueryReplicatorFactory<MergeOption>.Create(
            (MyEntitiesContext context) =>
                (from employee in context.Employees
                 select employee).Count());
}

#endregion

That's all you need to do in your business layer.

Writing the UI

Now that we've got a working business layer going, we'll write the code to get the UI working.

Writing the Controller

You're going to have an ASP.NET MVC Controller for your employee management stuff. Here's the skeleton:

public class EmployeeController : Controller
{
    private readonly EmployeeManager _EmployeeManager;
    private const int PAGE_SIZE = 20;

    public EmployeeController()
    {
        _EmployeeManager = new EmployeeManager(PAGE_SIZE);
    }
}

We've created an EmployeeManager in the constructor and specified the paging size to be the constant we've created.

We'll define a Manage action method like this:

public ActionResult Manage(int? page, [ModelBinder(typeof(EmployeeSorterBinder))] Sorter<Employee> sort)
{
    int totalPages = PagedDataHelper.GetTotalPages(_EmployeeManager.CountEmployees(), PAGE_SIZE);
    page = PagedDataHelper.ValidatePage(page, totalPages);

    IList<Employee> employees = _EmployeeManager.GetEmployees(page.Value, sort);
    return View(new PagedSortedViewModel<Employee>(employees, sort, page.Value, totalPages));
}

This method takes a nullable page number. This means that a page number may not necessarily be defined, which is fine because that means they want page 1. We also take a Sorter, which is loaded by using a model binder called "EmployeeSorterBinder" (as specified by the ModelBinder attribute). This is a class we will create shortly.

The method first calculates the total number of pages by using the PagedDataHelper class (part of DigitallyCreated.Utilities.Linq). It then gets a valid page number by calling the ValidatePage method. This ensures that no bogus page numbers are entered, and if they are the page number is set to 1. We then get the page of employees from the business layer, using our page number and specifying the sort order by passing in our Sorter.

We then create a PagedSortedViewModel, put the data we've got into it, and pass that to the Manage view (that we will create shortly). A PagedSortedViewModel is a strongly typed object that holds the list of employees, sort order that we're using, the page number we're on, and the total number of pages available. The HtmlHelpers provided by this project hook off this class to do their magic. If you need to pass additional data to the View, aside from the sortable data (a common enough occurrence), you can use a different PagedSortedViewModel that supports an auxiliary model. This is covered in a separate tutorial.

Remember that "EmployeeSorterBinder" we used to bind the Sorter<Employee> parameter on the action method? We need to implement this now. However, we don't need to implement it from scratch, simply "configure" the basic binding provided by this project. We do this by subclassing SorterModelBinder<T> and overriding some methods to provide our configuration. I typically create a new private inner class inside the controller. You should not share these classes between controllers since things like the SessionKey are specific to the page you're writing with sorting (more on SessionKey later). Here's the code:

private class EmployeeSorterBinder : SorterModelBinder<Employee>
{
    protected override Sorter<Employee> CreateNewSorter()
    {
        return new Sorter<Employee>()
            .AddProperty(e => e.FirstName, true)
            .AddProperty(e => e.LastName, true)
            .AddProperty(e => e.Email, true);
    }

    protected override string SessionKey
    {
        get { return "Employee.Manage.Sorter"; }
    }
}

We've overridden the CreateNewSorter method. The superclass calls this method when it needs to create a new Sorter. This occurs when it can't get the sort order out of the page URL, and it can't find a copy of the last used Sorter in the ASP.NET session. We need to return a new Sorter that is configured with the default sorting order we want our page to have. In this case, we're sorting by FirstName ascending (true is ascending, false is descending), then LastName ascending, then Email ascending. Notice how we are using lambda expressions to select the property to sort by. Also notice how we are chaining together our method calls. This is because the AddProperty method (and most of the other Sorter methods as well) returns the Sorter object. It allows us to use a "fluent" programming style.

We also override the SessionKey property. The string we return is the key that the base class uses to retrieve (and save) the last used Sorter from the ASP.NET session. This is the reason we cannot share this class between pages. If we did, then the sorting settings for Employee wouldn't be saved on a per page basis, and would be shared between pages (of course, if you want this, go right ahead and share the class!).

That's all we need to do in our controller.

Writing the View

We'll need a matching Manage view to go with that Mange action method in the EmployeeController. It needs to be strongly typed with PagedSortedViewModel<Employee> like so:

<%@ Page Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<PagedSortedViewModel<Employee>>" %>

Now, inside the content section of your view, we'll write the table that will contain the employees:

<table>
    <thead>
        <tr>
            <%= Html.SortableColumnHeader(e => e.FirstName, "First Name") %>
            <%= Html.SortableColumnHeader(e => e.LastName, "Last Name") %>
            <%= Html.SortableColumnHeader(e => e.Email, "Email") %>
        </tr>
    </thead>
    <tbody>
        <% if (Model.Any() == false) { %>
        
        <tr>
            <td colspan="3">There are no employees.</td>
        </tr>
        
        <% } %>
        
        <% foreach (Employee employee in Model) { %>
        
        <tr>
            <td><%= Html.Encode(employee.FirstName) %></td>
            <td><%= Html.Encode(employee.LastName) %></td>
            <td><%= Html.Encode(employee.Email) %></td>
        </tr>
        
        <% } %>
    </tbody>
</table>

You'll notice that in the thead section we're using some nice HtmlHelpers to output the HTML for each column header cell. The generated HTML allows the user to click the text to cause a sort. The sorting works in the same way as in any standard Windows list view with sortable columns. Here's what the HTML it generates looks like for all three columns:

<th class="Sortable SortedAscending PrimarySortColumn">
    <a title="First Name (Sorted Ascending)" href="/Employee/Manage?sort=FirstName!'LastName'Email">
        First Name 
    </a>
</th>
<th class="Sortable SortedAscending NonPrimarySortColumn">
    <a title="Last Name (Sorted Ascending)" href="/Employee/Manage?sort=LastName'FirstName'Email">
        Last Name 
    </a>
</th>
<th class="Sortable SortedAscending NonPrimarySortColumn">
    <a title="Email (Sorted Ascending)" href="/Employee/Manage?sort=Email'FirstName'LastName">
        Email 
    </a>
</th>

Note how all {th} elements have the class Sortable. The columns that are sorted ascending have the class SortedAscending; those that are sorted descending would have the class SortedDescending instead. The primary sort column (the first one being sorted; ie. if you're sorting by First Name, then Last Name, the primary column is First Name) has the class PrimarySortColumn and all others have the class NonPrimarySortColumn. These classes allow you to use CSS to style the table cell, perhaps to contain a sort direction arrow to aid the user.

Also note the sort order in the link href. It will be used if the user clicks this column header cell. The encoding that is used for this query string parameter is well documented in the Sorter class's XML documentation. There are ways to shorten it (ie to something like ?sort=F!'L'E'U) but this is not covered in this tutorial (see Sorter Translation Dictionaries Tutorial). The name of the column and its current sort order is also outputted into the link's title attribute.

The rest of the code in the above View code snippet is standard-fare ASP.NET MVC. We're just iterating over the Model and outputting each employee. The PagedSortedViewModel<T> is IEnumerable<T>, so it automatically iterates over the list of employees you passed into its constructor in the Controller.

Now we want some paging controls. We can get them by simply using this HtmlHelper:

<%= Html.PagingControls() %>

This outputs HTML that looks like this:

<p>
    <a href="/Employee/Manage?page=1">&lt;&lt; Previous</a> 
    Page 2 of 3 
    <a href="/Employee/Manage?page=3">Next &gt;&gt;</a>
</p>

The Previous and Next links will not be rendered when there is no next or previous page.

Both the sortable column headers and the paging controls play nice with each other. Both paging and sorting will generate URLs that retain all the other query string parameters present, so you get URLs like this:

/Employee/Manage?page=2&sort=LastName!'FirstName'Email'Username

And that's it! You've now got sorting and paging for almost free!

Disadvantages

The main disadvantage to using the Sorter class is that you cannot create compiled queries that involve the new OrderBy(Sorter<T>) extension method. Because the query is generated dynamically based on the user's sort preferences compiling queries won't work because to compile a query it needs to be static (unchanging). By its very nature, Sorter is not static. The end result means that your queries take a bit longer to run than compiled queries.

Hints

To easily use the new HtmlHelpers in your ASP.NET MVC views, add the following to your web.config under the system.web/pages/namespaces tag:

<add namespace="DigitallyCreated.Utilities.Mvc"/>

This ensures that your views include that namespace by default, thereby making the new HtmlHelper extension methods available to you without doing manual imports.

Last edited Mar 24, 2010 at 7:03 AM by dchambers, version 8

Comments

No comments yet.