Introduction

Many ASP.NET websites allow users to filter data presented to them on the basis of certain conditions. A general approach used in such cases is to re-construct the database query again and again for each such filter parameter. Though this works well as far as end user requirements are concerned, it can make your code a bit tricky and cumbersome to manage. This is because you need to manipulate the WHERE parameters of the data source control under consideration (LINQ Data Source for example) in your source code. That is where the Query Extender control comes handy.

The ASP.NET Query Extender server control allows you to filter data retrieved from a data source without changing the underlying query. It does so using a declarative syntax without any need to programmatically manipulate query parameters. This article explores the Query Extender control with the help of several examples.

Available Filter Options

The Query Extender control allows you to filter data on the basis of several "expressions". They include:

• Search expression
• Range expression
• Property expression
• Dynamic Filter expression
• Control Filter expression
• Custom expression
• Order By expression

The "search expression" is possibly the most commonly used expression of the Query Extender control. It allows you to filter data on the basis of some string value. The filter can be applied using "contains", "starts with" and "ends with" searching. For example, suppose you have BlogPosts table that stores blog posts. Using "search expression" you can find all blog posts whose title "contains" a specified string value.

The "range expression" allows you to filter data on the basis of a numeric or date range. For example, you may use range expression to find out all blog posts published between certain start and end date.

The "property expression" allows you to filter data on the basis of a column value. For example, you may want to filter only those blog posts on which comments are allowed (Boolean value).

Dynamic Filter expression and Control Filter expression are used with ASP.NET Dynamic Data and we won't discuss them in this article.

If none of the filtering options mentioned above meet your requirements you can always specify a custom LINQ expression to filter the data using a "custom expression".

The "order by expression" doesn't filter the data as such but it simply sorts the data on the basis of certain column and certain direction (ascending / descending).

In the sections that follow you will be using many of the filter options mentioned above.

Sample database and LINQ data context

Before you delve any further create a new ASP.NET Website using Visual Studio 2010. Add a new SQL Server database to the App_Data folder and create a table named BlogPosts. Figure 1 shows the schema of the BlogPosts table.

Figure 1: Schema of BlogPosts table

Though the BlogPosts table has a simplistic structure (a real world blog will have many other columns) it suffices our purpose of illustrating the use of Query Extender control and several filter options discussed earlier.

Now, add a new LINQ to SQL class to the website and drag BlogPosts table from the Server Explorer onto the surface of the .dbml file. This will create a LINQ to SQL class named BlogPost as shown in Figure 2.

Figure 2: BlogPost LINQ to SQL class

Throughout our example we will be using a LINQ data source control that makes use of the BlogPost LINQ to SQL class.

Filtering data by searching string values

Now that you have the database and LINQ to SQL class ready let's build our first example that shows how the Query Extender control can filter data by searching string values.

Add a new web form to the website and drag and drop a LINQ data source control and a Query Extender control on it (both can be found on the Data tab of Visual Studio toolbox). Next, configure the LINQ data source control to fetch data using BlogPost LINQ to SQL class (Figure 3).

Figure 3: Configuring LINQ data source

Select the Query Extender control and set its TargetControlID property to LinqDataSource1. The TargetControlID property associates a Query Extender control to a data source control. This way the Query Extender control knows which data source control is providing data to be filtered. Next, add a TextBox, a Button and a GridView control so that your web form resembles the web form shown in Figure 4.

Figure 4: A GridView showing data from BlogPosts table

Switch to the HTML source view and modify the markup of Query Extender control as shown below:

Notice the above markup carefully. The Query Extender control has a Search Expression specified with the help of SearchType attribute. The SearchType can be Contains, StartsWith or EndsWith depending on your need. The DataFields attribute specifies a comma separated list of columns that are to be searched for the specified string value. The string value to be searched comes from a Control Parameter whose ControlID is set to TextBox1. The Submit button simply does the job of posting the form back to the server so that the filter settings are applied. Figure 5 shows the GridView with rows containing string "WCF". As you can see though the BlogPosts table contains three records the GridView is showing only one (having WCF in the Title or Content columns).

Figure 5: Search Expression in action

If you wish you can also supply the search value from query string using a Querystring Parameter instead of a Control Parameter. The following markup show how.

If you supply the query string parameter as:

http://localhost:1035/QueryExtenderDemo/Example1.aspx?value=WCF

you will get the same results. You may use the query string based searching if the search criteria is coming from another web form.

Filtering data between a specific range

Now let's consider another frequently used filtering option – date range. Suppose you wish to see only those records from the BlogPosts table whose PublishDate falls between certain date range. The RangeExpression allows you to specify a minimum and maximum limit of a range. To see the RangeExpression in action add a new web form to the website and design it as shown in Figure 6. Remember to configure the LinqDataSource and QueryExtender controls as in the previous example.

Figure 6: Filtering data based on a date range

This time, however, add RangeExpression to the QueryExtender control as follows:

The RangeExpression allows you to specify a DataField on the basis of which the range filter is to be applied. The MinType and MaxType properties indicate whether the start and end range limits should be included or excluded from the results. The actual start and end values are supplied via two ControlParameters.

Filtering data on the basis of property value

At times you may want to filter data on the basis of a specific fixed column value. For example, you may want to filter blog posts based on whether comments are allowed for them or not. Another example would be filtering on the basis of a Category value. In such cases you can use PropertyExpression. To see the PropertyExpression in action add another web form to the website and design it as shown in Figure 7.

Figure 7: Filtering on the basis of property value

As you can see the CheckBox at the top of the web form allows you to filter blog posts based on AllowComments column value. Set AutoPostBack property of the CheckBox to true so that checking / un-checking it will cause a post back. The QueryExtender markup this time makes use of PropertyExpression as shown below:

Notice that ControlParameter markup now uses Name property to specify name of the column that is to be compared. Since AllowComments contains a Boolean value the Checked property of the CheckBox is used for the comparison.

Assuming that BlogPosts table has a Category column and you want to filter on the basis of a Category value, the PropertyExpression will be:

In this case the Text property of the TextBox will be compared with the Category column of the BlogPosts table.

Filtering data using a custom LINQ expression

At times the inbuilt filtering options provided by the QueryExtender control may not meet your requirements. Luckily, you can specify a custom filtering logic using a CustomExpression. The Querying event of the CustomExpression gives you a chance to apply a custom filtering logic to the source data.

To see the CustomExpression in action, add another web form to the website and design it as shown in Figure 8.

Figure 8: CustomExpression in action

Though the web form shown in Figure 8 looks quite similar to earlier examples, this time the QueryExtender control is making use of CustomExpression. The relevant markup from the web form is shown below:

The Querying event of the CustomExpression is handled by an event handler - FilterData. The FilterData event handler needs to be written in the web form code. The following sample Querying event handler simply filters data by checking whether value as supplied in the TextBox is found in the Title column of BlogPosts table or not (of course, you can specify any other filtering logic here).

The Querying event handler receives an event argument of type CustomExpressionEventArgs. The Query property of the CustomExpressionEventArgs object allows you to define a custom query as per your requirements.

Sorting the resultant data

The data filtered by the QueryExtender control may need sorting before it is displayed in a data bound control. The OrderByExpression allows you do just that. Consider the following markup:

The above markup searches the Title column for a string and further applies sorting to the resultant data on the basis of PublishDate column. The DataField property of the OrderByExpression allows you to specify the column on which the data is to be sorted. The Direction property specifies the sorting direction (Ascending / descending). The ThenBy expression allows you to further sort the data on the basis of some other field. A sample run of the web form making use of the above markup is shown in Figure 9:

Figure 9: Sorting data using OrderByExpression

Summary

The QueryExtender server control allows you to filter data retrieved by a data source without modifying the original query. The Query Extender control allows you to filter data in six ways viz. Search expression, Range expression, Property expression, Dynamic Filter expression, Control Filter expression and Custom expression. The filtered data can also be ordered using the Order By expression. The declarative syntax along with the filtering options mentioned above allows you to quickly add searching capabilities to your web forms.

About Bipin Joshi

Bipin Joshi is a blogger, author and a Kundalini Yogi who writes about apparently unrelated topics - Yoga & Technology! A former Software Consultant and trainer by profession, Bipin is programming since 1995 and is working with .NET framework ever since its inception. He is an internation... This author has published 7 articles on DotNetSlackers. View other articles or the complete profile here.