Column Ordering, Paging and Filtering in the MVC Framework

Web applications often have pages that show a table of items. Often these items are filtered by some search criteria. We should be able to show a page of items at a time (usually called paging) and it's nice to be able to sort the items by clicking on the column headers (usually called column ordering). The one thing you really don't want to do when faced with these requirements is get the entire set back from the database and then filter,page and sort them either on the web server or the client.

Today I want to show you a simple framework for doing this with the MVC Framework. The MvcContrib project already has an excellent grid HTML helper written by Jeremy Skinner, so I've merely used the power of LINQ and extended the Grid to provide paging and column ordering.

Here's the page I want to produce. It shows orders from the Northwind database, you can filter by customer and shipper, click on the columns to order by the column's contents and page through the results. They all work together, so the pager remembers the filter and the column order and the column orderer remembers the page and filter:

Here's the SQL that produced this page:

exec sp_executesql N'SELECT [t3].[OrderID], [t3].[CustomerID], [t3].[EmployeeID], 
[t3].[OrderDate], [t3].[RequiredDate], [t3].[ShippedDate], [t3].[ShipVia], 
[t3].[Freight], 
[t3].[ShipName], [t3].[ShipAddress], [t3].[ShipCity], [t3].[ShipRegion], 
[t3].[ShipPostalCode], [t3].[ShipCountry]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t2].[CompanyName]) AS [ROW_NUMBER], 
[t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], 
[t0].[RequiredDate], 
[t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], 
[t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], 
[t0].[ShipCountry]
    FROM [dbo].[Orders] AS [t0]
    LEFT OUTER JOIN [dbo].[Shippers] AS [t1] ON [t1].[ShipperID] = [t0].[ShipVia]
    LEFT OUTER JOIN [dbo].[Customers] AS [t2] ON [t2].[CustomerID] = [t0].[CustomerID]
    WHERE ([t1].[ShipperID]) = @p0
    ) AS [t3]
WHERE [t3].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
ORDER BY [t3].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int',@p0=1,@p1=140,@p2=20

The filtering (by Shipper), paging and ordering are all executed on the database. Only twenty rows are returned by the query.

The key to making this work is a re-useable definition of the columns we want to display. Here's my one for this page:

 

public class OrderTableInfo : TableInfo<Order>
{
    public OrderTableInfo()
    {
        AddColumn(order => order.OrderID, "Order Id");
        AddColumn(order => order.Customer.CompanyName, "Customer");
        AddColumn(order => order.Shipper.CompanyName, "Shipper");
        AddColumn(order => order.OrderDate, "Order Date");
    }
}

I've created a new TableInfo class. Simply inherit this and add the columns you want to be displayed in the constructor.  Each column definition is simply a Expression<Func<T, TParam>> that returns the property of the root object that we want to display. The expressions are reused both to grab the value of the property we want to display and as input to the OrderBy clause that does the column ordering.

Here's the controller action for this page:

[HandleError]
public class HomeController : Controller
{
    private readonly NorthwindDataContext db = new NorthwindDataContext();
    private const int pageSize = 20;
    public ActionResult Index(string customerId, int? shipperId, FormCollection form)
    {
        // hack because property binder favors queryString over form
        if (form["CustomerId"] != null) customerId = form["CustomerId"];
        if (form["ShipperId"] != null) shipperId = (form["ShipperId"].Length == 0) ? 
            (int?)null : 
            int.Parse(form["ShipperId"]);
        var criteria = new OrderSearchCriteria
                       {
                           CustomerId = customerId,
                           ShipperId =  shipperId
                       };
        var orderTableInfo = new OrderTableInfo();
        var orders = db.Orders
            .ThatMatch(criteria)
            .SortColumns(orderTableInfo, Request.QueryString)
            .ToPagedList(Request.QueryString.PageNumber(), pageSize);
        ViewData["Title"] = "Orders";
        var customers = db.Customers;
        var shippers = db.Shippers;
        return View("Index", new NorthwindViewData()
            .WithOrders(orders)
            .WithOrderTableInfo(orderTableInfo)
            .WithOrderSearchCriteria(criteria)
            .WithCustomers(customers)
            .WithShippers(shippers));
    }
    public ActionResult About()
    {
        ViewData["Title"] = "About Page";
        return View();
    }
}

As you can see we create a new instance of the OrderTableInfo that describes the table we want to create. We then get the orders to display from the database. The SortColumns(orderTableInfo, Request.QueryString) extension method looks in the query string for a key called  'sortby' and matches its value to the column name described in OrderTableInfo. If it finds a matching value it uses the expression from OrderTableInfo in an OrderBy clause. ToPagedList(Request.QueryString.PageNumber(), pageSize) appends Skip() and Take() clauses to do the paging. We then pass the list of orders and the orderTableInfo to the view.

Here's the section of the view that renders the table:

Pager is an extension method on HtmlHelper that uses the IPagedList from MvcContrib to render the  pager. The table of orders is rendered by the MvcContrib grid, but instead of specifying our columns here we use the column specification from OrderTableInfo. I've created a new HtmlHelper extension method 'CreateGridColumnBuilder' that creates the Action<IRootGridColumnBuilder<T>> that defines the columns for the Grid.

You'll notice that the 'Order Date' shown in the screen shot above is a short date time (in UK format). So what happens when you want to add formatting to the column? Maybe adding a link column for example. I've provided an overloaded version of TableInfo's AddColumn method that takes a full MvcContrib grid column definition that you can use like this:

public class OrderTableInfo : TableInfo<Order>
{
    public OrderTableInfo()
    {
        AddColumn(order => order.OrderID, "Order Id");
        AddColumn(order => order.Customer.CompanyName, "Customer");
        AddColumn(order => order.Shipper.CompanyName, "Shipper");
        AddColumn(order => order.OrderDate, 
            col => col.For(order => order.OrderDate.Value.ToShortDateString()), 
            "Order Date");
    }
}

Note the Order Date now defines both the property we want to sort by and the full column definition. The problem here is that there's nothing to stop you from providing totally different values for these, so take care :)

So to sum up, just follow these steps:

1. Create a definition for your table by inheriting from TableInfo<T>
2. Chain SortColumns and ToPaged list to your IQueryable<T> expression that returns your items.
3. Pass the table definition and items to the view
4. Use the Pager and MvcContrib Grid to render the view. Pass Html.CreateGridColumnBuilder(<table definition>) to the Grid.

You can download the full source code for this example here:

http://static.mikehadlow.com/Mike.PagingSorting.zip

MVC Framework: Capturing the output of a view

Update: I found that this technique has problems because it changes the state of the HttpResponse by setting its internal _headersWritten flag which means you can't subsequently execute a redirect. I've developed a different and better technique which involves replacing the current HttpContext. See this post:

MVC Framework: Capturing the output of a view (part 2)

Sometimes it's useful to be able to capture the output of a view rather than simply have it streamed to the Response.OutputStream. A couple of things have made this quite easy to do. Firstly, from CTP 3 of the MVC Framework, controllers now return an ActionResult rather than just setting properties and leaving the infrastructure to act on them. This means we can take a ViewResult and call ExecuteResult on it before it's returned, allowing us to take control of the rendering.

The second innovation is a nice piece of work from the MVCContrib project. This is an open source add-on to the MVC Framework that provides lots of useful stuff like standard error handling, data binding, IoC container integration and lots more. If you're building MVC Framework applications you really should look at what this project can do for you. Deep in its bowels is the BlockRenderer class, this can take any action that renders to Response.OutputStream and divert it to a string. It does it by adding a custom filter to the Response filter chain.

Here's a silly example of it in action. I've created a test controller and in its Index action I'm calling the Item action on another controller called orderController. I'm then using the BlockRenderer to capture the rendering of the Item view in a string, which is then simply displayed.

using System.Web.Mvc;
using MvcContrib.UI;

namespace Suteki.Shop.Controllers
{
   public class TestController : Controller
   {
       private OrderController orderController;

       public TestController(OrderController orderController)
       {
           this.orderController = orderController;
       }

       public ActionResult Index()
       {
           // pass the current controller context to orderController
           orderController.ControllerContext = ControllerContext;

           // create a new BlockRenderer
           var blockRenderer = new BlockRenderer(HttpContext);

           // execute the Item action
           var viewResult = (ViewResult) orderController.Item(1);

           // change the master page name
           viewResult.MasterName = "Print";

           // we have to set the controller route value to the name of the controller we want to execute
           // because the ViewLocator class uses this to find the view
           this.RouteData.Values["controller"] = "Order";

           string result = blockRenderer.Capture(() => viewResult.ExecuteResult(ControllerContext));

           return Content(result);
       }
   }
}

Note that I change the name of the master page. You can imagine that the "Print" master page is a simple body tag container without any of the site furniture: menus and wotnot, that my standard master page might have. I also have to change the controller route value because the ViewLocator class of the MVC Framework uses it to find the view name.

The main reason I have for doing this, is so that I can send a view by email, but you can imagine how it might be useful to do other things like build up a page from multiple controllers and or views.

Upgrading Suteki Shop to MVC Framework CTP 3

I've just spent this morning upgrading Suteki Shop to the latest version of the MVC Framework; CTP 3. There are some nice touches in it, but not an awful lot has changed, especially if, like me, you've been using the Code Plex source that was recently made available. Scott Guthrie details most of the major changes with this release on his blog.

Action Result

Still, it wasn't all plain sailing, there have been a lot of API changes since the last code drop. The names of the new controller action return values have changed as have the properties on the Controller base class which return them. I had to do a global find and replace for those. I'd written some nice test helper extension methods around ActionResult which had to be upgraded.

View data changes

The changes to view data caused a lot of churn as well. Now, instead of two separate kinds of view data; dictionary and strongly typed, there's a single class ViewDataDictionary<T> that supports both, with a new Model property having the type of the type parameter. This means that every invocation of ViewData in your View has to change to ViewData.Model. Currently I factor all my view data into a strongly typed hierarchy that I can pass through to various html helpers I've written, but with the new style view data there might be an opportunity for some nice refactorings.

Select rendering changes

Anther change which brings up some nice possibilities is the separation of the DropDownList html helper extension (renamed from 'Select') from the actual building of the select list itself in a SelectList class that you can pass from the controller along with all its data. Providing select lists from an IoC container might be a nice way to factor the loading of lookup data out from the controller.

One thing caught me out though. They've changed the order of the 'value' and 'text' parameters from the old Select helper method. I got some nasty runtime errors because of this.

'Hidden' extension method changes

Another nasty was the Hidden html helper extension method that renders an '<input type="hidden">' tag. It now takes a string as its value parameter instead of an object. For some reason the aspnet compiler doesn't seem to catch this change and so my forms were failing at runtime with the value parameter as an empty string.

MVCContrib

Probably the biggest investment of time this morning was changing the MVCContrib code to work with CTP 3. No, I didn't upgrade the entire thing; I just ripped out anything I wasn't using and upgraded the rest. It wasn't too bad in the end which must be tribute to the quality of the code. Once Jeremy and co have got the whole thing working with CTP 3, I'll move back to the main branch.

It's really fun working with the MVC Framework. The pace of development has been swift and it's nice that the team don't have to bake in the API at this stage which has allowed them to progressively factor the framework with feedback from the community. Sure it means that you have to spend half a day changing your code to work with a new release every so often, but if that means the design can evolve rapidly, I'm all for it.