How to: Using the ASP.NET 4 QueryExtender Control

Introduction

The QueryExtender control is newly added to the ASP.NET repertoire and is intended to be used to create filters for data that is retrieved from a data source, without using an explicit Where clause in the data source. The control can be used to filter data in the markup of a Web page by using declarative syntax.

Background

A very common task for developers who create data-driven Web pages is to filter data. Filters are found everywhere in the internet arena and no matter which site we surf, there are filters to facilitate ease of access to the data store. Terminologies can be different, somewhere they are just filters, somewhere search, elsewhere find or simply advanced search ! Filtering excludes data from a data source by displaying only the records that meet specified criteria adopted. Filtering enables you to present on the web page, varying views of the data that is in a dataset without affecting the data in the dataset.

Filtering typically requires you to create Where clauses to apply to the command that queries the data source. However, the Where property of the LinqDataSource control does not expose the full functionality that is available in LINQ. To address this concern and to make web page data filtering easier and convenient, ASP.NET 4 has introduced QueryExtender control which allows the designer to apply filtering to a data source using declarative syntax and with very minimal amount of code (you don't have to create explicit queries on the data source). It provides richer expressions for filtering than a Where clause.

Using the code

The QueryExtender control can be added to EntityDataSource or LinqDataSource controls in order to filter the data returned by these controls. It is based on LINQ, so the filter is applied on the database server before the data is sent to the page, which results in very efficient filtering operations.

The QueryExtender control supports a variety of filter options. The following article describes these options and provide samples of how to use them.

Step 1: Adding a web page

First come first. We must have a web page where we want to filter the data and present it in different views. I am creating a simple webpage default.aspx with just few controls which are actually needed to demonstrate the capabilities of QueryExtender. form element is shown in the code block here.

<form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td>
            Filter Results by Name: 
            </td>
            <td>
            <asp:TextBox ID="TextBoxName" runat="server" ></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
            Filter Results by ID between
            </td>
            <td>
            <asp:TextBox ID="TextBoxFrom" runat="server" Width="50px"></asp:TextBox>
            and <asp:TextBox ID="TextBoxTo" runat="server" Width="50px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
            Make Flag:
            </td>
            <td>
            <asp:CheckBox ID="CheckBoxMakeFlag" runat="server" />
            </td>
        </tr>
    </table>
    <asp:Button ID="btnFilter" runat="server" Text="Filter" /> 
    </div> 
</form>

Step 2: Adding a LINQ Data Source

QueryExtender control is LINQ based that means it can be used with LinqDataSource and EntityDataSource control. Let us add a LinqDataSource for the Products table in the AdventureWorks database (This is a sample database provided by SQL Server and you can install it from the SQL Server installation options -> sample databases).

<asp:LinqDataSource ID="ldsMyLinqDataSource" runat="server" TableName="Products" 
    Select="new (ProductID, Name, ProductNumber, MakeFlag, FinishedGoodsFlag, 
    Color, StandardCost, 
    ListPrice, Size, Class, DaysToManufacture, ModifiedDate)">
</asp:LinqDataSource>

Note: For the sake of simplicity, I have just included few fields in the Products table which we are going to use in various filtering options.

Step 3: Adding a QueryExtender control

Next step is to have a QueryExtender control which targets the LinqDataSource we have added above. This is pretty simple (remember, declarative syntax !)

<asp:QueryExtender ID="ctlMyQueryExtender" runat="server" TargetControlID="ldsMyLinqDataSource"> </asp:QueryExtender>

Step 4: Filtering Options

Now that we have our data source and QueryExtender ready, lets take a look at the choice of Expressions available for use with it.

SearchExpression

This is a text-based search. QueryExtender control performs a text-search in the specified fields. searches a field or fields for string values and compares them to a specified string value. The expression can perform a "starts with", "contains", or "ends with" search. For example, you can enter text into a text box control and use the expression to search for that text in columns that are returned from a data source control.

Lets modify the QueryExtender control to incorporate a SearchExpression. So our code now becomes,

<asp:QueryExtender ID="ctlMyQueryExtender" runat="server" TargetControlID="ldsMyLinqDataSource">

    <asp:SearchExpression DataFields="Name" SearchType="StartsWith">
        <asp:ControlParameter ControlID="TextBoxName" />
    </asp:SearchExpression>

</asp:QueryExtender>

RangeExpression

This is a range search expression. It uses a pair of values to define a range. The expression determines whether the value in a column falls in between the minimum and maximum ranges. Lets add this to our existing QueryExtender control. Modified code is,

<asp:QueryExtender ID="ctlMyQueryExtender" runat="server" TargetControlID="ldsMyLinqDataSource">

    <asp:SearchExpression DataFields="Name" SearchType="StartsWith">
        <asp:ControlParameter ControlID="TextBoxName" />
    </asp:SearchExpression>

    <asp:RangeExpression DataField="ProductID" MaxType="Inclusive" MinType="Inclusive">
        <asp:ControlParameter ControlID="TextBoxFrom" />
        <asp:ControlParameter ControlID="TextBoxTo" />
    </asp:RangeExpression>

</asp:QueryExtender>

OrderByExpression

This expression lets you sort data by a specified column and sort direction. Adding to our existing QueryExtender sample.

<asp:QueryExtender ID="ctlMyQueryExtender" runat="server" TargetControlID="ldsMyLinqDataSource">

    <asp:SearchExpression DataFields="Name" SearchType="StartsWith">
        <asp:ControlParameter ControlID="TextBoxName" />
    </asp:SearchExpression>

    <asp:RangeExpression DataField="ProductID" MaxType="Inclusive" MinType="Inclusive">
        <asp:ControlParameter ControlID="TextBoxFrom" />
        <asp:ControlParameter ControlID="TextBoxTo" />
    </asp:RangeExpression>

    <asp:OrderByExpression DataField="ListPrice" Direction="Descending">
        <asp:ThenBy DataField="ProductID" Direction="Ascending" />
    </asp:OrderByExpression>

</asp:QueryExtender>

PropertyExpression

This expression compares a property value of a column to a specified value. For example, you can compare a Boolean value to the value in the discontinued column of a Products table in a database. See the code below.

<asp:QueryExtender ID="ctlMyQueryExtender" runat="server" TargetControlID="ldsMyLinqDataSource">

    <asp:SearchExpression DataFields="Name" SearchType="StartsWith">
        <asp:ControlParameter ControlID="TextBoxName" />
    </asp:SearchExpression>

    <asp:RangeExpression DataField="ProductID" MaxType="Inclusive" MinType="Inclusive">
        <asp:ControlParameter ControlID="TextBoxFrom" />
        <asp:ControlParameter ControlID="TextBoxTo" />
    </asp:RangeExpression>

    <asp:OrderByExpression DataField="ListPrice" Direction="Descending">
        <asp:ThenBy DataField="ProductID" Direction="Ascending" />
    </asp:OrderByExpression>

    <asp:PropertyExpression>
        <asp:ControlParameter ControlID="CheckBoxMakeFlag" Name="MakeFlag" />
    </asp:PropertyExpression>

</asp:QueryExtender>

CustomExpression

This expression enables you to provide a custom LINQ expression that can use in the QueryExtender control. So lets add one.

<asp:QueryExtender ID="ctlMyQueryExtender" runat="server" TargetControlID="ldsMyLinqDataSource">

    <asp:SearchExpression DataFields="Name" SearchType="StartsWith">
        <asp:ControlParameter ControlID="TextBoxName" />
    </asp:SearchExpression>

    <asp:RangeExpression DataField="ProductID" MaxType="Inclusive" MinType="Inclusive">
        <asp:ControlParameter ControlID="TextBoxFrom" />
        <asp:ControlParameter ControlID="TextBoxTo" />
    </asp:RangeExpression>

    <asp:OrderByExpression DataField="ListPrice" Direction="Descending">
        <asp:ThenBy DataField="ProductID" Direction="Ascending" />
    </asp:OrderByExpression>

    <asp:PropertyExpression>
        <asp:ControlParameter ControlID="CheckBoxMakeFlag" Name="MakeFlag" />
    </asp:PropertyExpression>

    <asp:CustomExpression OnQuerying="FilterProducts"></asp:CustomExpression>

</asp:QueryExtender>

Now add the custom method 'FilterProducts' in the code behind default.aspx.cs.

protected void FilterProducts(object sender, CustomExpressionEventArgs e)
{
    e.Query = from p in e.Query.Cast<Product>()
        where p.ListPrice >= 6600
            select p;
}

Now lets add a gridview control to display on the web page and filter our data according to the expressions applied.

<asp:GridView ID="GridView1" CssClass="grd-cell-strd" runat="server" AllowSorting="true" 
AllowPaging="True" DataSourceID="ldsMyLinqDataSource" AutoGenerateColumns="False">
    <Columns>
    <asp:BoundField DataField="ProductID" HeaderText="ProductID" ReadOnly="True" 
        SortExpression="ProductID" />
    <asp:BoundField DataField="Name" HeaderText="Name" ReadOnly="True" 
        SortExpression="Name" />
    <asp:BoundField DataField="ProductNumber" HeaderText="ProductNumber" 
        ReadOnly="True" SortExpression="ProductNumber" />
    <asp:CheckBoxField DataField="MakeFlag" HeaderText="MakeFlag" ReadOnly="True" 
        SortExpression="MakeFlag" />
    <asp:CheckBoxField DataField="FinishedGoodsFlag" HeaderText="FinishedGoodsFlag" 
        ReadOnly="True" SortExpression="FinishedGoodsFlag" />
    <asp:BoundField DataField="Color" HeaderText="Color" ReadOnly="True" 
        SortExpression="Color" />
    <asp:BoundField DataField="StandardCost" HeaderText="StandardCost" 
        ReadOnly="True" SortExpression="StandardCost" />
    <asp:BoundField DataField="ListPrice" HeaderText="ListPrice" ReadOnly="True" 
        SortExpression="ListPrice" />
    <asp:BoundField DataField="Size" HeaderText="Size" ReadOnly="True" 
        SortExpression="Size" />
    <asp:BoundField DataField="Class" HeaderText="Class" ReadOnly="True" 
        SortExpression="Class" />
    <asp:BoundField DataField="DaysToManufacture" HeaderText="DaysToManufacture" 
        ReadOnly="True" SortExpression="DaysToManufacture" />
    <asp:BoundField DataField="ModifiedDate" HeaderText="ModifiedDate" 
        ReadOnly="True" SortExpression="ModifiedDate" />
    </Columns>
</asp:GridView>

Here we go. Add this code to your newly created aspx page and run !

Conclusion

QueryExtender control makes filtering data easier on a web page. It provides richer filtering expressions than a traditional LINQ Where clause. It also provides a common query language for the LinqDataSource and EntityDataSource controls. If you use the QueryExtender with these data source controls, you can provide searching capability in a Web page without writing a model-specific Where clause or eSQL statement. Moreover it supports a variety of filtering options which we just looked at, and they can be used individually or together as AND filters.