Filtering Data Using ASP.NET 4's QueryExtender Control

Introduction

One of the new controls available with ASP.NET 4 is the QueryExtender control. The QueryExtender is designed to simplify filtering data returned from a LinqDataSource or EntityDataSource by decoupling the filtering logic from the data source control. Using the QueryExtender is easy - simply add a QueryExtender to the page, specify what data source control it applies to, and then define the filtering criteria. For example, when displaying product information on a web page you could use the QueryExtender control and a few lines of markup to display only those products that are not within a certain price range and whose name or category starts with a user-specified search string.

Filtering the data returned by a LinqDataSource or EntityDataSource control is certainly possible without the QueryExtender; both the LinqDataSource and EntityDataSource controls have a Whereproperty that can be used to specify filtering criteria. What the QueryExtender offers is a simpler means by which to filter data. This article includes a number of demos (which can be downloaded at the end of this article) that showcase the QueryExtender's ease of use and its powerful filtering capabilities. Read on to learn more!

Building the Foundation

One thing to keep in mind is that the QueryExtender control only works against the LinqDataSource or EntityDataSource controls. It cannot be used to filter the results of a SqlDataSource control. Therefore, to use the QueryExtender you need to be using LINQ to SQL or the ADO.NET Entity Framework. A thorough discussion of these frameworks is beyond the scope of this article. In short, both LINQ to SQL and Entity Framework are object-relational mapping tools from Microsoft that build an object model that represents the data in a database. ASP.NET ships with two data source controls - the LinqDataSource and the EntityDataSource control - which allow a page developer to access data from these O/RMs declaratively.The demo available for download at the end of this article uses the ADO.NET Entity Framework to model the Products, Categories, and Suppliers tables in the Northwind database. The database file, NORTHWND.MDF, is located in the website's App_Data folder; the App_Code folder contains the ADO.NET Entity Data Model file, Northwind.edmx, which defines the mapping between the object model and the database.

New to LINQ to SQL and Entity Framework?
If you have not used LINQ to SQL or the Entity Framework in a past project but would like to learn more, check out the following resources. For getting started with LINQ to SQL, refer to Scott Guthrie's multi-part Using LINQ to SQL tutorials. To get rolling with Entity Framework, check out thisHow Do I Get Started with Entity Framework video by Alex James.

As noted in the Introduction, it is possible to filter data using the LinqDataSource or EntityDataSource alone. Consider a web page designed to allow the visitor to enter a maximum price and then to show the visitor all products that cost less than or equal to that price point. This can be accomplished with a TextBox, GridView, and data source control as the following markup and screen shot illustrates:

<b>Show All Prices Less Than:</b> $<asp:TextBox ID="txtMaxPrice" runat="server"></asp:TextBox> <asp:Button ID="btnDisplayProducts" runat="server" Text="Show Products" /> <asp:GridView ID="gvProducts" runat="server" DataSourceID="dsProducts" ...>    ... </asp:GridView> <asp:EntityDataSource ID="dsProducts" runat="server"    ConnectionString="name=NorthwindEntities"    DefaultContainerName="NorthwindEntities" EnableFlattening="False"    EntitySetName="Products" Where="it.UnitPrice <= @UnitPrice">    <WhereParameters>       <asp:ControlParameter ControlID="txtMaxPrice" Name="UnitPrice"          PropertyName="Text" Type="Decimal" />    </WhereParameters> </asp:EntityDataSource>

In the above example, the visitor enters a price into the txtMaxPrice TextBox and clicks the "Show Products" button. This causes a postback. On postback, the GridView is bound to the dsProducts EntityDataSource control, which queries the Entity Framework's Products object, applying a where condition that will only retrieve those products whose UnitPrice value is less than or equal to the amount entered in the txtMaxPrice TextBox. This filtering can be done declaratively, as the above markup illustrates. Alternatively, it could be done programmatically, with the query being modified from the EntityDataSource control's Selecting event handler.
There are two main drawbacks of specifying filtering logic in the EntityDataSource (or LinqDataSource) controls. First and foremost, both controls offer limited declarative functionality for filtering. Sure, filtering on one field - such as for products that cost less than a user-supplied amount - is easy enough, but it gets much harder doing multi-field filtering, such as filtering to show only products that cost less than a certain amount and whose name, supplier, or category contains a particular substring. The other drawback is that specifying filtering logic in the data source control smushes together the data retrieval and filtering logic into one control.
ASP.NET 4's new QueryExtender control makes it much easier to create more intricate filtering scenarios. What's more, using the QueryExtender control cleanly separates the data retrieval and filtering logic into two distinct controls - the EntityDataSource (or LinqDataSource) control gets the data while the QueryExtender handles the filtering. (The QueryExtender can also be used to specify how the results should be ordered, thereby allowing you to also decouple the sorting logic from the data source control.)

Using the QueryExtender Control to Filter Data

To see how the QueryExtender control works, let's start by creating a web page that lists all of the products, after which we'll use the QueryExtender to display only a subset of these products. To create such a page simply drag a GridView onto the page and bind it to an EntityDataSource control that retrieves all of the records from theProducts entity. This will result in the markup similar to the following:

<asp:GridView ID="gvProducts" runat="server" DataSourceID="dsProducts" ...>    ... </asp:GridView> <asp:EntityDataSource ID="dsProducts" runat="server"    ConnectionString="name=NorthwindEntities" EntitySetName="Products"    DefaultContainerName="NorthwindEntities" EnableFlattening="False"> </asp:EntityDataSource>

Next, add a QueryExtender control to the page. The QueryExtender control can be found under the Data tab in the Toolbox, which is the same tab where the GridView, EntityDataSource, and other data-related controls are located. The QueryExtender does not retrieve data - it simply applies filtering logic to a specified LinqDataSource or EntityDataSource control. Consequently, when using the QueryExtender control you must specify the ID of the data source control whose results to filter. This is accomplished via the QueryExtender's TargetControlID property. Set this property to dsProducts, the ID of the EntityDataSource control.
At this point the QueryExtender's declarative markup should look similar to the following:

<asp:QueryExtender ID="qeRange" runat="server" TargetControlID="dsProducts"> </asp:QueryExtender>

We're now ready to specify the QueryExtender filtering logic. The QueryExtender's filtering logic is defined using one or more expressions. Out of the box ASP.NET offers ten different types of expressions, including:

• RangeExpression - retrieves only those results where a specified field's value is less than, greater than, or in between specified values.
• SearchExpression - filters by comparing values in one or more fields with a search string.
• ControlFilterExpression - used to filter a field based on the value of a particular Web control on the page (such as a TextBox or DropDownList).
• CustomExpression and MethodExpression - these two expressions work in much the same way - they allow the page developer to write code that performs the filtering logic (rather than relying on declarative means alone).
• OrderByExpression - sorts the results based on the column(s) and sort direction(s) specified by the page developer.

For example, to display only those products whose UnitPrice is less than $10 we would add a RangeExpression to the QueryExtender control. To accomplish this, type in the following markup into in between the QueryExtender control's opening and closing tags:

<asp:QueryExtender ID="qeRange" runat="server" TargetControlID="dsProducts">    <asp:RangeExpression DataField="UnitPrice" MinType="Inclusive" MaxType="Exclusive">       <asp:Parameter DefaultValue="0" />       <asp:Parameter DefaultValue="10" />    </asp:RangeExpression> </asp:QueryExtender>

(Ideally, there would be a wizard accessible from the Design view that would assist us in adding and configuring these expressions. Unfortunately, there is no such rich designer experience, meaning you'll have to work from the Source view.)
As the above markup shows, the RangeExpression expression includes information as to what field to filter on - UnitPrice, in this example - and whether the comparison should be inclusive or exclusive. Because we want to see products greater than or equal to $0, but strictly less than $10, I have set the MinType and MaxType properties toInclusive and Exclusive, respectively. The bounds of the range are specified using Parameter controls within the RangeExpression, in this case specifying a lower and upper bound of 0 and 10, respectively.
Note that the RangeExpression can contain any of the data source parameter controls that can be used in the SqlDataSource, LinqDataSource, or EntityDataSource controls. This includes:

• The Parameter control, which is used to filter based on a hard-coded value,
• The SessionParameter control, which filters based on a session variable,
• The ControlParameter control, which filters based on the value of a Web control on the page, and others.

In fact, you can create your own custom control parameters. For more information on the parameter controls refer to Accessing and Updating Data in ASP.NET: Creating Custom Parameter Controls.For example, to update the filtering logic so that it filters based on the value entered by a user into a TextBox, we'd simply replace the second Parameter control in the RangeExpression with a ControlParameter, like so:

<asp:QueryExtender ID="qeRange" runat="server" TargetControlID="dsProducts">    <asp:RangeExpression DataField="UnitPrice" MinType="Inclusive" MaxType="Exclusive">       <asp:Parameter DefaultValue="0" />       <asp:ControlParameter ControlID="TextBoxID" DefaultValue="0" />    </asp:RangeExpression> </asp:QueryExtender>

For a complete working example of a page that only shows products that cost less than a user-specified amount refer to the FilterDemo.aspx page in the demo available for download at the end of this article.

When Does the QueryExtender Control's Filtering Take Place?

When using the QueryExtender control the EntityDataSource or LinqDataSource control is typically configured to return all records, leaving the QueryExtender responsible for filtering the data. But when does the QueryExtender control apply its filtering logic? Filtering could happen at one of two times: the EntityDataSource (or LinqDataSource) control could go to the database and retrieve all of the data (SELECT * FROM Products), after which the QueryExtender would apply its filtering; alternatively, the QueryExtender's logic could be applied before the database is query so that the EntityDataSource (or LinqDataSource) control gets just the filtered subset of data from the database (SELECT * FROM Products WHERE ...).As you might have guessed, the QueryExtender control's filtering criteria are applied before any actual data is retrieved from the database. Consider a QueryExtender with a RangeExpression that filters products such that only those whose UnitPrice field is between 0 and 10 are returned. Such a QueryExtender would cause the EntityDataSource to send a query like the following to the database: SELECT ... FROM Products WHERE UnitPrice >= 0 AND UnitPrice < 10

Using the SearchExpression To Filter By A String Value

The QueryExtender control and SearchExpression make it easy to filter data based on a search string. The SearchExpression allows you to specify one or more columns to search for a particular search string. What's more, you can specify whether to look for values that start with, end with, or contain the search string. The following screen shot shows a web page that contains a search box where a user can enter a string. The page then shows those products who product name, category, or supplier start with the entered search term. As the screen shot below shows, entering "a" into the search box returns products whose name start with "a" - Acme Tea, Acme Water, Alice Mutton, and Aniseed Syrup - along with Chartreuse verte, which is included because it's supplier, Aux joyeux ecclésiastiques, starts with the letter "a".
This search capability is implemented using a GridView and an EntityDataSource control that retrieves all products. The filtering logic is implemented by a single SearchExpression that is configured to filter the results by examining the ProductName, Category.CategoryName, and Supplier.CompanyName fields and including those that start with the text entered into the search box (txtFilterText):

<asp:QueryExtender ID="qeSearch" runat="server" TargetControlID="dsProducts">    <asp:SearchExpression DataFields="ProductName, Category.CategoryName, Supplier.CompanyName" SearchType="StartsWith">       <asp:ControlParameter ControlID="txtFilterText" />    </asp:SearchExpression></asp:QueryExtender>

That's all there is to it! Note that the SearchExpression will return records where any of the specified fields - ProductName, Category.CategoryName, andSupplier.CompanyName - start with the specified search string. See the Search.aspx page in the demo to see the SearchExpression in action.
If you want to show only those records where all of the fields match the search criteria you could use three SearchExpression controls - one for each field - like so:

<asp:QueryExtender ID="qeSearch" runat="server" TargetControlID="dsProducts">    <asp:SearchExpression DataFields="ProductName" SearchType="StartsWith">       <asp:ControlParameter ControlID="txtFilterText" />    </asp:SearchExpression>    <asp:SearchExpression DataFields="Category.CategoryName" SearchType="StartsWith">       <asp:ControlParameter ControlID="txtFilterText" />    </asp:SearchExpression>    <asp:SearchExpression DataFields="Supplier.CompanyName" SearchType="StartsWith">       <asp:ControlParameter ControlID="txtFilterText" />    </asp:SearchExpression> </asp:QueryExtender>

As the above syntax shows, the QueryExtender can include multiple expressions. Each expression is joined together via a Boolean AND, meaning that only those records that meet all of the filtering criteria are returned.

Specifying Filtering Logic Programmatically

The QueryExtender control makes it easy to define filtering logic declaratively via expressions like the RangeExpression and SearchExpression. It is also possible to define filtering logic programmatically using either the CustomExpression or MethodExpression controls. In a nutshell, the CustomExpression fires an event when it's time to apply the filtering logic; you create an event handler and write code that programmatically filters the data as needed. The MethodExpression works a little bit differently. With the MethodExpression you specify the name of a static method that takes as input an object of type IQueryable<T> and returns an object of the same type. The object passed into the method is the query before your filtering logic is applied. You can then add whatever filtering criteria are needed to the query and then return it.Let's look at using the MethodExpression in a bit more detail. Imagine that you need to filter the data displayed based on the logged on user. Maybe administrators can see all products but non-administrators are only able to view non-discontinued products. To accomplish this we would start by creating a static method that applies the filtering logic. This method would need to accept as input an object of type IQueryable<Product> and return an object of type IQueryable<Product>, as well.

// C# public static IQueryable FilterBasedOnUser(IQueryable query) {    if (Roles.IsUserInRole("Administrators"))       // Return the query without filtering       return query;    else       // Return only non-discontinued products       return query.Where(p => p.Discontinued == false); } ' VB Public Shared Function FilterBasedOnUser(ByVal query As IQueryable(Of Product)) As IQueryable(Of product)    If Roles.IsUserInRole("Administrators") Then       ' Return the query without filtering       Return query    Else       ' Return only non-discontinued products       Return query.Where(Function(p) p.Discontinued = False)    End If End Function

To apply filtering logic use LINQ's Where extension method. For more information on LINQ refer to my article series An Extensive Examination of LINQ.
With this method in place, all that remains is to add a QueryExtender control that uses a MethodExpression that references this method, which is accomplished by the following markup:

<asp:QueryExtender ID="qeByMethod" runat="server" TargetControlID="dsProducts">    <asp:MethodExpression MethodName="FilterBasedOnUser" /></asp:QueryExtender>

Specifying the Sort Order of the Returned Results

In addition to applying filtering logic, the QueryExtender control can also be used to specify how the returned records should be ordered. This is accomplished by using the OrderByExpression. With the OrderByExpression you specify the field to sort by and the direction (ascending or descending). For example, we could update the QueryExtender we just looked at to both filter programmatically by calling the FilterBasedOnUser method and to sort the returned results by the ProductName field by adding an OrderByExpression to the QueryExtender like so:

<asp:QueryExtender ID="qeByMethod" runat="server" TargetControlID="dsProducts">    <asp:MethodExpression MethodName="FilterBasedOnUser" />    <asp:OrderByExpression DataField="ProductName" Direction="Ascending" /> </asp:QueryExtender>

You can also include zero to many ThenBy tags within the OrderByExpression to indicate how ties should be broken. The following markup instructs the QueryExtender to sort the data by ProductName, breaking any ties by sorting on UnitPrice from most expensive to least:

<asp:QueryExtender ID="qeByMethod" runat="server" TargetControlID="dsProducts">    <asp:MethodExpression MethodName="FilterBasedOnUser" />    <asp:OrderByExpression DataField="ProductName" Direction="Ascending">       <asp:ThenBy DataField="UnitPrice" Direction="Descending" />   </asp:OrderByExpression> </asp:QueryExtender>

Conclusion

The QueryExtender control, new to ASP.NET 4, simplifies filtering the results returned by the LinqDataSource or EntityDataSource control. The QueryExtender control applies filtering (and even sorting) logic using one or more expressions. For example, the RangeExpression allows the page developer to declaratively apply a range filter to a field using lower and upper bounds that can be defined statically or coming from a predefined source, such as a session variable, querystring field, or a Web control on the page. The filtering logic can also be defined programmatically by using the CustomExpression or MethodExpression.Happy Programming!