Querying a Table By a Foreign Key using Dynamic Data Filter

Summary: This post shows how to query a child table using a column from a parent table in dynamic data filtering.  It shows how to use the ForeignKey filter template to query the products using the Category data field. The ForeignKey filter template is specified declaratively. This is because it must override the MultiForeignKey template specified in the partial class or data model. 

Note: You specify the filter template in the data model when you want to apply it globally to your application. You specify it in a page when you want to apply it just to that page. 

The ForeignKey filter template displays a drop-down list. This enables the user to select a category when displaying the products. By default all the categories are selected so that all the products are displayed. The ForeignKey filter template enables the selection of only one category at the time. By contrast, the MultiForeignKey filter template enables the selection of multiple categories.
The Category data field, to which the filter is applied, represents a foreign key (FK). As consequence, the ForeignKey filter obtains its selection values from the Category table.

The following are the key steps to filter the products by Category:

  • Declare the filter template to reference. You do this using the <asp:DynamicFilter> control as follows:
 <asp:DynamicFilter  runat="server" ID="CategoryFilterID"  Filter="ForeignKey"
    DataField="Category" OnFilterChanged="OnFilterSelectedIndexChanged" />

  • Add the <asp:DynamicFilterExpression> as an element of the <asp:QueryExtender> control. Set its ControlID property to point to the previously declared  filter as follows:
 <aspX:QueryExtender ID="QueryExtender1" TargetControlID="GridDataSource" runat="server">
      <asp:DynamicFilterExpression ControlID="CategoryFilterID" />

For a complete example, see the attached files.