Monday, September 29, 2008

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:

pagingSorting1

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:

pagingSortingView1

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

3 comments:

Anonymous said...

Great - thanks... I extended Stephen Walthers pager but ran into issues when it came to filtering the data. Whilst I haven't used your code exactly it gave me some fresh ideas on how to takle the problem.

Eric said...

This is a great example. I have learned a lot from it. I noticed that you write "So what happens when you want to add formatting to the column? Maybe adding a link column for example." For the life of me I can't figure out how I can do a link column from within the OrderTableInfo class. Wouldn't I need access to htmlHelper, or is there some other way to do it I am not considering. If it wasn't too much trouble, would you mind adding an example to the comments section or article like the one for the formatted order date column. Thanks so much. I really appreciate such a great article and you taking the time to work on the MVC platform, which I think is a great one for web development.

Mike Hadlow said...

Hi Eric, I should probably update this to work better with the MvcContrib Grid. They might even have added column sorting to it, making this solution a bit legacy. You can check the code out in Suteki Shop, it should be simple to work out how to use the TableInfo class to create a link column.

Good luck!