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

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

This topic shows how to filter the table rows to display by using a foreign key without requiring from you knowledge of the data source control and of the database query details. Dynamic Data generates the UI that enables the user to choose the column values for filtering the table rows and creates the necessary query information for the data source control in the page.

Dynamic Data provides a set of filter templates, in the ~\DynamicData\Filters directory, that are used to create the UI. You can also create custom filter templates for those column types that do not have a default template.

The following figure shows the steps that Dynamic Data follows in order to implement table row filtering by a foreign key.

Dynamic Data Table Row Filtering Elements

1. The DynamicFilter control passes the user's selected value to the QueryExtender control.

2. The QueryExtender control uses the value to create the query filtering information. Then it passes this information to the data source control.

3. The data source control passes the filtering query information to the data source provider.

4. The data source provider passes the query to the database.

5. The database returns the filtered table rows.

6. The data source provider sends this data back to the data source control.

7. This control finally passes the filtered table rows to the data-bound control for display.

To define the UI for table row filtering

1. In Visual Studio 2010 or Visual Web Developer 2010 Express, in a Dynamic Data Web site, open an ASP.NET Web page that contains a data source control to access the database and a related data-bound control to display the tables. For more information, see Walkthrough: Filtering Table Rows in Dynamic Data.

2. Before the data-bound control, add a DynamicFilter control to the page, as shown in the following example.

   <asp:DynamicFilter ID="DynamicFilter1" runat="server"/> 
   

3. Set the DataField property of the DynamicFilter control to the name of the column to use to filter the table rows.

   The following example shows the markup for a DynamicFilter control that specifies the name of the column to use for table row filtering.

   <asp:DynamicFilter ID="DynamicFilter1" 
     DataField="ProductCategory" runat="server"/>
   

   The DynamicFilter control automatically generates a DropDownList control for the chosen column. By default, only Boolean and foreign-key columns have filter templates. The DropDownList control enables the user to select column values for filtering the table rows.

   Note

   Dynamic Data will infer the filter template to use based on the column type. It will throw a InvalidOperationException if the column type does not have an associated default filter template.

To define the data source filtering information

1. Add a QueryExtender control to the page, as shown in the following example.

   <asp:QueryExtender ID="QueryExtender1" runat="server"/>
   
   </asp:QueryExtender>
   

   The QueryExtender control enables you to filter table rows from a data source by using simple page markup.

2. Set the TargetControlID property of the QueryExtender control to the identifier of the data source control.

   The following example shows the markup for a QueryExtender control that points to a data source control.

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

3. Add a DynamicFilterExpression object as a child of the QueryExtender control as shown in the following example.

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

4. Set the ControlID property of the DynamicFilterExpression control to the identifier of the DynamicFilter control. The latter control modifies the database query for the data source.

   The following example shows the markup for a QueryExtender control that instructs Dynamic Data to query the data source so that it returns all the table rows that contains the category value selected by the user.

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

Example

The following example uses the ForeignKey.ascx filter template to create a DropDownList control for the Products foreign-key column ProductCategory. When the user selects a value from this control, the example displays only the Products table rows that contain that category value.

Compiling the Code

This example requires the following:

• A Dynamic Data Web site or a Dynamic Data Web application. This enables you to create a data context for the database and to create the class for the table to access. For more information, see Walkthrough: Filtering Table Rows in Dynamic Data.

• The AdventureWorksLT sample database. For more information, see How to: Connect to the AdventureWorksLT Database using an .MDF File.

See Also

Tasks

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

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

Reference

DynamicFilter Web Server Control Declarative Syntax

QueryExtender Web Server Control Declarative Syntax