Filtering a Grid of Data in ASP.NET MVC

By Scott Mitchell

---

A Multipart Series on Grids in ASP.NET MVC

Displaying a grid of data is one of the most common tasks faced by web developers. This article series shows how to display grids of data in an ASP.NET MVC application and walks through a myriad of common grid scenarios, including paging, sorting, filtering, and client-side enhancements. Displaying a Grid - this inaugural article walks through building the ASP.NET MVC demo application and retrieving and displaying database data in a simple grid. Sorting a Grid - learn how to display a sortable grid of data. Paging a Grid - shows how to efficiently page through a grid of data N records at a time. Filtering a Grid - see how to create a filtering interface to limit what records are displayed. Sorting and Paging a Grid - learn how to display a grid that is both sortable and can be paged. Sorting, Paging, and Filtering - shows how to display a grid that is sortable, pageable, and filterable. MvcContrib Grid - see how to use the free, open source MvcContrib Grid to display a grid of data.

Introduction

This article is the fourth installment in an ongoing series on displaying a grid of data in an ASP.NET MVC application. The previous two articles in this series - Sorting a Grid of Data in ASP.NET MVC and Displaying a Paged Grid of Data in ASP.NET MVC - showed how to sort and page data in a grid. This article explores how to present a filtering interface to the user and then only show those records that conform to the filtering criteria.

In particular, the demo we examine in this installment presents an interface with three filtering criteria: the category, minimum price, and whether to omit discontinued products. Using this interface the user can apply one or more of these criteria, allowing a variety of filtered displays. For example, the user could opt to view: all products in the Condiments category; those products in the Confections category that cost $50.00 or more; all products that cost $25.00 or more and are not discontinued; or any other such combination.

Like with its predecessors, this article offers step-by-step instructions and includes a complete, working demo available for download at the end of the article. Read on to learn more!

Step 0: A Brief Roadmap

This article walks through displaying a filtering interface and then showing only those matching records in a grid of data. It is presumed that you have already read and worked through the previous three articles in this series.

Recall that in Sorting a Grid of Data in ASP.NET MVC the demo was available at the URL www.yoursite.com/Products/Sortable. Visiting this URL displayed the grid using its default sorting characteristics, which was to sort the grid by the ProductName column in ascending order. To have the data sorted by an alternate column or sorting direction, you'd pass in the column name and sort direction through the querystring like so: www.yoursite.com/Products/Sortable?sortBy=ColumnName&ascending=true|false. For example, the URL /Products/Sortable?sortBy=UnitPrice&ascending=false would display the products sorted by the UnitPrice column in descending order.

The Displaying a Paged Grid of Data in ASP.NET MVC article worked much in the same way. Visiting www.yoursite.com/Products/Paged displayed the first 10 records. To view an alternate page of data, or to change the number of records displayed per page, you'd visit the URL www.yoursite.com/Products/Paged?page=pageNumber&pageSize=numberOfRecordsPerPage.

The filtering demo presented in this article works much the same way. Visiting www.yoursite.com/Products/Filterable displays the grid without any filtering logic applied. Consequently, all of the products are displayed. The filtering criteria is specified through three querystring parameters:

• categoryId - an integer value. If specified, only those products with a matching CategoryID value are returned.
• minPrice - a decimal value. If specified and greater than 0, only those products with a UnitPrice value greater than or equal to minPrice are returned.
• omitDiscontinued - a Boolean value. If true, only those products that are not discontinued are returned.

In the Introduction I gave three examples of how a visitor could filter the data. Here are what the URLs would look like for those three filtering scenarios:

• Products/Filterable?CategoryId=2 - shows all products in the Condiments category.
• Products/Filterable?CategoryId=3&MinPrice=50 - shows all products in the Confections category that cost $50.00 or more.
• Products/Filterable?MinPrice=25&OmitDiscontinued=true - shows all products that cost $25.00 or more and are not discontinued.

Of course, we won't require users to type in their filtering criteria into their browsers' Address bars (although they could). Instead, we'll create a filtering interface that lets the user specify their criteria. In Step 4 we will build this filtering interface; here's a sneak peak to whet your appetite.

Note that the code/demo in this article focuses on filtering only. The grid displays all of the matching products sorted by their ProductID values in ascending order. A future article will examine how to create a sortable, paged, filtered grid.

Step 1: Updating ProductGridModel, the View-Specific Model

Over the last two articles in this series we created a class named ProductGridModel designed specifically to serve as a Model for displaying product information in a grid. When we first created this class in the Sorting a Grid of Data in ASP.NET MVC article it had just four properties:

• Products - the collection of Product records to display in the grid,
• SortBy - the name of the column by which to sort the data,
• SortAscending - a Boolean value indicating whether to sort the data in ascending order (versus descending order), and
• SortExpression - the sorting string used by the Dynamic LINQ library to sort the data.

In Displaying a Paged Grid of Data in ASP.NET MVC we augmented this class, adding five paging-related properties:

• CurrentPageIndex - the page index currently being viewed,
• PageSize - the number of records to show per page of data,
• TotalRecordCount - the total number of records being paged through,
• PageCount - the number of pages of data, and
• NumericPageCount - how many numeric page buttons to show in the grid's paging interface.

As you might have surmised, we are going to update this class again to handle the filtering parameters - CategoryId, MinPrice, and OmitDiscontinued - along with a CategoryList property that is used by the filtering interface. More on this latter property in a moment.

If you are following along at your computer, expand the Models folder and open the ProductGridModel.cs file. Update the class to include the following properties and updated constructor. The text in red is what has been added from the prior article:

namespace Web.Models {    public class ProductGridModel    {       // Constructor       public ProductGridModel()       {          // Define any default values here...          this.PageSize = 10;          this.NumericPageCount = 10;          this.OmitDiscontinued = false;       }       // Data properties (this property already exists - you don't need to add this!)       public IEnumerable<Product> Products { get; set; }              // Sorting-related properties       ...       // Paging-related properties       ...       // Filtering-related properties       public int? CategoryId { get; set; }       public decimal MinPrice { get; set; }       public bool OmitDiscontinued { get; set; }       public IEnumerable CategoryList { get; set; }    } }

The first three filtering-related properties are, hopefully, pretty straightforward and self-explanatory. However, the last filtering property - CategoryList - is not like the others. It is a collection of SelectListItem instances. SelectListItem is a class in the MVC framework that represents a list item in a drop-down list. The filtering interface that we'll be building in Step 4 displays the categories as a drop-down list. This property - CategoryList - contains the list items to display in the drop-down. In the next step we'll see how this property gets populated.

Step 2: Creating the Filterable Action

When a request arrives for www.yoursite.com/Products/Filterable, ASP.NET MVC will execute the Filterable action in the ProductsController class. The Filterable action needs to accept three input parameters - a nullable integer parameter named categoryId, a decimal parameter named minPrice, and a nullable Boolean parameter named omitDiscontinued. When a request arrives for the Filterable action, ASP.NET MVC automatically maps any of the request's parameters to the input parameters of the executed action. For instance, when a request arrives for the URL www.yoursite.com/Products/Filterable?CategoryId=1&MinPrice=25&omitDiscontinued=false, ASP.NET MVC invokes the Filterable action and passes in the values 1, 25, and false to the action's categoryId, minPrice, and omitDiscontinued input parameters.

The Filterable action is responsible for creating a ProductGridModel object and assigning its filtering-related values. The following snippet shows the code for the this action. (Recall that in an earlier installment we added a DataContext property that to the ProductsController class that returns an instance of the Linq-to-Sql data context. The code this.DataContext.Products returns the collection of all products.)

public class ProductsController : Controller {    // GET: /Products/Filterable?CategoryID=number&MinPrice=number&DiscontinuedOnly=true|false    public ActionResult Filterable(int? categoryId = null, decimal minPrice = 0M, bool? omitDiscontinued = null)    {       var model = new ProductGridModel()       {          CategoryId = categoryId,          MinPrice = minPrice,          OmitDiscontinued = omitDiscontinued.HasValue ? omitDiscontinued.Value : false,          CategoryList = this.DataContext.Categories                                  .OrderBy(c => c.CategoryName)                                  .Select(c =>                                        new SelectListItem                                           {                                              Text = c.CategoryName,                                              Value = c.CategoryID.ToString()                                           }                                        )       };       var filteredResults = this.DataContext.Products.AsQueryable();       if (categoryId != null)          filteredResults = filteredResults.Where(p => p.CategoryID == categoryId.Value);       if (minPrice > 0M)          filteredResults = filteredResults.Where(p => p.UnitPrice >= minPrice);       if (omitDiscontinued != null && omitDiscontinued.Value == true)          filteredResults = filteredResults.Where(p => p.Discontinued == false);       model.Products = filteredResults;       return View(model);    } }

The Filterable action starts by creating a new ProductGridModel instance named model and assigning model's CategoryId, MinPrice, and OmitDiscontinued properties based on the values of its categoryId, minPrice, and omitDiscontinued input parameters.

Next, model's CategoryList property is populated with a list of SelectListItem objects that correspond to the categories in the Categories table. First, the collection of all categories is obtained via this.DataContext.Categories. The OrderBy clause sorts the categories by CategoryName in alphabetical order and then for each returned Category object a new SelectListItem object is created with its Text and Value properties assigned to the Category object's CategoryName and CategoryID properties, respectively.

At this point, all that remains is to assign the appropriate filtered set of products to model's Products property. This is done by creating a query from the collection of products. Next, the various input parameters are inspected to determine which filtering logic applies. For instance, if the categoryId input parameter is not null (meaning a value was specified), then the query is updated to return only those products whose CategoryID value matches the categoryId input parameter's value. Likewise, if the minPrice input parameter is greater than 0 then the query is further refined to only include those products with a UnitPrice greater than or equal to minPrice.

After the filtering logic has been applied, the filteredResults query is assigned to the Products property and the model object is passed to the View.

Step 3: Creating the View

To create the view for the Filterable action, right-click on the action name in the Controller class file and choose the Add View option. From the Add View dialog box, check the "Create a strongly-typed view" checkbox and then select the Web.Models.ProductGridModel option from the "View data class" drop-down (see the screen shot below). Click OK. This should close the dialog box and create (and open) the new view, Filterable.aspx.

Next, add the following markup and server-side code to the View in the Content control for the MainContent ContentPlaceHolder:

<table class="grid" style="width: 90%"> <tr> <th style="width: 35%">Product</th> <th style="width: 25%">Category</th> <th style="width: 25%">Qty/Unit</th> <th style="width: 15%">Price</th> <th style="width: 5%">Discontinued</th> </tr> <% foreach (var item in Model.Products) { %> <tr> <td class="left"><%: item.ProductName%></td> <td class="left"><%: item.Category.CategoryName%></td> <td class="left"><%: item.QuantityPerUnit%></td> <td class="right"><%: String.Format("{0:C}", item.UnitPrice)%></td> <td> <% if (item.Discontinued) { %> <img src="<%=Url.Content("~/Content/cancel.png") %>" alt="Discontinued" title="Discontinued" /> <% } %> </td> </tr> <% } %> </table>

The above markup is identical to what we added to the Sortable View in Sorting a Grid of Data in ASP.NET MVC). Take a moment to visit this View through a browser. If you visit Products/Filterable you should see a grid showing all of the records sorted by their default ordering (that is, by ProductID).

The grid shows all of the products because there are no filter criteria specified in the querystring. If you go to your browser's Address bar and enter some filtering criteria, however, you can view filtered results. For example, visit www.yoursite.com/Products/Filterable?CategoryID=1 and you will see only those products in the Beverages category.

Visiting www.yoursite.com/Products/Filterable?CategoryID=1 prompts the ASP.NET MVC framework to execute the ProductsController class's Filterable method, passing in the value 1 to its CategoryID input parameter. (Because the values for minPrice and omitDiscontinued were omitted, the default values are used: 0 and null, respectively.) The code in the Filterable action uses these input parameter values and returns only those products whose CategoryID equals 1 (namely, those products in the Beverages category).

Step 4: Adding the Filtering Interface

At this point the user viewing the grid cannot filter the results unless she types in the querystring parameters by hand in her browser's Address bar. To remedy this we need to add a filtering interface to the View.

Let's start by building the user interface for the filtering interface and not worry (yet) about how the user's filtering selections will be applied. Add the following markup above the <table> in the Filterable.aspx View:

<% using (Html.BeginForm("Filterable", "Products", FormMethod.Get)) { %>    <fieldset class="filtering">       <legend>Filter Product Results</legend>       <div>          <b>Show Products In:</b>          <%: Html.DropDownListFor(model => model.CategoryId, Model.CategoryList, "-- All Categories --") %>                    <b>Minimum Price:</b>          $<%: Html.TextBoxFor(model => model.MinPrice, new { size = 8 })%>          <b>Omit Discontinued?</b>          <%: Html.CheckBoxFor(model => model.OmitDiscontinued) %>          <input type="submit" value="Filter Results" />       </div>    </fieldset> <% } %>

The above markup contains four method calls to MVC's Helper class, which is part of the framework. The Helper class offers a variety of methods for generating markup for common user interface elements.

We start by calling the Html.BeginForm method, which emits a <form> element. The input parameters we are supplying to the BeginForm method instruct it to generate a <form> tag, when submitted, sends the user to Products/Filterable using the GET method, meaning that the form's fields' name/value pairs will be passed through the querystring.

To display a drop-down list of categories we use the Helper.DropDownListFor method, passing in three input parameters:

• A lambda expression indicating the selected value. In this case we're saying, "Select the list item whose value equals the Model's CategoryId property." Recall that the Model for this view is the ProductGridModel class, which has a CategoryId property that is assigned the value of the categoryId input parameter passed into the Filterable action. The categoryId input parameter is the value specified in the CategoryID querystring field, so in essence this first parameter has the effect of saying, "Select the list item that whose value matches the CategoryID passed through the querystring."
• The SelectListItems that make up the drop-down list. Recall that this property (CategoryList) is a collection of SelectListItems built from the Categories table.
• The text to use for the default, empty list item.

The Html.DropDownListFor method handles generating the appropriate markup for us based on these input parameters and frees us from having to write this markup ourselves, which would include a number of if statements and other messy code to ensure that the correct list item was selected.

The Html.TextBoxFor and Html.CheckBoxFor work in much the same way, generating the HTML for displaying a textbox and checkbox.

Lastly, there is a submit button labeled "Filter Results." When this button is clicked, the browser will submit the <form> we just defined, which prompts the browser to make a request back to the Products/Filterable URL, but passing in the name/values of the form fields via the querystring. Consequently, on submission the browser will load the URL Products/Filterable?CategoryID=selectedCategoryId&MinPrice=enteredMinPrice&OmitDiscontinued=checkedStatus.

To better understand how the Html class's methods are working, after adding the above markup visit the page and view the HTML source. Here is the markup for the filtering interface. Note how the various Html class method calls generated HTML.

<form action="/Products/Filterable" method="get">    <fieldset class="filtering">       <legend>Filter Product Results</legend>       <div>          <b>Show Products In:</b>          <select id="CategoryId" name="CategoryId"><option value="">-- All Categories --</option> <option value="1">Beverages</option> <option value="30">Biscuits</option> <option value="2">Condiments</option> <option value="3">Confections</option> <option value="4">Dairy Products</option> <option value="5">Grains/Cereals</option> <option value="6">Meat/Poultry</option> <option value="7">Produce</option> <option value="8">Seafood</option> </select>                 <b>Minimum Price:</b>          $<input id="MinPrice" name="MinPrice" size="8" type="text" value="0" />          <b>Omit Discontinued?</b>          <input id="OmitDiscontinued" name="OmitDiscontinued" type="checkbox" value="true" /><input name="OmitDiscontinued" type="hidden" value="false" />          <input type="submit" value="Filter Results" />       </div>    </fieldset> </form>

And that's all there is to it! With this markup in place the visitor can select their own filter criteria. Clicking the "Filter Results" button submits the form, which causes the browser to re-request the page, passing the filtering criteria through the querystring. Consequently, the appropriate subset of products are displayed in the grid. Below is a screen shot of the grid when filtering for Seafood products that cost $25 or more.

Conclusion and Looking Forward...

In this article we updated our view-specific Model (ProductGridModel) to accommodate filtering, created an applicable View, and designed a filtering interface to let the user enter the filtering criteria. At this point, the grid is sorted by its default ordering (by ProductID) and there's no way to page through the results (should there be more than 10 records). In a future installment we'll see how to display a filterable grid that is both sortable and paged.

Until then... Happy Programming!

By Scott Mitchell

---

Attachments:

Download the Demo Code Used in this Article

Further Reading

Displaying a Grid of Data in ASP.NET MVC

Sorting a Grid of Data in ASP.NET MVC

Displaying a Paged Grid of Data in ASP.NET MVC