How to: Filter Table Rows Using Values from a Parent Table in Dynamic Data
[This documentation is for preview only, and is subject to change in later releases. Blank topics are included as placeholders.]
ASP.NET Dynamic Data enables you to use page markup to filter the child table rows to display based on the foreign-key values that the user selects in a parent table. When the user selects a value in the parent table only the child table rows that contain that value are displayed. This lets you perform data filtering without requiring knowledge of the data source control and of the database query details.
The following figure shows the steps that Dynamic Data follows in order to implement table row filtering
Dynamic Data Child Table Rows Filtering Elements
.png "Dynamic Data Query Elements")
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 this filtering information to the child data source control.
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 this data back to the child data source control.
This control finally passes the filtered table rows to the child data-bound control for display.
To filter table rows using values from a parent table
In Visual Studio 2010 or Visual Web Developer 2010 Express, in a Dynamic Data Web site, open an ASP.NET Web page. 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.
Add a QueryExtender control to the page and set its TargetControlID property 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 LinqDataSource1 associated with the child table.
<asp:QueryExtender ID="QueryExtender1" TargetControlID="LinqDataSource1" 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 TargetControlID="LinqDataSource1" runat="server"> <asp:ControlFilterExpression /> </asp:QueryExtender>Set the ControlID property of the ControlFilterExpression object to the data-bound control 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 associated with the parent table.
<asp:QueryExtender TargetControlID="LinqDataSource1" runat="server"> <asp:ControlFilterExpression ControlD="ParentGridView" Column="ProductCategory" /> </asp:QueryExtender>
Example
The following example shows how to filter child table rows by using a foreign-key value from a parent table. The example uses the ProductCategories and Products tables from the AdventureWorksLT database. These tables have a master-detail relationship. In the example, the Products table rows are filtered based on the ProductCategory value that is assigned by the user in the ProductCategories table.
<%@ Page Language="VB" AutoEventWireup="true"
CodeFile="TableRowsDataKeyFiltering.aspx.vb"
Inherits="Samples_TableRowsDataKeyFiltering" %>
<!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>Parent Foreign Key Table Row Filtering</title>
</head>
<body>
<h3>Parent Foreign Key Table Row Filtering</h3>
<form id="form1" runat="server">
<div>
<asp:DynamicDataManager ID="DynamicDataManager1" runat="server">
<DataControls>
<asp:DataControlReference ControlID="GridView1" />
<asp:DataControlReference ControlID="GridView2" />
</DataControls>
</asp:DynamicDataManager>
<h4>Parent Table: <%=LinqDataSource2.GetTable().DisplayName%></h4>
Select a category in the
<code><%=LinqDataSource2.GetTable().DisplayName%></code>
parent table to filter the rows in the
<code><%=LinqDataSource1.GetTable().DisplayName%></code>
child table.
<asp:GridView ID="GridView2" runat="server"
DataSourceID="LinqDataSource2"
AutoGenerateColumns="false"
AutoGenerateSelectButton="true"
AllowPaging="True" AllowSorting="True">
<Columns>
<asp:DynamicField DataField="Name" />
</Columns>
</asp:GridView>
<aspX:LinqDataSource ID="LinqDataSource2" runat="server"
ContextTypeName="AdventureWorksLTDataContext"
TableName="ProductCategories">
</aspX:LinqDataSource>
<hr />
<h4>Child Table: <%=LinqDataSource1.GetTable().DisplayName%></h4>
<asp:GridView ID="GridView1" runat="server"
DataSourceID="LinqDataSource1" AutoGenerateColumns="false"
AllowPaging="true" AllowSorting="true">
<Columns>
<asp:DynamicField DataField="Name" />
<asp:DynamicField DataField="Color" />
<asp:DynamicField DataField="ProductCategory" />
</Columns>
</asp:GridView>
<aspX:QueryExtender ID="QueryExtender1"
TargetControlID="LinqDataSource1" runat="server">
<asp:ControlFilterExpression
ControlID="GridView2"
Column="ProductCategory" />
</aspX:QueryExtender>
<aspX:LinqDataSource ID="LinqDataSource1" runat="server"
TableName="Products"
ContextTypeName="AdventureWorksLTDataContext"/>
</div>
</form>
</body>
</html>
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="TableRowsDataKeyFiltering.aspx.cs"
Inherits="Samples_TableRowsDataKeyFiltering" %>
<!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>Parent Foreign Key Table Row Filtering</title>
</head>
<body>
<h3>Parent Foreign Key Table Row Filtering</h3>
<form id="form1" runat="server">
<div>
<asp:DynamicDataManager ID="DynamicDataManager1" runat="server">
<DataControls>
<asp:DataControlReference ControlID="GridView1" />
<asp:DataControlReference ControlID="GridView2" />
</DataControls>
</asp:DynamicDataManager>
<h4>Parent Table: <%=LinqDataSource2.GetTable().DisplayName%></h4>
Select a category in the
<code><%=LinqDataSource2.GetTable().DisplayName%></code>
parent table to filter the rows in the
<code><%=LinqDataSource1.GetTable().DisplayName%></code>
child table.
<asp:GridView ID="GridView2" runat="server"
DataSourceID="LinqDataSource2"
AutoGenerateColumns="false"
AutoGenerateSelectButton="true"
AllowPaging="True" AllowSorting="True">
<Columns>
<asp:DynamicField DataField="Name" />
</Columns>
</asp:GridView>
<aspX:LinqDataSource ID="LinqDataSource2" runat="server"
ContextTypeName="AdventureWorksLTDataContext"
TableName="ProductCategories">
</aspX:LinqDataSource>
<hr />
<h4>Child Table: <%=LinqDataSource1.GetTable().DisplayName%></h4>
<asp:GridView ID="GridView1" runat="server"
DataSourceID="LinqDataSource1" AutoGenerateColumns="false"
AllowPaging="true" AllowSorting="true">
<Columns>
<asp:DynamicField DataField="Name" />
<asp:DynamicField DataField="Color" />
<asp:DynamicField DataField="ProductCategory" />
</Columns>
</asp:GridView>
<aspX:QueryExtender ID="QueryExtender1"
TargetControlID="LinqDataSource1" runat="server">
<asp:ControlFilterExpression
ControlID="GridView2"
Column="ProductCategory" />
</aspX:QueryExtender>
<aspX:LinqDataSource ID="LinqDataSource1" runat="server"
TableName="Products"
ContextTypeName="AdventureWorksLTDataContext"/>
</div>
</form>
</body>
</html>
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.DynamicData
Imports System.Collections.Specialized
Partial Public Class Samples_TableRowsDataKeyFiltering
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Dim table As MetaTable = LinqDataSource2.GetTable()
Title = table.DisplayName
' Set the primary key to a category
' for which child values exist.
Dim TouringBikes As Integer = 7
Dim values As New OrderedDictionary()
values.Add("ProductCategoryID", TouringBikes)
Dim keyNames As String() = {"ProductCategoryID"}
Dim key As New DataKey(values, keyNames)
Dim tmpGrid As IPersistedSelector = _
DirectCast(GridView2, IPersistedSelector)
tmpGrid.DataKey = key
End Sub
End Class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.DynamicData;
using System.Collections.Specialized;
public partial class Samples_TableRowsDataKeyFiltering : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
MetaTable table = LinqDataSource2.GetTable();
Title = table.DisplayName;
// Set the primary key to a category
// for which child values exist.
int TouringBikes = 7;
OrderedDictionary values = new OrderedDictionary();
values.Add("ProductCategoryID", TouringBikes);
string[] keyNames = { "ProductCategoryID" };
DataKey key = new DataKey(values, keyNames);
((IPersistedSelector)GridView2).DataKey = key;
}
}
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 tables to access.
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 Foreign Key in Dynamic Data
How to: Filter Table Rows Using Default Filter Templates in Dynamic Data