Walkthrough: Filtering Rows in Tables That Have a Parent-Child Relationship

Article
02/13/2013

This walkthrough shows how to filter rows in tables that have a parent-child relationship. Filtering table rows lets the user select (filter) the table rows to display based on a selected column value. It also specifies whether columns are displayed and in what order they are displayed.

The example shown in this topic uses tables from the AdventureWorks database. The example uses the child Product table that contains the foreign-key column ProductCategory that is defined in the parent table ProductCategories. The tables have a one-to-many relationship — that is, a single category can be associated with many products.

When the user selects a category in the parent table, Dynamic Data filters the rows in the child table, and only the rows that contain products that belong to the selected category are displayed.

This walkthrough illustrates the following tasks:

How to set the parent table for a foreign key. The setting includes a LinqDataSource control to access the database that contains the parent table. It also includes a GridView control to display the parent table and to enable the user to select the foreign-key value.
How to set the child table for displaying filtered rows. The setting includes a LinqDataSource control to access the database that contains the child table. It also includes a GridView control to display the filtered rows of the child table.
How to set filtering for rows in the child table. The QueryExtender control enables filtering of child rows based on the foreign key selected by the user in the parent table.

By default, Dynamic Data includes templates that let you perform filtering for Boolean, foreign-key, and enumeration column values. In these templates, filtering is defined declaratively by using the ASP.NET QueryExtender control.

The following illustration shows the steps that Dynamic Data performs in order to filter rows in tables that have a parent-child relationship.

Dynamic Data Query Elements

The figure illustrates the following sequence:

The parent data-bound control passes the user's selected foreign key to the QueryExtender control.
The QueryExtender control uses this foreign-key value to create the query filtering information. It then passes the filtering information to the data source control for the child rows.
The child data source control passes the filtering query information to the data source provider.
The data source provider passes the query to the database.
The database returns the filtered table rows.
The data source provider sends the rows back to the child data source control.
The child data source control finally passes the filtered table rows to the child data-bound control for display.

A Visual Studio project with source code is available to accompany this topic: Scaffolding Dynamic Data.

Prerequisites

Visual Studio 2010 or Visual Web Developer 2010 Express.
A Dynamic Data Web site. For more information, see Walkthrough: Creating a New Dynamic Data Web Site Using Scaffolding.
In the Dynamic Data Web site, a data model context object named AdventureWorksLTDataContext. This walkthrough assumes that you are working with a data context that has that name. If you use a data model context that has a different name, you must use a different name in some of the steps in this walkthrough.

Displaying the Parent Table

In this section you will set the parent table for selecting a foreign key. The page that you create includes a LinqDataSource control to access the database that contains the parent table. It also includes a GridView control to display the parent table and enable the user to select a foreign-key value.

To display the parent table

In Solution Explorer, right-click the project name, and then click Add New Item.
Under Installed Templates, select Visual Basic or Visual C#, and then in the right-hand pane, select Web Form.

In the Name box, enter ParentChild.aspx.
Switch to Design view.
From the Data tab of the Toolbox, add a DynamicDataManager control to the page.

The DynamicDataManager control must be included on a page in order to support Dynamic Data for data-bound controls. The markup for the DynamicDataManager control must precede the markup for any controls that use Dynamic Data. You will add the data-bound controls in later steps.
From the Data tab of the Toolbox, add a LinqDataSource control to the page.
From the LinqDataSource Tasks task menu, click Configure Data Source.

The Choose a Context Object dialog box is displayed.
In the Choose your context object list, select AdventureWorksLTDataContext.

Note

If the data model data context that you are working with has a different name, use that name in this step and in subsequent code that references the data context.
Click Next.

The Configure Data Selection dialog box is displayed.
In the Table list, select the ProductCategories table.
In the Select box, select the check box that has the asterisk (*).

This selects all the columns in the ProductCategories table.
Click Finish to close the Configure Data Selection dialog box.
From the Data group of the Toolbox, add a GridView control to the page.

This control is used to display the ProductCategories parent table.
From the GridView Tasks task menu, in the Choose Data Source list, select LinqDataSource1.

This is the ID of the data source control that you created in the previous steps. This step enables access to the ProductCategories parent table.
Select the options to enable paging and selection.
Switch to Source view.
In the Columns element, delete all the BoundField controls.
Above the GridView control, enter the following markup:
```
<h2>Parent Table: ProductCategories</h2>
```
This displays a caption that identifies the information that is being displayed.
Set the AutoGenerateColumns property to true.
Set the SelectedIndex property to a number. (The exact number you use is not important, except that it has to be less than or equal to the number of items that are displayed in the grid.).

The following example shows how to set the properties of the GridView control.
```
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
  AutoGenerateColumns="True" SelectedIndex="4" 
  DataSourceID="LinqDataSource1">
  <Columns>
    <asp:CommandField ShowSelectButton="True" />
  </Columns>
</asp:GridView>
```
Switch to Design view.
From the DynamicDataManager Tasks task menu, click Register Controls.

The DataControlReference Collection Editor is displayed.
In the Members pane, click Add.

A DataControl entry is displayed in the Members box.
In the DataControl properties pane in the ControlID list, select GridView1.
Click OK.
Save the ParentChild.aspx file
Right-click the page and select View in Browser.

The ProductCategories table is displayed.

Setting the Child Table for Displaying Filtered Rows

In this section you will set the child table for displaying filtered rows. The setting includes a LinqDataSource control to access the database that contains the child table. It also includes a GridView control to display the child table filtered rows.

To set the child table for displaying filtered rows

In the ParentChild.aspx file, switch to Design view.
From the Data tab of the Toolbox, add a LinqDataSource control to the page.
From the LinqDataSource Tasks task list, select Configure Data Source.

The Choose a Context Object dialog box is displayed.
In the Choose your context object list, select the AdventureWorksLTDataContext context object.
Click Next.

The Configure Data Selection dialog box is displayed.
In the Table list, select the Products table.
In the Select box, select the check box that has the asterisk (*).

This selects all the columns in the Products table.
Click Finish.
From the Data group of the Toolbox, add a GridView control to the page.

This control is used to display the Products table.
From the GridView Tasks menu in the Choose Data Source list, select LinqDataSource2.

This is the ID of the data source control that you created earlier. This step enables access to the Products child table.
Select the options to enable to enable paging and selection.
Switch to Source view.
In the Columns element, delete all the BoundField controls.
Above the GridView control, enter the following markup:
```
<h2>Child Table: Products</h2>
```
This displays a caption that identifies the information that is being displayed.
Set the AutoGenerateColumns property to true.
Set the SelectedIndex property to a number. (The exact number you use is not important, except that it has to be less than or equal to the number of items that are displayed in the grid.).

The following example shows how to set the related properties.
```
<asp:GridView ID="GridView2"  runat="server" 
  DataSourceID="LinqDataSource2" 
  AutoGenerateColumns="True"
  PageSize="5"> 
</asp:GridView>
```
Switch to Design view.
In the DynamicDataManager Tasks task list, click Register Controls.

The DataControlReference Collection Editor is displayed.
In the Members pane, click the Add button.

A DataControl entry is displayed in the Members box.
In the DataControl properties pane in the ControlID list, select GridView2.
Click OK.
Save the ParentChild.aspx file.
Right-click the page and select View in Browser.

The Products table is displayed.

Configuiring Filtering for the Child Table Rows

In this final section you will configure filtering for the child table rows. The QueryExtender control is configured to enable filtering for child table rows based on the foreign key that is selected by the user in the parent table.

To configure filtering for the child table rows

Switch to Source view.
In the ParentChild.aspx page, add a QueryExtender control.
Set the TargetControlID property of the QueryExtender control to the ID of the data source control that is associated with the child table whose rows you want to filter.

The following example shows the markup for a QueryExtender control that specifies the data source control LinqDataSource2 that is associated with the child table.
```
<asp:QueryExtender ID="QueryExtenderID" 
    TargetControlID="LinqDataSource2" runat="server" >
</asp:QueryExtender>
```
Add a ControlFilterExpression object as a child of the QueryExtender control.

The following example shows the markup for adding a ControlFilterExpression object to the control.
```
<asp:QueryExtender  ID="QueryExtenderID"
    TargetControlID="LinqDataSource2" runat="server">
  <asp:ControlFilterExpression />
</asp:QueryExtender>  
```
Set the ControlID property of the ControlFilterExpression object to the ID of the data-bound control that is associated with the parent table.
Set the Column property of the ControlFilterExpression object to the column in the parent table that is used for filtering.

This column represents a foreign key in the child table.

The following example shows the markup for a ControlFilterExpression object that points to a data-bound control and a foreign key that is associated with the parent table.
```
<asp:QueryExtender  ID="QueryExtenderID"
    TargetControlID="LinqDataSource2" runat="server">
  <asp:ControlFilterExpression
      ControlID="GridView1"
      Column="ProductCategory" />
</asp:QueryExtender>
```
Save the ParentChild.aspx file.
In Solution Explorer, right-click the ParentChild.aspx page and then select View in Browser.
In the ProductCategories parent table, select a category.

Dynamic Data filters the Products table rows based on the category that you selected.

Example

The following example shows the complete page that is illustrated in this walkthrough.

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="https://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    </div>
    <asp:DynamicDataManager ID="DynamicDataManager1" runat="server" >
    <DataControls>
        <asp:DataControlReference ControlID="GridView1" />
        <asp:DataControlReference ControlID="GridView2" />
    </DataControls>
    </asp:DynamicDataManager>
    <asp:LinqDataSource ID="LinqDataSource1" runat="server" 
        ContextTypeName="AdventureWorksLTDataContext" EntityTypeName="" 
        TableName="ProductCategories">
    </asp:LinqDataSource>

    <h2>Parent Table: ProductCategories</h2>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AutoGenerateColumns="True" DataKeyNames="ProductCategoryID"  
        SelectedIndex="4"
        DataSourceID="LinqDataSource1">
        <Columns>
            <asp:CommandField ShowSelectButton="True" />
        </Columns>
    </asp:GridView>


    <asp:LinqDataSource ID="LinqDataSource2" runat="server" 
        ContextTypeName="AdventureWorksLTDataContext" EntityTypeName="" 
        TableName="Products">
    </asp:LinqDataSource>

    <h2>Child Table: Products</h2>
    <asp:GridView ID="GridView2" runat="server" AllowPaging="True" 
        AutoGenerateColumns="True" DataKeyNames="ProductID" PageSize="5"
        DataSourceID="LinqDataSource2">
        <Columns>
            <asp:CommandField ShowSelectButton="True" />

        </Columns>
    </asp:GridView>

    <asp:QueryExtender ID="QueryExtenderID" 
        TargetControlID="LinqDataSource2" runat="server" >
             <asp:ControlFilterExpression
                ControlID="GridView1"
                Column="ProductCategory" />
    </asp:QueryExtender>


    </form>
</body>
</html>

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">


<html xmlns="https://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">

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

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

    <h2>Parent Table: ProductCategories</h2>
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
        AutoGenerateColumns="True" DataKeyNames="ProductCategoryID"
        PageSize="5" SelectedIndex="5" 
        DataSourceID="LinqDataSource1">
        <Columns>
            <asp:CommandField ShowSelectButton="True" />
        </Columns>
    </asp:GridView>

    <asp:LinqDataSource ID="LinqDataSource2" runat="server" 
        ContextTypeName="AdventureWorksLTDataContext" EntityTypeName="" 
        TableName="Products">
    </asp:LinqDataSource>

    <h2>Child Table: Products</h2>

    <asp:GridView ID="GridView2" runat="server" AllowPaging="True" 
        AutoGenerateColumns="True" DataKeyNames="ProductID"
        PageSize="5" 
        DataSourceID="LinqDataSource2">
        <Columns>
            <asp:CommandField ShowSelectButton="True" />

        </Columns>
    </asp:GridView>

    <asp:QueryExtender ID="QueryExtenderID" 
     TargetControlID="LinqDataSource2" runat="server">
     <asp:ControlFilterExpression ControlID="Gridview1" Column="ProductCategory" /> 
    </asp:QueryExtender>


    </form>
</body>
</html>

Next Steps

This walkthrough has illustrated the basic principles of how to enable automatic data filtering between tables that have a parent-child relationship. You might want to experiment with additional features. Suggestions for additional exploration include the following:

Learn how to enable table-row filtering using a foreign key in the same table where filtering is performed. For more information and example, see DynamicFilter.
Learn how to enable table-row filtering using the supported column types (Boolean, foreign-key, and enumeration column values). For more information and example, see QueryableFilterRepeater.
Learn how to customize a specific table layout. For example, you can customize the layout for a specific table to create 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 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).