Oleg Sych

Out of the box, ASP.NET Dynamic Data automatically generates search screens for entities based on their own columns. Here is an example of a search screen it generated for the Order table of the Northwind model from the previous article.

This screen contains controls for all columns of the Order table for which Dynamic Data can generate filter controls. This includes controls for boolean, enumeration and foreign key columns the framework provides out of the box, as well as the custom filter control for DateTime columns we have added to the application. Although in this example we still don’t have filter controls for other common data types, such as string and numeric columns, implementing them is very straightforward and similar to the implementation of our DateTimeFilter. The real limitation here is that we cannot search the Order table using filters based on related tables. For example, what if we need to give the users of our application the ability to search for orders placed by customers in a particular state or zip code? The short answer is that Dynamic Data doesn’t support filtering based on data in related tables. However, it is possible to make it cooperate. Here is the long answer…

The Approach

As usual with Dynamic Data, it is helpful to first work through a particular scenario using the traditional, static code. If we were to build a search screen for the Orders table using regular ASP.NET controls and the ADO.NET Entity Framework, we would create a page with text boxes, drop-down lists and other controls for users to enter search criteria and use LINQ to Entities to filter the query results, like so.

using (NorthwindEntities context = new NorthwindEntities())
{
    var query = from o in context.Orders
                where o.Customer.PostalCode == "33609"
                select o;
    Order[] results = query.ToArray();
}

Of course, this would not work very well if our page allowed a number of different criteria to be specified for the Customer (parent) table as well as for the Order (child) table. Let’s say that we have a dozen different filter controls on our search page for the user to choose from, but they only need to find orders placed by Customers with zip code 33609 and shipped to Ukraine. We would probably change the code to look more like this.

using (NorthwindEntities context = new NorthwindEntities())
{
    IQueryable<Customer> parentQuery = context.Customers;
    if (custZip.Text != string.Empty)
        parentQuery = parentQuery.Where(c => c.PostalCode == custZip.Text);
    if (custCountry.Text != string.Empty)
        parentQuery = parentQuery.Where(c => c.Country == custCountry.Text);

    IQueryable<Order> childQuery = context.Orders;
    if (shipZip.Text != string.Empty)
        childQuery = childQuery.Where(o => o.ShipPostalCode == shipZip.Text);
    if (shipCountry.Text != string.Empty)
        childQuery = childQuery.Where(o => o.ShipCountry == shipCountry.Text);

    var query = from customer in parentQuery
                join order in childQuery
                on customer.CustomerID equals order.CustomerID
                select order;

    Order[] results = query.ToArray();
}

This way we can check each of our filter controls and generate Where calls for the parent, Customers table, and for the child, Orders table, and then join the two queries to get the list of orders that meet the combined criteria. This approach takes advantage of the Where call chaining that ADO.NET Entity Framework and LINQ to SQL convert into individual conditions in the Where clause of SQL Select statement. Here is what the SQL query generated by this code would look like.

select Customers.*
from Customers
inner join Orders
on Orders.CustomerID = Customers.CustomerID
where Customers.PostalCode = ‘33609′
  and Orders.ShipCountry = ‘Ukraine’

Essentially, this is the same way the QueryableFilterRepeater control works in the List.aspx page template. It generates DynamicFilters for all columns that can be filtered in a table and calls their GetQueryable method allowing them to modify the search query based on the values entered in their filter controls.

<asp:QueryableFilterRepeater runat="server" ID="FilterRepeater">
  <ItemTemplate>
    <asp:Label runat="server" Text=’<%# Eval("DisplayName") %>‘
      OnPreRender="Label_PreRender" />
    <asp:DynamicFilter runat="server" ID="DynamicFilter"
      OnFilterChanged="DynamicFilter_FilterChanged" />
    <br />
  </ItemTemplate>
</asp:QueryableFilterRepeater>

What if we simply take this markup and its supporting code, put it in a new filter control, similar to the DateTime.aspx we created previously and implement its GetQueryable method  to do the join? Unfortunately, this straightforward approach doesn’t work in the 4.0 version of ASP.NET Dynamic Data.

The Problem

The reason this approach doesn’t work in the current version of ASP.NET Dynamic Data lies in the implementation of the QueryableFilterRepeater and DynamicFilter classes. The first stumbling block you would hit trying to make this work is how do you tell the QueryableFilterRepeater control that you want it to generate controls for a parent (Customer) table instead of the main (Order) table for which the page is generated? There are no public properties or methods that you could use to specify this information directly on these classes. The only way the framework provides is the DynamicDataManager, which requires you to create a data-bound control, such as a GridView or a FormView; associate it with the data manager; create a data source control, such as an EntityDataSource or a LinqDataSource; associate it with the data-bound control; create a QueryExtender control; associate it with the data source control; finally add a DynamicFilterExpression and associate it with the QueryableFilterRepeater.

Even if you get all this right, this is still not going to work in a filter control. The current implementation of the DynamicDataManager, QueryableFilterRepeater and DynamicFilter classes perform all of their dynamic control generation logic in Page.InitComplete event handlers. Unlike the Control.Init and Control.Load events, the page events are not automatically replayed for controls generated dynamically. When our custom filter control is initialized, the Page.InitComplete event has already fired, so no matter what we do, our DynamicDataManager, QueryableFilterRepeater and DynamicFilter simply will not initialize and will not work.

Why the ASP.NET team chose to use the Page.InitComplete event as opposed to Control.Init or Control.Load to implement filtering capabilities in Dynamic Data is beyond my understanding. If you do, please share your knowledge by posting a comment on this page; otherwise, consider voting on Microsoft connect to have this problem fixed.

The Workaround

Instead of relying on the built-in QueryableFilterRepeater and DynamicFilter controls, we can use the non-dynamic Repeater and PlaceHolder controls and directly access MetaModel.FilterFactory to dynamically create the appropriate QueryableFilterUserControl instances. Here is how the markup for our custom filter control could look.

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="ParentTable.ascx.cs"
  Inherits="DynamicDataDemo.ParentTableFilter" %>
<asp:Repeater ID="repeater" runat="server">
  <ItemTemplate>
    <asp:Label ID="label" runat="server" Text=’<%# Eval("DisplayName") %>‘/>
    <asp:PlaceHolder ID="placeHolder" runat="server"
      OnDataBinding="PlaceHolder_DataBinding"/>
    <br />
  </ItemTemplate>
</asp:Repeater>
<asp:EntityDataSource ID="parentDataSource" runat="server" />

Notice that we have an EntityDataSource control for the parent table. This control will not be used to query the database, only to access the metadata.

In the code-behind, we get the MetaTable instance that describes our parent table (i.e. Customer), initialize the parent data source control and bind the repeater to the list of parent columns that can be filtered. 

protected override void OnInit(EventArgs e)
{
    base.OnInit(e);

    var foreignKeyColumn = (MetaForeignKeyColumn)this.Column;
    MetaTable parentTable = foreignKeyColumn.ParentTable;

    parentDataSource.ContextType = parentTable.DataContextType;
    parentDataSource.EntitySetName = parentTable.DataContextPropertyName;

    this.repeater.DataSource = parentTable.GetFilteredColumns();
    this.repeater.DataBind();

    this.EntityDataSource.ContextCreated += this.EntityDataSource_ContextCreated;
}

The actual code that creates appropriate filter controls is located in the DataBinding event handler of the PlaceHolder control. This is where we use the Dynamic Data FilterFactory to create and initialize a new QueryableFilterUserControl and add it to the place holder.

protected void PlaceHolder_DataBinding(object sender, EventArgs e)
{
    FilterFactory filterFactory = this.Column.Model.FilterFactory;
    MetaColumn filterColumn = (MetaColumn)this.Page.GetDataItem();

    var control = (ExtendedFilterUserControl)filterFactory
        .CreateFilterControl(filterColumn, string.Empty);
    control.Initialize(filterColumn, this.parentDataSource, null);
    control.FilterChanged += delegate { this.OnFilterChanged(); };
    this.filterControls.Add(control);

    PlaceHolder placeHolder = (PlaceHolder)sender;
    placeHolder.Controls.Add(control);

    Label label = (Label)placeHolder.FindControl("label");
    label.Text = filterColumn.DisplayName;
    if (control.FilterControl != null)
    {
        label.AssociatedControlID = control.FilterControl
            .GetUniqueIDRelativeTo(label);
    }
}

If you are reading this code carefully, you will notice that we are actually using a custom class called ExtendedFilterUserControl. That is because QueryableFilterUserControl class does not provide any public means to associate it with a particular column and a data source. To work around this limitation, we need to subclass the QueryableFilterUserControl and make its internal Initialize method publically available. We also need to expose its internal QueryableDataSource property in order to generate LINQ join queries later.

public abstract class ExtendedFilterUserControl : QueryableFilterUserControl
{
    private static readonly MethodInfo initializeMethod =
        typeof(System.Web.DynamicData.QueryableFilterUserControl)
        .GetMethod("Initialize",
            BindingFlags.Instance | BindingFlags.NonPublic);

    private static readonly PropertyInfo dataSourceProperty =
        typeof(System.Web.DynamicData.QueryableFilterUserControl)
        .GetProperty("QueryableDataSource",
            BindingFlags.Instance | BindingFlags.NonPublic);

    protected EntityDataSource EntityDataSource
    {
        get
        {
            return (EntityDataSource)dataSourceProperty.GetValue(this, null);
        }
    }

    public void Initialize(MetaColumn metaColumn,
        IQueryableDataSource dataSource, HttpContextBase httpContext)
    {
        initializeMethod.Invoke(this,
            new object[] { metaColumn, dataSource, httpContext });
    }
}

This code uses reflection to access internal members of the QueryableFilterUserControl base class. This requires for the Dynamic Data application to be deployed with full trust, which increases security vulnerability of the web server and may not be possible in shared hosting environment. In order to take advantage of this new base class, we need to change all filter controls in the DynamicData\Filters folder of our project to inherit from ExtendedFilterUserControl.

Generating Joins Dynamically

If you remember, this is what an example of a static join query looked like with LINQ syntax.

using (NorthwindEntities context = new NorthwindEntities())
{
    IQueryable<Customer> parentQuery = context.Customers;
    // generate where calls for parent query …

    IQueryable<Order> childQuery = context.Orders;
    // generate where calls for child query …

    var query = from customer in parentQuery
                join order in childQuery
                on customer.CustomerID equals order.CustomerID
                select order;

    Order[] results = query.ToArray();
}

However, we will need to generate joins dynamically, using LINQ expressions, which first requires us to understand how to implement joins using LINQ extension methods. Here is the same query implemented using the Join extension method.

using (NorthwindEntities context = new NorthwindEntities())
{
    IQueryable<Customer> parentQuery = context.Customers;
    // generate where calls for parent query …

    IQueryable<Order> childQuery = context.Orders;
    // generate where calls for child query …

    IQueryable<Order> query = parentQuery
        .Join(childQuery,
            customer => customer.CustomerID,
            order => order.CustomerID,
            (customer, order) => order);

    Order[] results = query.ToArray();
}

Note that the on/equals clause of the LINQ join operator generates two lambda expressions called “key selectors”. A key selector takes an entity object, such as Customer, as a parameter and returns a “key value” that will be used to join the tables, such as CustomerID. The select clause of the LINQ join operator generates a lambda expression called “result selector”. A result selector takes two entities returned by the join and returns the entity that will be returned by the query, which in this case is Order.

This Join call is exactly what we need to generate dynamically in the GetQueryable method of our custom filter control.

private ObjectContext objectContext;
private List<ExtendedFilterUserControl> filterControls;

public override IQueryable GetQueryable(IQueryable sourceQuery)
{
    // Generate query against the parent table
    var foreignKeyColumn = (MetaForeignKeyColumn)this.Column;
    MetaTable parentTable = foreignKeyColumn.ParentTable;
    IQueryable parentQuery = parentTable.GetQuery(this.objectContext);
    IQueryable emptyParentQuery = parentQuery;
    foreach (var filterControl in this.filterControls)
    {
        parentQuery = filterControl.GetQueryable(parentQuery);
    }

    if (parentQuery != emptyParentQuery)
    {
        // Join the parent query with the main (child) query
        string primaryKeyColumnName = parentTable.PrimaryKeyColumns[0].Name;
        string foreignKeyColumnName = foreignKeyColumn.ForeignKeyNames[0];

        MethodCallExpression joinCall = Expression.Call(
            typeof(Queryable),
            "Join",
            new Type[] {
                    parentQuery.ElementType,
                    sourceQuery.ElementType,
                    this.Column.Table.GetColumn(foreignKeyColumnName).ColumnType,
                    sourceQuery.ElementType
                },
            new Expression[] {
                    parentQuery.Expression,
                    sourceQuery.Expression,
                    BuildKeySelector(parentQuery, primaryKeyColumnName),
                    BuildKeySelector(sourceQuery, foreignKeyColumnName),
                    BuildResultSelector(parentQuery, sourceQuery)
                }
        );
        sourceQuery = sourceQuery.Provider.CreateQuery(joinCall);
    }

    return sourceQuery;
}

In the GetQueryable method, we use MetaTable class to create an empty parent query; iterate through the list of the filter controls and call their GetQueryable methods to allow them to add their Where calls to the query. We then check the parent query to see if it was modified by any of the filter controls. If none of them modified the original parent query, i.e. if user has not entered any filter criteria for the parent table, we don’t have to do anything with the child (source) query. In fact, we don’t to modify the child query in this case because without parent filter criteria, we would only introduce an unnecessary join and reduce query performance.

Note that we pass an ObjectContext to the GetQuery method of the parent MetaTable. In a LINQ Join, both queries must come from the same ObjectContext (in LINQ to Entities) or DataContext (in LINQ to SQL). We can get the right context using the ContextCreated event of the EntityDataSource or the LinqDataSource control used to perform the query.

private ObjectContext objectContext;

protected override void OnInit(EventArgs e)
{
    base.OnInit(e);

    // …rest of the code… 

    this.EntityDataSource.ContextCreated += this.EntityDataSource_ContextCreated;
}

private void EntityDataSource_ContextCreated(object sender,
    EntityDataSourceContextCreatedEventArgs e)
{
    this.objectContext = e.Context;
}

And that is why we need to expose the internal QueryableDataSource property of QueryableFilterUserControl in our ExtendedFilterUserControl as shown above.

The code for BuildKeySelector and BuildResultSelector is relatively straightforward. The only counter-intuitive part you need to be aware of is that Expression.Lambda takes the body expression as it’s first parameter, followed by the parameter expressions.

private static LambdaExpression BuildKeySelector(IQueryable query,
    string columnName)
{
    var parameter = Expression.Parameter(query.ElementType);
    var property = Expression.Property(parameter, columnName);
    var selector = Expression.Lambda(property, parameter);
    return selector;
}

private static LambdaExpression BuildResultSelector(IQueryable parentQuery,
    IQueryable sourceQuery)
{
    var sourceParameter = Expression.Parameter(sourceQuery.ElementType);
    var parentParameter = Expression.Parameter(parentQuery.ElementType);
    var selector = Expression.Lambda(sourceParameter,
        parentParameter, sourceParameter);
    return selector;
}

The Result

Similar to other filter controls, all we need to do in order to take advantage of the new filter control is to apply a FilterUIHint to the appropriate foreign key columns in the model.

[MetadataType(typeof(OrderDetail.Metadata))]
public partial class OrderDetail
{
    public class Metadata
    {
        [FilterUIHint("ParentTable")]
        public object Order;
    }
}

Here is the search screen generated for the OrderDetail table before (left) and after (right) adding the FilterUIHint to the Order column.

As you can see, instead of a single drop-down list for selecting a single order, we now have a set of filter controls based on the columns of the parent (Order) table, including the custom DateTimeFilter controls we have implemented in the previous article. Obviously, there are several cosmetic problems, such as an orphaned “Order” label and a blank space between the parent table filter controls and the rest of the child table controls. We also don’t have the original ForeignKey filter control that was generated for the Order column before. For smaller reference tables, it may be beneficial to have the original foreign key filter in addition to the new parent filter controls, giving you the best of both worlds.

Fixing these problems is outside of scope of this article and is left as an exercise for the reader (tip: take a look at FilterUIHintAttribute.ControlParameters as one possible solution).

Download

Source Code 
See DynamicData\ExtendedFilterUserControl.cs and DynamicData\Filters\ParentTable.ascx.

• 2 Responses
• DotNetKicks
• Del.icio.us
• Digg
• Technorati