Practical ASP.NET
The QueryExtender
Extend runtime sorting and filtering of data with ASP.NET 4's QueryExtender, which provides a single interface for DataSources.
As far as ASP.NET developers are concerned, the Microsoft .NET Framework 3.0 and 3.5 were effectively "no upgrade" releases, with the exception of two controls: the DataList and Pager. The .NET Framework 4, on the other hand, offers a number of new features aimed directly at a key issue for ASP.NET developers building business applications and managing data. The most interesting is the QueryExtender, which does two things. First, it substantially extends what filtering and sorting you can do with the LinqDataSource and the EntityDataSource. Second, it makes the two DataSources look alike when it comes to specifying what data you want.
The LinqDataSource didn't provide much runtime flexibility when it came to retrieving data at runtime based on the user's input -- you had to make most of your decisions at design time. The solutions you could apply were primarily procedural and code-driven: there wasn't a powerful, declarative, codeless option available. When you could take action at runtime, LinqDataSource and EntityDataSource required very different solutions.
The QueryExtender simplifies data management by providing a single declarative syntax for filtering data, regardless of DataSource. You just add the QueryExtender to your page, tie it to a DataSource and specify which options in the QueryExtender you want to take advantage of. You can sort the output from your DataSource and filter it based on the content of the user's entries on the page. Any DataView attached to the DataSource will reflect the results of your settings in the QueryExtender.
Figure 1 shows a QueryExtender page with a GridView and a DetailsView both tied to a LinqDataSource. The results in the GridView are filtered by the values that the user enters into the text boxes on the screen. The data is sorted by options selected in the QueryExtender.
[Click on image for larger view.] |
| Figure 1. The results in the GridView are limited by the user's entries into the text box to show only courses beginning with the word "Developing." |
In theory, the QueryExtender supports both the LinqDataSource and the EntityDataSource. In practice ... not so much. For the QueryExtender to work with a DataSource, the DataSource must implement the IQueryableDataSource and -- as of beta 2 -- only the LinqDataSource supports the interface. In the sample application in the download for this article, I've included a selection of DataSources that you can use to test for compatibility with the QueryExtender as .NET 4 rolls out.
Sorting with the QueryExtender
As of beta 2, there's no visual designer for QueryExtender and the control doesn't appear in Visual Studio 2010's Toolbox. If you want to experiment with the QueryExtender, you'll need to do it in Source View. Your first step is to add the QueryExtender to a page and specify the DataSource to extend using the QueryExtender's TargetControlId property. This example ties a QueryExtender to a DataSource called LinqDataSource1:
<asp:QueryExtender ID="QueryExtender1"
runat="server"
TargetControlID="LinqDataSource1">
The simplest way to use the QueryExtender is to have it sort data. You can do that just by adding the OrderByExpression element inside the QueryExtender. This example sorts the data returned by the DataSource in descending order using the CourseNumber property on the objects returned by the DataSource:
<asp:QueryExtender ID="QueryExtender1"
runat="server"
TargetControlID="LinqDataSource1">
<asp:OrderByExpression DataField="CourseNumber"
Direction="Descending" />
</asp:QueryExtender>
If, at this point, you find that you're getting an "unknown server tag" message, you'll need to add this tag to your web.config file inside the controls element inside the pages element:
<pages>
<controls>
<add tagPrefix="asp"
namespace="System.Web.UI.WebControls.Expressions"
assembly="System.Web.Extensions,
Version=4.0.0.0, Culture=neutral,
PublicKeyToken=31BF3856AD364E35"/>
To sort by additional fields, you can add ThenBy tags within the OrderByExpression element. This example sorts the data by Description when there are two elements with the same CourseNumber:
<asp:OrderByExpression DataField="CourseNumber"
Direction="Descending">
<asp:ThenBy DataField="Title" Direction="Ascending" />
</asp:OrderByExpression>
The usual limitations on sorting very large fields still apply: You can't, for instance, sort on SQL Server Text fields.
Filtering Data by Range
Beyond just sorting your data, the QueryExtender lets you filter it by the value of fields retrieved through your DataSource. The easiest way to filter data is to use the RangeExpression element, which lets you specify a maximum and minimum value for any field in your results. You also supply parameters that specify where the high and low values come from.
In this example, the results are restricted by the CourseNumber field to values between 0 and 150, inclusive:
<asp:RangeExpression DataField="CourseNumber"
MaxType="Inclusive" MinType="Inclusive">
<asp:Parameter Type="Int32" DefaultValue="0" />
<asp:Parameter Type="Int32" DefaultValue="150" />
</asp:RangeExpression>
As with previous versions of DataSources, you can tie your high and low values to controls. With the QueryExtender, you do that by adding ControlParameter tags. This example ties the high and low range to two TextBoxes called HighCourseNumber and LowCourseNumber:
<asp:RangeExpression DataField="CourseNumber"
MaxType="Inclusive" MinType="Inclusive">
<asp:ControlParameter ControlID="LowCourseNumber"
PropertyName="Text" Type="Int32" />
<asp:ControlParameter ControlID="HighCourseNumber"
PropertyName="Text" Type="Int32" />
</asp:RangeExpression>
Other parameters support tying the RangeExpression to Session variables, entries in the QueryString, personalized values in the user's Profile, and even to keys in the Route used to retrieve the page. You can mix and match parameters so that your low value is provided by a Parameter tag, while your high value comes from a ControlParameter tag. You can also use these parameters with other filtering elements inside of QueryExtender.
Incorporate as many RangeExpression elements as you want into your QueryExtender to filter your data by multiple fields. You can also provide more than just two parameters within a RangeExpression element -- the RangeExpression just uses the highest and lowest values you provide.
Filtering on Properties and Methods
The PropertyExpression and MethodExpression elements work very much like the RangeExpression element. The PropertyExpression allows you to limit the data that will be passed to the DataView by specifying a value for a property. To filter the courses to the number of days each course takes, for instance, you can add one of the Parameter elements within the PropertyExpression and specify the name of the Property to test in the element's Name attribute. This example finds all the courses with four in their Days property:
<asp:PropertyExpression >
<asp:Parameter Type="Int32" DefaultValue="4"
Name="Days" />
</asp:PropertyExpression>
You can add multiple Parameter elements within a PropertyExpression or add multiple PropertyExpression elements within a QueryExtender. Either way, your criteria are ANDed together.
The MethodExpression provides a more flexible approach than the previous filters by allowing you to call LINQ queries embedded inside a method. For instance, you could extend the QueryExtender's page with this method:
Public Shared Function GetCoursesByDays( _
ByVal Courses As IQueryable(Of Course), _
ByVal MinDays As Integer) As IQueryable(Of Course)
Return From c In Courses
Where c.Days >= MinDays
Select c
End Function
This function demonstrates the requirements for any filtering method that you want to use: It must be a shared/static method, it must accept a "by value" IQueryable object as its first parameter, and it must return an IQueryable object. Your method can accept any number of parameters after that first IQueryable parameter.
Once you've created your filtering method, you tie your method to the QueryExtender with the MethodExpression element. This example calls my sample method, passing three to the method's MinDays parameter:
<asp:MethodExpression MethodName="GetCoursesByDays">
<asp:Parameter Type="Int32" Name="MinDays" Default
Value="3" />
</asp:MethodExpression>
The first parameter on the method called from the MethodExpression holds the objects returned by the DataSource. You must provide, within the MethodExpression, enough Parameter elements for all other parameters on the method. The order in which your Parameter elements appear doesn't have to match the order of the parameters on the method.
If you'd like to put your filter method in a different class, you can do that also. You'll need to add a Type attribute to your MethodExpression element with the name of your class. This example assumes that the method is on a class called FilterClass:
<asp:MethodExpression TypeName="FilterClass"
MethodName="GetCoursesByDays"...
You'd think you could then move your filter class to another class library. But when I tried this, I got a message that the first parameter passed to the method must be a Linq.DataQuery object and wasn't able to recover.
If you're going to put your filter code in your page's code file, use the CustomExpression element. It calls a method in your page, passing the usual event parameters: sender and e. Within the method you can retrieve any parameters that you've specified in the CustomExpression element from the e parameter's Values property. The e parameter also has a Query property that provides the data to filter and which accepts the result of your filtering code. This example assumes that the CustomExpression element has defined a parameter called MinDays and uses a LINQ query to filter the result:
Protected Sub QueryMethod( _
ByVal sender As Object, _
ByVal e As _
System.Web.UI.WebControls.Expressions.CustomExpressionEventArgs)
e.Query = From c In e.Query.Cast(Of Course)() _
Where c.Days >= Convert.ToInt32(e. _
Values("MinDays")) _
Select c
End Sub
The CustomExpression element needs to be told the name of the method to call, which you provide in the element's OnQuerying attribute. This example specifies the method shown above and uses a Parameter tag to define the MinDays parameter that my method requires:
<asp:CustomExpression OnQuerying="QueryMethod">
<asp:Parameter Name="MinDays" Type="Int32" Default
Value="3" />
</asp:CustomExpression>
Searching on Text String
For many developers, the SearchExpression element will be the most useful part of the QueryExtender. The SearchExpression provides a variety of ways to search on strings, checking for strings that begin with, end with or contain the user's text.
Like the other filter elements, SearchExpression allows you to tie the QueryExtender to multiple criteria sources using various Parameter elements. This example filters results going to the DataView using the content that the user enters into a TitleTextBox control. I've used the StartsWith option in the SearchType attribute so that I'll match Titles that begin with whatever the user types in:
<asp:SearchExpression DataFields="Title"
SearchType="StartsWith">
<asp:ControlParameter ControlID="TitleTextBox"
PropertyName="Text" Type="String" />
</asp:SearchExpression>
Empty controls are normally ignored by the SearchExpression. You can, however, incorporate empty controls into your search by treating them as Nulls: Just set the SearchExpression element's ConvertEmptyStringToNull attribute to true.
When working with strings you need to consider case and culture-sort rules. The ComparisonType attribute on the SearchExpression element lets you sort with and without case sensitivity, or even by using the Unicode numeric values of the characters.
Two additional elements that you can use with QueryExtender -- ControlFilterExpression and DynamicFilterExpression -- are supported only in ASP.NET Dynamic Data Web sites, but I'll leave that for another article.
Working with Code
The various expression tags are represented as System.Web.UI.WebControls.Expressions.DataSourceExpression objects in your code. You can retrieve the DataSourceExpressions in your QueryExtender by looping through them. The following example iterates through the DataSourceExpression in a QueryExtender looking for CustomExpression objects. When the code finds a CustomExpression, it loops through the DataSourceExpression's Parameters collection looking for basic Parameter objects so that it can set the Parameter's DefaultValue:
For Each dse As DataSourceExpression In _
Me.QueryExtender1.Expressions
If TypeOf dse Is CustomExpression Then
ce = CType(dse, CustomExpression)
For Each parm As Parameter In ce.Parameters
If TypeOf parm Is System.Web.UI.WebControls. _
Parameter Then
parm.Type = TypeCode.Int32
parm.DefaultValue = "3"
End If
Next
End If
Next
You can also request just the DataSourceExpressions of a particular type by using the OfType method on the QueryExtender's Expressions collection. This example retrieves all the CustomExpression parameters before looping through them:
Dim ces As System.Collections.Generic.IEnumerable( _
Of CustomExpression)
ces = Me.QueryExtender1.Expressions.OfType( _
Of CustomExpression)()
For Each ce As CustomExpression In ces ...
For a very dynamic search page, you can add and remove DataSourceExpressions from a QueryExtender at runtime. You can even add a QueryExtender to your page from code. I can add an OrderByExpression to a QueryExtender on the page by creating a System.Web.UI.WebControls.Expressions.OrderBy-Expression object and setting its properties. I can also add ThenBy objects to my SortExpression. Once I've got the OrderByExpression built, I add it to the Expressions collection on the QueryExtender, as shown in this example:
Dim ob As New OrderByExpression
ob.Direction = SortDirection.Descending
ob.DataField = "Days"
Dim tb As New ThenBy
tb.DataField = "Title"
tb.Direction = SortDirection.Ascending
ob.ThenByExpressions.Add(tb)
Me.QueryExtender1.Expressions.Add(ob)
You can also remove a DataSourceExpression using the RemoveAt method (passing the position of the DataSourceExpression to remove) or the Remove method (passing a reference to the DataSourceExpression to remove). With either method, asking for a DataSourceExpression that doesn't exist raises an error. You can use the Contains method on the Expressions collection to check to see if a particular DataSourceExpression is present.
With one exception, adding other DataSourceExpressions in code follows the same pattern as SortExpression. The exception is the CustomExpression, where you have to wire up your event method to the Querying event, as this code does:
Dim ce As New CustomExpression
AddHandler ce.Querying, AddressOf QueryMethodDynamic
Me.QueryExtender1.Expressions.Add(ce)
In Visual Basic there's no need to declare the variable with the WithEvents keyword. While, generally speaking, order doesn't matter when adding to the Expressions collection, the Insert method does allow you to add DataSourceExpressions at specific points in the collection.
A Strategic Extension
There are some features that I'd like to see added to the Query-Extender. It would be great if the QueryExtender also worked with the SqlDataDataSource and the ObjectDataSource. For dynamic runtime solutions, it would be convenient if DataSourceExpressions could be enabled and disabled in addition to being removed and added. And I'd love to have more flexibility than just ANDing them together when it comes to combining filtering expressions.
But the QueryExtender is more than just a useful tool -- it's a reflection of two of Microsoft's strategic intents in data access. First, the QueryExtender extends the declarative model that Microsoft is moving toward in data access. As with SQL, the QueryExtender allows you to declare what you want rather than have to specify the individual procedural steps required to get there. Second, the QueryExtender continues the process of providing a single interface to data. The point of DAO, ADO and ADO.NET was that you shouldn't care what database engine you're working with; the point of LINQ is you shouldn't even care whether your data is coming from an RDBMS. In the same way, the QueryExtender provides a single interface for declaratively selecting the data you -- or, more importantly, your users -- want, regardless of the DataSource. The QueryExtender is a strategic tool in addition to being a useful one.