Filtering Data with the QueryExtender Control

A very common task for developers who create data-driven Web pages is to filter data. This traditionally has been performed by building Where clauses in data source controls. This approach can be complicated, and in some cases the Where syntax does not let you take advantage of the full functionality of the underlying database.

To make filtering easier, a new QueryExtender control has been added in ASP.NET 4. This control can be added to EntityDataSource or LinqDataSource controls in order to filter the data returned by these controls. Because the QueryExtender control relies on LINQ, the filter is applied on the database server before the data is sent to the page, which results in very efficient operations.

The QueryExtender control supports a variety of filter options. The following sections describe these options and provide examples of how to use them.

Search

For the search option, the QueryExtender control uses provided text to find records in specified fields. In the following example, the control uses the text that is entered in the TextBoxSearch control and searches for its contents in the ProductName and Supplier.CompanyName columns in the data returned from the LinqDataSource control.

<asp:LinqDataSource ID="dataSource" runat="server"> TableName="Products">

</asp:LinqDataSource>

<asp:QueryExtender TargetControlID="dataSource" runat="server">

<asp:SearchExpression DataFields="ProductName, Supplier.CompanyName"

SearchType="StartsWith">

<asp:ControlParameter ControlID="TextBoxSearch" />

</asp:SearchExpression>

</asp:QueryExtender>

Range

The range option is similar to the search option, but specifies a pair of values to define the range. In the following example, the QueryExtender control searches the UnitPrice column in the data returned from the LinqDataSource control. The range is read from the TextBoxFrom and TextBoxTo controls on the page.

<asp:LinqDataSource ID="dataSource" runat="server"> TableName="Products">

</asp:LinqDataSource>

<asp:QueryExtender TargetControlID="dataSource" runat="server">

<asp:RangeExpression DataField="UnitPrice" MinType="Inclusive"

MaxType="Inclusive">

<asp:ControlParameter ControlID="TextBoxFrom" />

<asp:ControlParameter ControlID="TexBoxTo" />

</asp:RangeExpression>

</asp:QueryExtender>

PropertyExpression

The property expression option lets you define a comparison to a property value. If the expression evaluates to true, the data that is being examined is returned. In the following example, the QueryExtender control filters data by comparing the data in the Discontinued column to the value from the CheckBoxDiscontinued control on the page.

<asp:LinqDataSource ID="dataSource" runat="server" TableName="Products">

</asp:LinqDataSource>

<asp:QueryExtender TargetControlID="dataSource" runat="server">

<asp:PropertyExpression>

<asp:ControlParameter ControlID="CheckBoxDiscontinued" Name="Discontinued" />

</asp:PropertyExpression>

</asp:QueryExtender>

CustomExpression

Finally, you can specify a custom expression to use with the QueryExtender control. This option lets you call a function in the page that defines custom filter logic. The following example shows how to declaratively specify a custom expression in the QueryExtender control.

<asp:LinqDataSource ID="dataSource" runat="server" TableName="Products">

</asp:LinqDataSource>

<asp:QueryExtender TargetControlID="dataSource" runat="server">

<asp:CustomExpression OnQuerying="FilterProducts" />

</asp:QueryExtender>

The following example shows the custom function that is invoked by the QueryExtender control. In this case, instead of using a database query that includes a Where clause, the code uses a LINQ query to filter the data.

protected void FilterProducts(object sender, CustomExpressionEventArgs e)

{

e.Query = from p in e.Query.Cast<Product>()

where p.UnitPrice >= 10

select p;

}

These examples show only one expression being used in the QueryExtender control at a time. However, you can include multiple expressions inside the QueryExtender control.