Chapter 5. Filter Templates
In This Chapter
• Building Search Pages with Filter Templates
• Filter Template Lookup Rules
Search functionality is essential for most database applications. Whether it is a customer browsing the product catalog or a service representative looking up a customer record, users need a fast and convenient mechanism for finding the information they need. For more than a decade now, ASP.NET developers have been using stored procedures to implement data access code in general and search functionality in particular. For each search screen, such as the Customer search page shown in Figure 5.1, one or more stored procedures would be created, each accepting a number of parameters representing the search criteria and executing an appropriate SELECT statement.
Figure 5.1. Customer Search page.
Although implementing a simple page using stored procedures is easy, complexity grows exponentially with the number of additional parameters in the search criteria. Consider the Customer search page shown in Figure 5.1. With only 2 search parameters, City and Title, this search page needs to support 3 different combinations of their values—only City, only Title, and both City and Title. This requires placing conditional logic either in the form of IF statements in the stored procedure body or in the form of logical conditions in the WHERE clause of the SELECT statement to handle each of the 3 combinations. With 3 search parameters, the number of combinations jumps to 7; with 4 parameters, it is already 15; and with only 5 parameters, (a very modest number for an advanced search page in a typical application), the number of possible combination is a formidable 31.
The number of possible combinations of parameter values is important for two reasons. On one hand, it directly affects the complexity of the database code developers have to write. But more importantly, conditional logic in queries and stored procedures defeats the query optimization logic the database engines use to improve performance. When a query designed to handle all 31 combinations of 5 parameters is executed for the first time, the probability of having the same combination of parameter values next can be as low as 3%, so the execution plan cached for the first query is likely to be invalid next time.
A better alternative to placing conditional logic in a static query code is to create the query dynamically, based on the specific combination of the parameter values. This approach leads to smaller, more focused queries that can be processed by the query optimizer effectively. Although dynamic queries can be created in the database and application code, this approach could open the application to SQL injection attacks if the developers are not careful to use parameters and not string concatenation to build the queries. This and the long entrenched assumption that dynamic SQL queries are slower than stored procedures are the reasons why database administrators often resist use of dynamic queries in internal applications.
Filtering with LINQ
Implementing search functionality became a lot easier with the advent of LINQ (Language Integrated Query) and LINQ-based data access frameworks, such as Entity Framework and LINQ to SQL. LINQ enables using SQL-like queries directly in C# and Visual Basic code:
using (NorthwindEntities context = new NorthwindEntities())
{
IQueryable<Customer> query = from c in context.Customers
where c.City == "London"
select c;
gridView.DataSource = customers.ToArray();
gridView.DataBind();
}
With the help of strongly typed classes, LINQ takes advantage of the compiler and eliminates the simple, but all too common, errors caused by misspelled table and column names in SQL queries. However, its biggest advantage is not the convenience of having the compiler check your code for typos. LINQ queries are first compiled into expression trees before they are translated into dynamic, parameterized SQL statements and sent to the database server for execution.
Although having the query compiled into an expression might seem like overhead at first, it offers two important advantages. On one hand, it protects the application against SQL injection attacks. Any malicious search string provided by the user, such as "'; drop table Customers;'" are passed to the database server as a parameter value and not as part of the query itself. On the other hand, LINQ expressions can be manipulated to customize the query. The following code snippet shows how you could implement filtering logic for this sample Customer search page using LINQ:
using (NorthwindEntities context = new NorthwindEntities())
{
IQueryable<Customer> customers = context.Customers;
if (this.cityFilter.Text.Length > 0)
customers = customers.Where(c => c.City == this.cityFilter.Text);
if (this.titleFilter.Text.Length > 0)
customers = customers.Where(c=>c.ContactTitle==this.titleFilter.Text);
gridView.DataSource = customers.ToArray();
gridView.DataBind();
}
This code uses the Where extension method instead of the where keyword. The first parameter of this generic method is an IQueryable<T> object, such as the IQueryable<Customer> in this example. An IQueryable<T> can be obtained from a regular LINQ query or directly from the Customers property of the context object. The second parameter of the Where method is a lambda expression, a mini-method that takes a Customer as a parameter and returns a Boolean value that indicates whether or not it matches the search condition.
LINQ allows you to compose queries from multiple parts. In this ongoing example, the Where method is first called if the user entered a search string for City, producing a SQL query that looks like this:
select * from Customers where City = @City
If the user enters a search string for Title, the second Where method is called, and the SQL query looks like the following:
select * from Customers where ContactTitle = @Title
Only if the user entered both City and Title search strings, both Where methods are called, and the SQL query includes conditions for both columns:
select * from Customers where City = @City and ContactTitle = @Title
In other words, with the help of LINQ, you can build focused queries that include only those conditions required for the search values supplied by the user. This helps the database server choose a more efficient execution plan than a typical hand-coded SQL (shown next) that tries to handle both search conditions and usually requires the database server to perform a full table scan:
select * from Customers
where City = ISNULL(@City, City)
and ContactTitle = ISNULL(@Title, ContactTitle)
Creating a Filter Template
A filter template is a user control that encapsulates both presentation and data access logic necessary to implement query filtering for a single column. In traditional search pages, this logic is usually spread over the markup and code-behind files. Consider the filtering logic required for the City column in our Customer search page (see the markup in Listing 5.1).
Listing 5.1. Simple Search Page (Markup)
<%@ Page Title="" Language="C#"
MasterPageFile="~/Site.master" CodeBehind="SamplePage.aspx.cs"
Inherits="WebApplication.Samples.Ch05.LinqFilter.SamplePage" %>
<asp:Content ContentPlaceHolderID="main" runat="server">
Customer City: <asp:TextBox runat="server" ID="cityFilter" />
Title: <asp:TextBox runat="server" ID="titleFilter" />
<asp:Button runat="server" Text="Find" OnClick="Find_Click" />
<asp:GridView ID="gridView" runat="server" />
</asp:Content>
The traditional page includes a TextBox control where the user can enter a value to search for in the City column. The data access logic for filtering rows based on this value is a part of the Find_Click event handler shown in Listing 5.2.
Listing 5.2. Simple Search Page (Code-Behind)
using System;
using System.Linq;
using DataModel;
namespace WebApplication.Samples.Ch05.LinqFilter
{
public partial class SamplePage : System.Web.UI.Page
{
protected void Find_Click(object sender, EventArgs e)
{
using (NorthwindEntities context = new NorthwindEntities())
{
IQueryable<Customer> customers = context.Customers;
if (this.cityFilter.Text.Length > 0)
{
customers = customers.Where(c => c.City == this.cityFilter.Text);
}
if (this.titleFilter.Text.Length > 0)
{
customers = customers.Where(c=>c.ContactTitle==this.titleFilter.Text);
}
this.gridView.DataSource = customers.ToArray();
this.gridView.DataBind();
}
}
}
}
To create a filter template for the City column, you need to extract both the markup and the code into a new user control and save it in the DynamicDataFilters folder of the web project. Note that the markup and code-behind for the ContactTitle column is nearly identical to that of the City column. The filter template created here works for both of these columns. Listings 5.3 and 5.4 show its markup and code-behind respectively.
Listing 5.3. Text Filter Template (Markup)
<%@ Control Language="C#" CodeBehind="Text.ascx.cs"
Inherits="WebApplication.DynamicData.Filters.TextFilter" %>
<asp:TextBox runat="server" ID="textBox" OnTextChanged="TextBox_TextChanged" />
You can use any of the built-in or third-party ASP.NET controls to implement user interface appropriate for entering search values for a data type the template is designed to handle. This template is designed for String columns. It uses a TextBox control similar to the one used in the sample search page earlier. The OnTextChanged event handler is used to refresh the query results when the user changes the search value.
Listing 5.4. Text Filter Template (Code-Behind)
using System;
using System.Linq;
using System.Linq.Dynamic;
using System.Web.DynamicData;
using System.Web.UI;
namespace WebApplication.DynamicData.Filters
{
public partial class TextFilter : QueryableFilterUserControl
{
public override IQueryable GetQueryable(IQueryable query)
{
if (!string.IsNullOrEmpty(this.textBox.Text))
{
string filter = this.Column.Name + " == @0";
query = query.Where (filter, this.textBox.Text);
}
return query;
}
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
this.textBox.MaxLength = this.Column.MaxLength;
this.textBox.Text = this.DefaultValue;
}
protected void TextBox_TextChanged(object sender, EventArgs e)
{
this.OnFilterChanged();
}
}
}
Filter templates inherit from a special base class provided by Dynamic Data called QueryableFilterUserControl. This class defines several virtual methods and properties that child classes need to override and offers several helper methods designed to help with implementation of concrete filter templates.
GetQueryable Method
The GetQueryable method is the heart of a filter template. It is defined as abstract in the QueryableFilterUserControl base class and must be overridden by all filter templates to implement the data access logic. This method receives a query object, an instance of IQueryable, which it needs to modify based on the search value entered by the user. If the user did not enter any search criteria for this particular column, the GetQueryable method should return the original query. Notice how the code in Listing 5.4 checks the contents of the TextBox control and modifies the query only if it is not empty.
OnFilterChanged Method
The OnFilterChanged method of the QueryableFilterUserControl base class notifies the data source control responsible for executing the query and fetching rows from the database that the query might have changed. The base class calls the GetQueryable method in response to the OnFilterChanged.
In this example, the OnFilterChanged method is called from the TextBox_TextChanged method, which handles the OnTextChanged event of the TextBox control. This ensures that any change to the search value entered in the text box is detected during the post-back, and the GetQueryable method is invoked to apply the filter.
DefaultValue Property
The DefaultValue property provides the initial filter value. When instantiated as part of the List page template, which is discussed in Chapter 6, “Page Templates,” filters can be initialized by passing column names and values through the page request URL. Although not required for new filter templates, supporting this functionality is important to make them behave consistently with the built-in templates provided by Dynamic Data project templates.
Dynamic LINQ Queries
Most filter templates, such as the Text.ascx in this example, can be reused for any column of a matching type. Dynamic filter templates, such as this, cannot hardcode the column name in the query. Instead, use the column metadata, available through the Column property of the QueryableFilterUserControl base class, to get the column name and build the query dynamically:
public override IQueryable GetQueryable(IQueryable query)
{
if (!string.IsNullOrEmpty(this.textBox.Text))
{
string filter = this.Column.Name + " == @0";
query = query.Where(filter, this.textBox.Text);
}
return query;
}
In this example, a dynamic version of the Where extension method is used. This method takes a string that represents a logical condition and converts it into a lambda expression LINQ needs to execute this query. Notice the use of the MetaColumn.Name property; it specifies the actual column name dynamically. With the City column, the filter string will be "City==@0", and with ContactTitle, it will be "ContactTitle==@0". @0 is a placeholder for the first parameter of the lambda expression, similar to parameters in dynamic SQL statements. The actual parameter value, TextBox.Text, is passed as the second argument in the call to the Where method.
The System.Linq.Dynamic namespace, which provided the dynamic version of the Where extension method in this filter template, is not a part of the .NET Framework. It is a C# sample project called DynamicQuery and can be found in the following folder of the Visual Studio installation:
Microsoft Visual Studio 10.0
Samples
1033
CSharpSamples.zip
LinqSamples
DynamicQuery
Before you can start using dynamic LINQ extensions in your project, you need to extract the sample project from the following folder on your computer and add it to your solution. When compiled, the assembly can be used in both C# and Visual Basic projects.
The dynamic LINQ extensions make the task of creating queries dynamically almost as easy as writing regular LINQ code. Compare the statically typed code shown here:
IQueryable<Customer> query = // ...
query = query.Where(customer => customer.City == "London");
IQueryable query = // ...
query = query.Where("City == @0", "London");
The dynamic Where method parses the string and creates a strongly-typed LINQ expression tree that can be used by a LINQ-enabled data access framework, such as the ADO.NET Entity Framework in this example. Following is the equivalent code that uses LINQ expression classes to accomplish the same result:
IQueryable query = // ...
ParameterExpression parameter = Expression.Parameter(
query.ElementType, string.Empty);
Expression property = Expression.PropertyOrField(parameter, "City");
Expression comparison = Expression.Equal(
property, Expression.Constant("London"));
LambdaExpression lambda = Expression.Lambda(
comparison, new ParameterExpression[] { parameter });
MethodCallExpression where = Expression.Call(
typeof(Queryable), "Where",
new Type[] { query.ElementType },
new Expression[] { query.Expression, Expression.Quote(lambda) });
query = query.Provider.CreateQuery(where);
Compared to raw expressions, dynamic LINQ extensions make implementing filter templates much, much easier. However, parsing strings requires additional CPU resources on the web server, and you might want to consider implementing filter templates with LINQ expressions as an optimization. You might also need to use LINQ expressions if the dynamic extensions do not support the LINQ functionality you need in your filter template, such as joins. However, this topic is too vast and complex to cover here; it is revisited in Chapter 12, “Building Custom Search Pages.”
Building Search Pages with Filter Templates
Now that you have implemented the filter template, Text.ascx, and saved it in the DynamicDataFilters folder of this web project, you can rewrite the sample search page to take advantage of its functionality. Compare Listing 5.5, which shows markup of the new version of this page, with Listing 5.1, which shows the original version.
Listing 5.5. Simple Search Page with DynamicFilter Controls (Markup)
<%@ Page Title="" Language="C#"
MasterPageFile="~/Site.master" CodeBehind="SamplePage.aspx.cs"
Inherits="WebApplication.Samples.Ch05.DynamicFilter.SamplePage" %>
<asp:Content ContentPlaceHolderID="main" runat="server">
Customer City:
<asp:DynamicFilter runat="server" ID="cityFilter"
DataField="City" FilterUIHint="Text" />
Title:
<asp:DynamicFilter runat="server" ID="titleFilter"
DataField="ContactTitle" FilterUIHint="Text" />
<asp:Button runat="server" Text="Find" />
<asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" />
<asp:EntityDataSource runat="server" ID="dataSource"
ConnectionString="name=NorthwindEntities"
DefaultContainerName="NorthwindEntities" EntitySetName="Customers" />
<asp:QueryExtender runat="server" TargetControlID="dataSource">
<asp:DynamicFilterExpression ControlID="cityFilter" />
<asp:DynamicFilterExpression ControlID="titleFilter" />
</asp:QueryExtender>
</asp:Content>
At run time, the new version of the page looks identical to the original shown in Figure 5.1. However, notice that the DynamicFilter controls are used instead of text boxes. DynamicFilter is a new ASP.NET control provided by Dynamic Data. At run time, it dynamically loads an appropriate filter template for the column specified in its DataField property. On this page, you have two instances of the DynamicFilter control, one for the City column and one for the ContactTitle.
Each DynamicFilter control must be ultimately connected to a LINQ-based data source control that queries the database. In this example, Entity Framework is used, and there is an EntityDataSource control on this page, configured to retrieve rows from the Customers table. EntityDataSource class inherits the QueryCreated event from its base class, QueryableDataSource. The control fires this event after it has created the initial LINQ query, an IQueryable instance, but before the query is converted to SQL and sent to the database server for execution. During this event, the query can be modified by the DynamicFilter controls.
In addition to the data source control, there’s also a QueryExtender control on the page. This control is associated with a QueryableDataSource by specifying its ID in the TargetControlID property of the query extender. At run time, the query extender handles the QueryCreated of the data source control and passes the IQueryable instance to every one of its data source expressions. This page uses two DynamicFilterExpression instances to associate both of the DynamicFilter controls with the QueryExtender. Each DynamicFilterExpression passes the query to its DynamicFilter, which in turn passes it to its filter template, Text.ascx, in this example.
Figure 5.2 illustrates how the whole process works, starting with the EntityDataSource on the left and ending with the filter template, a QueryableFilterUserControl, on the right.
Figure 5.2. Query modification sequence diagram.
It might seem this markup is more complex than the original version shown in Listing 5.1. However, keep in mind, unlike in the original page where there was a significant amount of data access logic hard-coded in the code-behind file, the new page has most of this logic in the markup, leaving the code-behind (see Listing 5.6) almost empty. Implementing filtering for additional columns is also easier. Should you need to support filtering for additional columns on this page, you can simply add more DynamicFilter controls to the markup without having to modify the code-behind.
Listing 5.6. Sample Search Page with DynamicFilter Controls (Code-Behind)
using System;
using System.Web.DynamicData;
namespace WebApplication.Samples.Ch05.DynamicFilter
{
public partial class SamplePage : System.Web.UI.Page
{
protected void Page_Init(object sender, EventArgs e)
{
MetaTable table = Global.DefaultModel.GetTable("Customers");
this.gridView.SetMetaTable(table);
}
}
}
The code-behind only needs to associate the required MetaTable object with the GridView control. This allows the DynamicFilter controls to locate the MetaColumn objects based on the column names specified in their DataField properties.
QueryableFilterRepeater Control
The DynamicFilter control is the right choice for implementing filtering functionality if you need to build a custom search page with specific search criteria. However, if you need to build an advanced search page where users can search for data using any of the supported columns or a dynamic search page that can work for different tables, you can also use the QueryableFilterRepeater control. Listing 5.7 shows how you could modify the search page to take advantage of it.
Listing 5.7. Search Page with QueryableFilterRepeater Control (Markup)
<%@ Page Title="" Language="C#"
MasterPageFile="~/Site.master" CodeBehind="SamplePage.aspx.cs"
Inherits="WebApplication.Samples.Ch05.QueryableFilterRepeater.SamplePage" %>
<asp:Content ContentPlaceHolderID="main" runat="server">
<asp:QueryableFilterRepeater runat="server" ID="filterRepeater">
<ItemTemplate>
<%# Eval("DisplayName") %>:
<asp:DynamicFilter ID="DynamicFilter" runat="server" />
</ItemTemplate>
</asp:QueryableFilterRepeater>
<asp:Button runat="server" Text="Find" />
<asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" />
<asp:EntityDataSource runat="server" ID="dataSource"
ConnectionString="name=NorthwindEntities"
DefaultContainerName="NorthwindEntities" EntitySetName="Customers" />
<asp:QueryExtender runat="server" TargetControlID="dataSource">
<asp:DynamicFilterExpression ControlID="filterRepeater" />
</asp:QueryExtender>
</asp:Content>
Similar to the regular Repeater control, QueryableFilterRepeater enables you to define a markup template that it will use to generate filtering controls for each supported column in the table. Each instance of the ItemTemplate it defines is bound to a MetaColumn object describing a column that can be filtered by Dynamic Data. This allows you to use ASP.NET data-binding syntax <%# Eval("DisplayName")%> to generate a text label for each column. Inside of the ItemTemplate, you also need to have a single instance of the DynamicFilter control that has ID property set to “DynamicFilter”. This allows the QueryableFilterRepeater to find the DynamicFilter control inside of each ItemTemplate instance and configure it based on the current MetaColumn; the QueryableFilterRepeater will initialize the filter control at run time.
The QueryableFilterRepeater control also needs to be connected to the QueryExtender with the help of the DynamicFilterExpression. Notice that in this example, there are no DynamicFilterExpression instances for the individual DynamicFilter controls, only one for the QueryableFilterRepeater. The DynamicFilterExpression calls the GetQueryable method of the QueryableFilterRepeater control, which in turn invokes the GetQueryable method of each DynamicFilter it created to modify the query.
The overall process of applying filters to a LINQ query, shown in Figure 5.3, is similar to the process involving only DynamicFilter controls reviewed earlier, except that here, a QueryableFilterRepeater is between a single DynamicFilterExpresssion and multiple DynamicFilter controls.
Figure 5.3. QueryableFilterRepeater query modification sequence diagram.
Notice that in addition to the DataField property of the DynamicFilter control, which the QueryableFilterRepeater initializes programmatically, the code in Listing 5.7 also does not specify the FilterUIHint property. For the Text filter template to work, this information needs to be specified in column metadata by applying the FilterUIHintAttribute to the property of the column. Listing 5.8 shows an example of doing that for the City and ContactTitle columns of the Customer entity.
Listing 5.8. Specifying FilterUIHintAttribute
using System.ComponentModel.DataAnnotations;
namespace DataModel
{
[MetadataType(typeof(Customer.Metadata))]
partial class Customer
{
public abstract class Metadata
{
[FilterUIHint("Text")]
public object City { get; set; }
[FilterUIHint("Text")]
public object ContactTitle { get; set; }
}
}
}
Filter Template Lookup Rules
Compared to the lookup rules for field templates, lookup rules for filter templates are simple. Dynamic Data uses only the FilterUIHint associated with the column and has an intrinsic knowledge of several column types. If the FilterUIHint is not specified in the data model, and the column is Foreign Key (a column that represents the parent navigation property of a many-to-one or a one-to-one association in the child entity), a Boolean, or an enumeration, Dynamic Data chooses a predefined FilterUIHint value, ForeignKey, Boolean, or Enumeration, respectively. Having the FilterUIHint value, Dynamic Data tries to locate a user control with the matching name in the DynamicDataFilters directory of the web application project. If the user control does not exist, the lookup process ends, and filtering is considered to be unsupported for this column. Figure 5.4 illustrates the algorithm used by Dynamic Data for filter template lookup.
Figure 5.4. Filter template lookup algorithm.
Built-in Filter Templates
Perhaps the reason why the lookup algorithm for filter templates is so simplistic compared to field templates is that only three filter templates come with a Dynamic Data web project by default.
Boolean
The Boolean filter template is used for columns of Boolean type. It contains a DropDownList control that allows the user to choose filter value from All, True, False, and [Not Set] if the column allows null values.
Enumeration
The Enumeration filter template is used for columns of enum types or integer columns marked with the EnumDataTypeAttribute that specifies an enum type. It contains a DropDownList control that allows users to choose filter value from All, [Not Set] (if the column allows null values), as well as the string names of all items in the enumerated type.
ForeignKey
The ForeignKey filter template is used for foreign key columns. It displays all records from the parent table referenced by the foreign key column in a DropDownList control, plus All and [Not Set] (if the column allows null values). Each ListItem represents a single row from the parent table. Its Value property contains a comma-separated list of primary key column values. The Text property value is determined by calling the GetDisplayString method of the MetaTable instance describing the parent table. If the parent entity class overrides the ToString method, the value returned by this method is used; otherwise, the value of the display column of the parent entity class is used. Display column can be specified explicitly, by applying the DisplayColumnAttribute to the entity class, or determined heuristically, in which case the value of the first string property is used.
Summary
A filter template is a special type of user control that encapsulates both user interface and data access logic required to filter rows of a particular table based on the values of a single column. Filter template modifies the LINQ query created by a data source control to apply the filter criteria dynamically, based on the search parameter entered by the user. Multiple instances of the same filter template can be used in an application for different columns of the same type. Dynamic Data includes a small number of ready-to-use filter templates, including templates for Boolean, enumeration, and foreign key columns. New filter templates can be implemented using built-in or third-party ASP.NET controls and LINQ expressions or dynamic LINQ extensions.