Walkthrough: Filtering Table Rows in Dynamic Data

MSDN Library

Design Tools
Development Tools and Languages
Mobile and Embedded Development
.NET Development
Office Development
Online Services
Open Specifications
patterns & practices
Servers and Enterprise Development
Speech Technologies
Web Development
Win32 and COM Development

Walkthrough: Filtering Table Rows in Dynamic Data
This page is specific to:.NET Framework Version: 4

[This documentation is for preview only, and is subject to change in later releases. Blank topics are included as placeholders.]

This walkthrough shows how to perform table row filtering in an ASP.NET Dynamic Data Web site. Filtering lets users display subsets of the data that is displayed by Dynamic Data, which you can implement without creating and executing new queries each time the user selects a new filter value. By default, Dynamic Data includes templates that let you specify filtering for Boolean values and for foreign-key values. For foreign-key values, Dynamic Data uses filtering to display the related rows for the foreign-key value that the user selects.

In this walkthrough you will build an application that displays filtered data from the AdventureWorksLT sample database in the following ways:

Filter table rows using a foreign key. You will create the page markup to let Dynamic Data generate the UI and perform row filtering for a foreign-key column. You use this approach when you want to filter rows for a column type for which Dynamic Data provides a default filter template. (If you want to support filtering for a column type for which Dynamic Data does not provide a default filter template, you need to create a custom filter template.)
Filter table rows using default filter templates. You create the page markup to let Dynamic Data automatically generate the UI and perform row filtering for the column types for which default filter templates exist. By default, Dynamic Data can create the UI for Boolean and foreign-key columns.
Test data filtering. This lets you make sure that Dynamic Data table row filtering capabilities have been integrated in the Web site.

You can download a complete application that contains the examples described in this topic: Download_VB, Download_CS.

Prerequisites

In order to build the application and run the examples in this topic, you need the following:

A Dynamic Data Web site or a Dynamic Data Web application. This enables you to create the AdventureWorksLTDataContext data context for the database and to create the classes for the tables to access. This walkthrough assumes that you have created the Web site and that you are using a data context that is based on LINQ to SQL. For more information, see Walkthrough: Creating a New Dynamic Data Web Site using Scaffolding.
The AdventureWorksLT sample database. For more information, see How to: Connect to the AdventureWorksLT Database using an .MDF File.

Filtering Table Rows Using a Foreign Key

This section shows how to create a Web page that let the user filter table rows by using a foreign key. The page contains a LinqDataSource control to interact with the database and a GridView control to display the filtered table rows. The page uses the LINQ to SQL database model, as illustrated in the topic Walkthrough: Creating a New Dynamic Data Web Site using Scaffolding.

To filter table rows using a foreign key

In Solution Explorer, right-click the project name, and then select Add New Item.
Under Visual Studio installed templates, select Web Form.
In the Name box, enter ForeignKeyTableRowFiltering.aspx. Make sure that you select Place code in separate file.
Add a DynamicDataManager control to the page, as shown in the following example:
```
<body>
  <form ID="Form1" runat="server">
    <asp:DynamicDataManager ID="DynamicDataManager1"
      runat="server"/>
  </form>
</body>
```
The DynamicDataManager control must be included on a page in order to support Dynamic Data controls. The markup for the DynamicDataManager control must precede the markup for any controls that use Dynamic Data.

From the Data tab of the Toolbox, add a LinqDataSource control to the page, as shown in the following example:

<form ID="Form1" runat="server">
  <asp:LinqDataSource ID="LinqDataSource1" runat="server" 
  </asp:LinqDataSource>
</form>

Set the LinqDataSource control's TableName property to the database table to access (Products), as shown in the following example:
```
<asp:LinqDataSource ID="LinqDataSource1" runat="server" 
  TableName="Products"
</asp:LinqDataSource>
```

Set the ContextTypeName property to the data context class (AdventureWorksLTDataContext), as shown in the following example:

<asp:LinqDataSource ID="LinqDataSource1" runat="server" 
  ContextTypeName="AdventureWorksLTDataContext">
</asp:LinqDataSource>

From the Data tab of the Toolbox, add a GridView control to the page.
This control will display the Products table data fields. Set the DataSourceID property to the ID of the LinqDataSource control, as shown in the following example:
```
<form ID="Form1" runat="server" >
  
  <asp:GridView ID="GridView1"  runat="server" 
    DataSourceID="LinqDataSource1" >
  </asp:GridView>

</form>
```
Set the GridView control's AutoGenerateColumns property to false, as shown in the following example:
```
<asp:GridView ID="GridView1"  runat="server" 
  AutoGenerateColumns="false" >
</asp:GridView>
```
This disables the automatic generation of columns that are based on the database table. Instead you will use a DynamicField controls to populate the GridView control.
Optionally, set the GridView control's AllowPaging and AllowSorting properties to true, as shown in the following example:
```
<asp:GridView ID="GridView1"  runat="server" 
  AllowPaging="true"
  AllowSorting="true" >
</asp:GridView>
```
Add DynamicField controls to the Columns property and set their DataField properties to "ProductCategory", "Name", "Color", and "Size".
DynamicField controls use ASP.NET Dynamic Data to read data from the data model and to format it by using the appropriate field templates. The following example shows the markup for the Columns element and the DynamicField controls:
```
<asp:GridView ID="GridView1" runat="server">
  
  <Columns>
    <asp:DynamicField DataField="ProductCategory" />
    <asp:DynamicField DataField="Name" />
    <asp:DynamicField DataField="Color" />
    <asp:DynamicField DataField="Size" />
  </Columns>
</asp:GridView>
```

<asp:DynamicDataManager ID="DynamicDataManager1"
  runat="server">
  <DataControls>
    <asp:DataControlReference ControlID="GridView1"/>
  </DataControls>
</asp:DynamicDataManager>

In the markup above the data-bound control, add a DynamicFilter control to the page.
Set the DataField property of the DynamicFilter control toProductCategory.
This is the name of the column to use to for table row filtering. The following example shows the markup for the DynamicFilter control.
```
<asp:DynamicFilter ID="DynamicFilter1" 
  DataField="ProductCategory" runat="server"/>
```
Dynamic Data throws an InvalidOperationException error if the column type does not have an associated filter template.
In the markup above the DynamicFilter control, add a Label control, as shown in the following example:
```
<asp:Label ID="Label1" 
  Text="ProductCategory  " runat="server"/>
```
You will use this Label control to display the name of the column that is used for filtering.
Add a QueryExtender control to the page.
Set the TargetControlID property of QueryExtender control to the ID of the data source control that you want to extend, as shown in the following example:
```
<asp:QueryExtender ID="QueryExtenderID" 
  TargetControlID="LinqDataSource1" runat="server"/>
</asp:QueryExtender>
```
The QueryExtender control extends a data source control's capabilities by letting you configure data filtering through declarative syntax, as shown in the next steps.
Add a DynamicFilterExpression object as a child of the QueryExtender control.

Set the ControlID property of the DynamicFilterExpression object to the identifier of the DynamicFilter control, as shown in the following example:

<asp:QueryExtender ID="QueryExtenderID" 
    TargetControlID="LinqDataSource1" runat="server">
    <asp:DynamicFilterExpression ControlID="DynamicFilter1"/> 
</asp:QueryExtender>

Save and close the file.
In Solution Explorer, right-click the ForeignKeyTableRowFiltering.aspx page and then select View in Browser.
The page displays the columns from the Products and the UI for filtering the products by category.
In the Product Category drop-down control, select a category.
Dynamic Data filters the products that are displayed, based on the category that you selected.
Close the ForeignKeyTableRowFiltering.aspx page.

Filtering Table Rows Using Default Filter Templates

This section shows how to create a Web page that lets the user filter table rows for the column types for which default filter templates exist. The page contains a LinqDataSource control to interact with the database and a GridView control to display the filtered table rows. The page uses the LINQ to SQL database model, as illustrated in the Walkthrough: Creating a New Dynamic Data Web Site using Scaffolding.

To filter table rows using default filter templates

As a shortcut, instead of recreating a new page, in Solution Explorer right-click the ForeignKeyTableRowFiltering.aspx file and then select Copy.
Right-click the project folder and then select Paste.
Right click the Copy of ForeignKeyTableRowFiltering.aspx page and then select Rename.
Rename the file DefaultTableRowFiltering.aspx.
Open the DefaultTableRowFiltering.aspx file.
Add one more DynamicField control to the Columns property and set the DataField property to "ProductModel".
The ProductModel and the ProductCategory columns are the two foreign keys for which Dynamic Data automatically create a UI to filter table rows. The following example shows the markup for the GridView control after you add the new DynamicField control.
```
<asp:GridView ID="GridView1" runat="server">
  
  <Columns
    <asp:DynamicField DataField="ProductModel" />
    <asp:DynamicField DataField="ProductCategory" />
    <asp:DynamicField DataField="Name" />
    <asp:DynamicField DataField="Color" />
    <asp:DynamicField DataField="Size" />
  </Columns>
</asp:GridView>
```
In the markup before the data-bound control, replace the DynamicFilter control, add the Label control with a QueryableFilterRepeater control, as shown in the following example.
```
<asp:QueryableFilterRepeater ID="FilterRepeater1" runat="server" >
</asp:QueryableFilterRepeater> 
```
In the ItemTemplate element of the QueryableFilterRepeater control, add a DynamicFilter control, as shown in the following example:
```
<asp:QueryableFilterRepeater ID="FilterRepeater1" runat="server" >
  <ItemTemplate>
    <asp:DynamicFilter ID="DynamicFilter" runat="server" />
  </ItemTemplate>
</asp:QueryableFilterRepeater> 
```
The DynamicFilter control automatically generates a DropDownList control for each column type that has a related default filter template. By default, only Boolean and foreign-key columns have filter templates. The DropDownList control enables the user to select the column value for filtering the table rows.

Add a Label control before the DynamicFilter control.

Dynamic Data uses the Label control to display a name for any column that can be used for data filtering.

The following example shows the markup for a Label that displays the names of the columns to use for data filtering.

<asp:QueryableFilterRepeater ID="FilterRepeater1" runat="server" >
  <ItemTemplate>
    <asp:Label ID="Label1" runat="server" 
      Text='<%# Eval("DisplayName") %>: ' />
    <asp:DynamicFilter ID="DynamicFilter" 
      runat="server" />
  </ItemTemplate>
</asp:QueryableFilterRepeater>

In the QueryExtender control, set the DynamicFilterExpression ControlID property to the ID of the QueryableFilterRepeater control, as shown in the following example:

<asp:QueryExtender ID="QueryExtender1" 
    TargetControlID="LinqDataSource1" runat="server">
    <asp:DynamicFilterExpression 
      ControlID="FilterRepeater1"/> 
</asp:QueryExtender>

Save and close the file.
In Solution Explorer, right-click the DefaultTableRowFiltering.aspx page and then select View in Browser.
The page displays the defined Products columns and the UI for filtering the products by category and by model.
In the ProductCategory drop-down control, select a category.
Dynamic Data filters the products displayed based on the category you selected.
In the ProductModel drop-down control, select a model.
Dynamic Data filters the products displayed based on the model you selected.
Close the browser.

Testing Table Row Filtering

You can now test and compare the table row filtering pages that you just created. This lets you make sure that Dynamic Data filtering capabilities have been integrated in the Web site and compare the methods used. You can interact with the database and verify that the application works as expected.

To test and compare table row filtering

In Solution Explorer, right-click the ForeignKeyTableRowFiltering.aspx page and then select View in Browser.
The page displays the defined Products columns and the UI for filtering the products by category. Notice that this is the column that you specifically configured to create the UI for table row filtering.
Close the ForeignKeyTableRowFiltering.aspx page.
In Solution Explorer, right-click the DefaultTableRowFiltering.aspx page and then select View in Browser.
The page displays the defined Products columns and the UI for filtering the products by category and by model. Notice that Dynamic Data has automatically created a UI for category and model filtering. This shows how you can use declarative syntax to instruct Dynamic Data to generate the UI and table row filtering for all the column types for which a default filter template exists.
Close the browser.

Next Steps

This walkthrough has illustrated the basic principles of how to add data filtering to an ASP.NET Dynamic Data Web site. You have seen how to enable data filtering using a specific column or automatically. You might want to experiment with additional features. Suggestions for additional exploration include the following:

Learn how to enable data-key filtering. This enables the user to filter table rows based on the column value from a parent table. For more information, see How to: Filter Table Rows Using Values from a Parent Table in Dynamic Data.
Learn how to customize a specific table layout. This is to customize the layout for a specific table that could allow for custom UI for data filtering. For more information, see Walkthrough: Customizing Table Layout Using Entity Templates.

For general information, you might want to do the following:

Understand how to use the O/R Designer in a Web application to create data classes that represent a SQL Server database. For more information, see How to: Create LINQ to SQL Classes in a Web Application.
Understand LINQ-to-SQL Object-Relational mapping. For more information, see LINQ to SQL.
Understand the Object Relational Designer (O/R Designer). For more information, see Object Relational Designer (O/R Designer).

Tasks

How to: Filter Table Rows Using Values from a Parent Table in Dynamic Data

How to: Filter Table Rows Using Foreign Key in Dynamic Data

How to: Filter Table Rows Using Default Filter Templates in Dynamic Data

Concepts

ASP.NET Dynamic Data Overview

ASP.NET Dynamic Data Infrastructure