Monday 31 May 2010

Part 2 – A Cascading Hierarchical Field Template & Filter for Dynamic Data

In this second part we are going to complete the field templates by adding the CascadeHierarchical_Edit field template (see Figure 1) and in the next adding a CascadeHierarchical filter.

Starting with a copy of the ForeignKey_Edit field template as our base we will end up with a field template that looks like Figure 1 allowing you to have a hierarchy ‘n’ levels deep.

Cascade Hierarchical Field Template

Figure 1 – Cascade Hierarchical Field Template in action.

Note: It turns out the the Product field on the Order is only available in insert not edit so I have moved away from Northwind and have created a very construed Vehicles sample DB, I have included as a script in the download at the end of this article.

The first we must make the copy of the ForeignKey_Edit field template, and then make sure we rename it to CascadeHierarchical_Edit, also we must change the class name all it’s files; this sample being a Web Application project there are three files:

  • CascadeHierarchical_Edit.ascx
  • CascadeHierarchical_Edit.ascx.cs
  • CascadeHierarchical_Edit.ascx.designer.cs

Each must be modified as follows (I generally follow the naming convention use in the other standard field templates i.e. Text_Edit’s class name is Text_EditField), so we will set our field templates class name to CascadeHierarchical_EditField.

Note: if you change CascadeHierarchical_Edit.ascx first then you will not need to change the CascadeHierarchical_Edit.ascx.designer.cs will happen automatically. (I don’t know why the CascadeHierarchical_Edit.ascx.cs is not also changed automatically when the ascx file’s Inherits property is changed).

In the .cs file change the class name to CascadeHierarchical_EditField and in the CascadeHierarchical_Edit.ascx change the Inherits property to  be namespace.CascadeHierarchical_EditField, namespace is usually the name of the project, in any case if will already be filled in in the template unless you are copying from one project to another.

Now we have a basis to begin, lets first start with the Page_Init and some explanation.

<%@ Control 
    Language="C#" 
    CodeBehind="CascadeHierarchical_Edit.ascx.cs" 
    Inherits="CascadeHierarchicalFieldTemplate.CascadeHierarchical_EditField" %>

<asp:RequiredFieldValidator 
    runat="server" 
    ID="RequiredFieldValidator1" 
    CssClass="DDControl DDValidator" 
    Display="Static" 
    Enabled="false"/>
<asp:DynamicValidator 
    runat="server" 
    ID="DynamicValidator1" 
    CssClass="DDControl DDValidator" 
    Display="Static"/>

Listing 1 – the aspx page.

Here in Listing 1 you can see all we have are the required and dynamic validators, this is all we need at the dropdown lists will all be created dynamically.

#region member variables
// hold the current data context to access the model
private object context;
// hold the list of filters
private SortedList<int, HierachicalListControl> filters = new SortedList<int, HierachicalListControl>();
// hold the attribute
private CascadeHierarchicalAttribute cascadeHierarchicalAttribute;
#endregion

protected void Page_Init(object sender, EventArgs e)
{
    // check we have a cascade hierarchical attribute if not throw error
    cascadeHierarchicalAttribute = Column.GetAttribute<CascadeHierarchicalAttribute>();
    if (cascadeHierarchicalAttribute == null)
        throw new InvalidOperationException("Was expecting a CascadeFilterAttribute.");

    // check we have correct column type if not throw error
    if (!(Column is MetaForeignKeyColumn))
        throw new InvalidOperationException(String.Format("Column {0} must be a foreign key column navigation property", Column.Name));

    // get current context
    context = Table.CreateContext();

    // get hierarchical cascade columns
    var parentColumns = new SortedList<int, String>();
    for (int i = 0; i < cascadeHierarchicalAttribute.Parameters.Length; i++)
        parentColumns.Add(i, cascadeHierarchicalAttribute.Parameters[i]);

    // add extra column to represent this column itself
    parentColumns.Add(cascadeHierarchicalAttribute.Parameters.Length, "");

    //get current column into a local variable
    MetaForeignKeyColumn column = ForeignKeyColumn;

    // setup list of filter definitions
    for (int i = 0; i < parentColumns.Count; i++)
    {
        // get parent column name
        var parentColumnName = parentColumns[i];

        // create dropdown list
        var ddl = new DropDownList()
        {
            ID = String.Format("ListControl{0}", i),
            Enabled = false,
            AutoPostBack = true
        };
        // create filter
        var filter = new HierachicalListControl(ddl) { Column = column };

        // check for last parent filter
        if (!String.IsNullOrEmpty(parentColumnName))
        {
            // set parent column from parent table
            filter.ParentColumn = (MetaForeignKeyColumn)column.ParentTable.GetColumn(parentColumnName);

            // set current column to parent column
            column = filter.ParentColumn;
        }
        else
        {
            // this is the last parent and has
            // no parent itself so set to null
            filter.ParentColumn = null;
            column = null;
        }
        // add filter to list of filters
        filters.Add(i, filter);
    }

    // add dropdown list to page in correct order 2, 1, 0
    // last parent, parent<N>, child
    for (int i = parentColumns.Count - 1; i >= 0; i--)
    {
        // setup dropdown list
        filters[i].ListControl.Items.Clear();
        filters[i].ListControl.Items.Add(new ListItem("------", ""));

        // add parent list controls event handler
        if (i > 0)
            filters[i].ListControl.SelectedIndexChanged += ListControls_SelectedIndexChanged;

        // add control to place holder
        this.Controls.Add(filters[i].ListControl);
    }

    if (Mode == DataBoundControlMode.Insert)
    {
        // fill last parent filter
        var lastParentIndex = filters.Count - 1;
        var parentTable = filters[lastParentIndex].Column.ParentTable;
        var parentQuery = parentTable.GetQuery(context);

        // set next descendant list control
        PopulateListControl(lastParentIndex, parentQuery);
    }
}

Listing 2 -  Page_Init

The reason for using Page_Init (see Listing 2) is that we are going to create the dropdown lists dynamically, these need to be instantiated in the OnInit event to be fully involved in post back.

The first thing we need to do in the Page_Init is to make sure we have the correct Column Type (MetaForeignKeyColumn) and that the column has a CascadeHierarchicalAttribute assigned, if either of these in not present then we throw an error.

There are three for loops in the Page_Init, the first for loop we are building a list of parent columns to help with the next for loop which builds the filters list (by having two loops we make it easy to put the list controls onto the page in the most logical order, last parent to the left and child to the right).

Note: We also set each dropdown list’s SelectedIndexChanged event to post back to the same ListControls_SelectedIndexChanged handler.

This list of  filters is used throughout the rest of the field template to build each list control and made up of a dropdown list the current column and it’s parent column (see Listing 3).

/// <summary>
/// Class to contains information about cascading dropdown lists
/// </summary>
protected internal class HierachicalListControl
{
    /// <summary>
    /// Returns a <see cref="System.String"/> that represents this instance.
    /// </summary>
    /// <returns>
    /// A <see cref="System.String"/> that represents this instance.
    /// </returns>
    public override string ToString()
    {
        var parentColumn = ParentColumn != null ? ParentColumn.Name : "null";
        return String.Format("{0}.{1}", Column.Name, parentColumn);
        //return this.Column.Name;
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="HierachicalListControl"/> class.
    /// </summary>
    /// <param name="column">This column.</param>
    /// <param name="parentColumn">This column's parent column.</param>
    public HierachicalListControl(ListControl listControl)
    {
        ListControl = listControl;
    }

    /// <summary>
    /// Gets or sets the filter column.
    /// </summary>
    /// <value>The column.</value>
    public MetaForeignKeyColumn Column { get; set; }

    /// <summary>
    /// Gets or sets the filter column's parent column.
    /// </summary>
    /// <value>The parent column.</value>
    public MetaForeignKeyColumn ParentColumn { get; set; }

    /// <summary>
    /// Gets or sets the list control.
    /// </summary>
    /// <value>The list control.</value>
    public ListControl ListControl { get; set; }
}

Listing 3 – HierachicalListControl used in list of filters .

Note: I have overridden the ToString method to make the filters list more readable in debug mode, in Column.ParentColumn format, this is not required for the sample to function correctly.

The final for loop is use to initialise each dropdown list with it’s default item, hook-up the SelectionIndexChanged event and then add it to the page.

The final segment of code in Page_Init is the if statement for when we are in insert mode as the OnDataBound event will not fire, so we populate the last filter the (highest in the hierarchy).

protected void Page_Load(object sender, EventArgs e)
{
    if (filters[0] != null &&
        filters[0].ListControl != null)
    {
        RequiredFieldValidator1.ControlToValidate = "ListControl0";
        DynamicValidator1.ControlToValidate = "ListControl0";
        SetUpValidator(RequiredFieldValidator1);
        SetUpValidator(DynamicValidator1);
    }
}

Listing 4 – Page_Load event handler

The Page_Load event just makes sure that we have some filter[0] and then sets up the validators. The next event handler is the OnDataBinding handler here we make sure we are in Edit mode and that the current value is not null and then we call the main method for setting up the filters SetupListControls.

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

    // Set initial value
    if (Mode == DataBoundControlMode.Edit && FieldValue != null)
        PopulateAllListControls(FieldValue);
}

/// <summary>
/// Sets the default values.
/// </summary>
/// <param name="fieldValue">The value.</param>
private void PopulateAllListControls(object fieldValue)
{
    var displayStrings = new SortedList<int, String>();

    #region Get list of propert values
    // get property values
    var propertyValues = new SortedList<int, Object>();
    propertyValues.Add(0, fieldValue);
    for (int i = 0; i < filters.Count - 1; i++)
    {
        var parentName = filters[i].ParentColumn.Name;
        object pv = propertyValues[i].GetPropertyValue(parentName);
        propertyValues.Add(i + 1, pv);
    }
    #endregion

    // stating at the first filter and work way up to the last filter
    for (int i = 0; i < filters.Count; i++)
    {
        var parentTable = filters[i].Column.ParentTable;
        var parentQuery = parentTable.GetQuery(context);
        IQueryable listItemsQuery;
        if (i == cascadeHierarchicalAttribute.Parameters.Length)
        {
            listItemsQuery = parentQuery.GetQueryOrdered(parentTable);
        }
        else
        {
            var pcol = filters[i + 1].Column;
            var selectedValue = filters[i].ParentColumn.GetForeignKeyString(propertyValues[i]);
            listItemsQuery = parentQuery.GetQueryFilteredFkColumn(pcol, selectedValue);
        }

        // set next descendant list control
        PopulateListControl(i, listItemsQuery);

        // set initial values
        var selectedValueString = filters[i].Column.Table.GetPrimaryKeyString(propertyValues[i]);
        ListItem item = filters[i].ListControl.Items.FindByValue(selectedValueString);
        if (item != null)
            filters[i].ListControl.SelectedValue = selectedValueString;
    }
}

Listing 5 – OnDataBinding and PopulateAllListControls

The first thing SetupListControls does is get a list of actual values for each filter in the cascade of filters using fieldValue as a starting point, working back from the passed in fieldValue, it calls the method GetPropertyValue which uses reflection to get the value of the next entity using the current value and the parent column’s name. These values are in turn used to fill each filter with appropriately filtered values. The last parent getting an unfiltered list of items as it has no parent itself.

/// <summary>
/// Gets the property value.
/// </summary>
/// <param name="sourceObject">The source object.</param>
/// <param name="propertyName">Name of the property.</param>
/// <returns>The named properties value.</returns>
public static Object GetPropertyValue(this Object sourceObject, 
    string propertyName)
{
    if (sourceObject != null)
        return sourceObject.GetType()
            .GetProperty(propertyName)
            .GetValue(sourceObject, null);
    else
        return null;
}

Listing 6 – GetPropertyValue

The two extension methods used to get the list of items are called GetQueryOrdered Listing 7 or GetQueryFilteredFkColumn Listing 8.

/// <summary>
/// Gets the query ordered.
/// </summary>
/// <param name="sourceQuery">The source query.</param>
/// <param name="table">The table.</param>
/// <returns></returns>
public static IQueryable GetQueryOrdered(this IQueryable sourceQuery, MetaTable table)
{
    // get display column attribute
    var displayColumnAttribute = table.GetAttribute<DisplayColumnAttribute>();

    // check to see if sort is assigned
    if (displayColumnAttribute == null || displayColumnAttribute.SortColumn == null)
        return sourceQuery;

    // {row.OrderBy(row => row.Name)}
    var orderByCall = GetOrderByCallExpression(
        sourceQuery, 
        table, 
        displayColumnAttribute.SortColumn, false);

    // create and return query
    return sourceQuery.Provider.CreateQuery(orderByCall);
}

Listing 7 – GetQueryOrdered

/// <summary>
/// Gets a list of entities from the source IQueryable 
/// filtered by the MetaForeignKeyColumn's selected value
/// </summary>
/// <param name="sourceQuery">The query to filter</param>
/// <param name="fkColumn">The column to filter the query on</param>
/// <param name="fkSelectedValue">The value to filter the query by</param>
/// <returns>
/// An IQueryable of the based on the source query 
/// filtered but the FK column and value passed in.
/// </returns>
public static IQueryable GetQueryFilteredFkColumn(this IQueryable sourceQuery, 
    MetaForeignKeyColumn fkColumn, String fkSelectedValue)
{
    // if no filter value return the query
    if (String.IsNullOrEmpty(fkSelectedValue))
        return sourceQuery;

    // order query
    sourceQuery = GetQueryOrdered(sourceQuery, fkColumn.Table);

    // {RequiredPlots}
    var parameterExpression = Expression.Parameter(sourceQuery.ElementType, fkColumn.Table.Name);

    // {(RequiredPlots.Builders.Id = 1)}
    var body = BuildWhereClause(fkColumn, parameterExpression, fkSelectedValue);

    // {RequiredPlots => (RequiredPlots.Builders.Id = 1)}
    var whereLambda = Expression.Lambda(body, parameterExpression);

    // {Developers.Where(RequiredPlots => (RequiredPlots.Builders.Id = 1))}
    var whereCall = Expression.Call(typeof(Queryable),
        "Where",
        new Type[] { sourceQuery.ElementType },
        sourceQuery.Expression,
        Expression.Quote(whereLambda));

    // create and return query
    return sourceQuery.Provider.CreateQuery(whereCall);
}

Listing 8 – GetQueryFilteredFkColumn

Listings 7 & 8 are form the IQueriableExtensionMethods.cs class file in the NotAClue.Web.DynamicData class library, and are a part of my dynamic Linq Expression extension methods that originally came from the ASP.NET July 2007 Futures Source Code sample on ASP.Net on CodePlex.com, I continue to expand and modify them as I need to; so I wont go into detail here as there better posts on Linq Expression than any I could write.

Note: There is a useful sample in the C# samples see Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library on Scott Guthrie's blog, “Dynamic Linq” allows you to easily build Linq queries on the fly.
/// <summary>
/// Handles the SelectedIndexChanged event of the parentListControls control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
/// <summary>
/// Setups the parent list control.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="filterIndex">The parent id.</param>
/// <param name="items">The items.</param>
public void PopulateListControl(int filterIndex, IQueryable items)
{
    // clear the list controls list property
    filters[filterIndex].ListControl.Items.Clear();
    // enable list control
    filters[filterIndex].ListControl.Enabled = true;

    // add unselected value showing the column name
    // [Styles]
    filters[filterIndex].ListControl.Items.Add(
        new ListItem(String.Format("[{0}]", 
            filters[filterIndex].Column.DisplayName), ""));

    foreach (var row in items)
    {
        // populate each item with the display string and key value
        filters[filterIndex].ListControl.Items.Add(
            new ListItem(filters[filterIndex].Column.ParentTable.GetDisplayString(row),
            filters[filterIndex].Column.ParentTable.GetPrimaryKeyString(row)));
    }
}

Listing 9 – PopulateListControl

Once we have the query filtered and ordered we then set the list control’s items up by calling PopulateListControl (Listing 9), then finally we set the initial value in of the list control.

/// <summary>
/// Handles the SelectedIndexChanged event for each List control, 
/// and populates the next list control in the hierarchy.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">
/// The <see cref="System.EventArgs"/> instance containing the event data.
/// </param>
void ListControls_SelectedIndexChanged(object sender, EventArgs e)
{
    // get list control
    var listControl = (ListControl)sender;

    // get the sending list controls id as an int
    var id = ((Control)sender).ID;

    // use regular expression to find list control index
    var regEx = new Regex(@"\d+");
    var parentIndex = int.Parse(regEx.Match(id).Value);

    if (!String.IsNullOrEmpty(listControl.SelectedValue))
    {
        if (parentIndex > 0)
        {
            // set child index
            var childIndex = parentIndex - 1;

            // get parent table
            var parentTable = filters[childIndex].Column.ParentTable;

            // get query from table
            var query = parentTable.GetQuery(context);

            // get items for list control
            var itemQuery = query.GetQueryFilteredFkColumn(
                filters[parentIndex].Column,
                listControl.SelectedValue);

            // populate list control
            PopulateListControl(childIndex, itemQuery);

            // reset all descendant list controls
            ResetAllDescendantListControls(childIndex);
        }
    }
    else
    {
        // reset all descendant list controls
        ResetAllDescendantListControls(parentIndex);
    }
}

Listing 10 – parentListControls_SelectedIndexChanged

The next thing we have to deal with is SelectedIndexChanged event on each dynamically created filter, we do this in the ListControls_SelectedIndexChanged handler (Listing 10) we first get the posting list control and then extract the filter index from it’s name. Remember all the filters are named ListControl{N} where N is the index into the filters list for the filter.

Note: We use a regular expression “\d+” to match one or more digits in the list control’s ID

Then we check to see if the list control has a SelectedValue if not we reset it and all it’s children to the default of "----" and Enabled to false. If it has a SelectedValue then we get a query on the child column filtered by the current SelectedValue and populate the next list control in line.

We then call SetupParentListControl to populate the control and finally ResetAllDescendantListControls to set any descendant controls to their default value of "----" and disabled.

The last two methods as two of the standard field template methods just slightly change to avoid exceptions seen here in Listings 11.

protected override void ExtractValues(IOrderedDictionary dictionary)
{
    // If it's an empty string, change it to null
    string value;
    if (filters[0] != null && filters[0].ListControl != null)
        value = filters[0].ListControl.SelectedValue;
    else
        value = String.Empty;

    if (String.IsNullOrEmpty(value))
        value = null;

    ExtractForeignKey(dictionary, value);
}

public override Control DataControl
{
    get
    {
        if (filters[0] != null && filters[0].ListControl != null)
            return filters[0].ListControl;
        else
            return null;
    }
}

Listing 11 – ExtractValues method and DataControl property.

These two methods merely check to see if there are nay controls before processing.

Download

The sample is a Visual Studio 2010 and .Net 4.0 sample but the code and field template should work with Visual Studio 2008 SP1 and .Net 3.5 SP1 DD Web Application.

Note: The script for creating the Vehicles database is in the zip file.

12 comments:

Unknown said...

Script for the Vehicles database is not in the zip file!

Stephen J. Naughton said...

Sorry Peter, fixed it, I accidentally added it to a previous releases zip file :(
Steve :)

matteo said...

Where is a little bug. this code

// set initial values var selectedValueString = filters[i].Column.Table.GetPrimaryKeyString(propertyValues[i]); ListItem item = filters[i].ListControl.Items.FindByValue(selectedValueString); if (item != null) filters[i].ListControl.SelectedValue = selectedValueString;

works only if every table have the same PK. For example if you change DB with ID_Model and ID_VehiclesType for PK it doesn't work.

Stephen J. Naughton said...

Sorry matteo not sure what you mean :(

Steve

matteo said...

I meand that this code works only if every table in your database have the same PK column name (in your database, every PK is Id). if you have different PK name (example if you change in your db PK in the table Models name (From Id to Id_Pk_Models)) your code doesn't work because you use the wrong PK name. I hope that I have explain myself clearly now :)
thanks

Stephen J. Naughton said...

Sorry Matteo, I have thos working on client systems where the PK is no ID or Id with out changing the code, as far as I can see I am just getting the PK the DD knows about. :(

Steve

matteo said...

may be there is a different in linqtosql. this line

var selectedValueString = filters[i].Column.Table.GetPrimaryKeyString(propertyValues[i]);

in my code try to get the father PK with the name of child PK (obviously i get an error). For example tell me "PK_Models is not a valid column in Vehicle Type ". If it doesn't happen to you with EF, may be there is some different about this line result.

I hope to be useful for someone that what to use your code with LinqToSql.

bye :)

Stephen J. Naughton said...

Hi Matteo, I have used this with Linq to SQL and EF With Link to SQL the PK's were PKID and the foreign keys were ColumnName_FKID so I can't be sure of the issue with the data models al though I will give it a try and update the post with a fix thanks :)

Steve

Alessio said...

Hi Steve, thanks for you work. It helped me a lot. I'm a beginner with Dynamic Data so your posts were very useful. I tried this solution in a project compiled with .NET Framework 3.5, but it doesn't work 'cause there is no reference to QueryableFilterUserControl. Is there a way I can fix it, without using .NET Framework 4 (my hosting site doesn't allow this).
Thanks in advance

Alessio

Stephen J. Naughton said...

Sorry Alessio, I don't have a version for .Net 3.5 that's working, what filtering do you have in your project?

Steve :)

Alessio said...

I need a cascade filter and obviously a cascade template field for a chain of tables that are realted with foreign keys. In particular it is a site of an Italian soccer team (a little team, not famous) and I have tables for:
category -> league -> matchday -> game

The team company has more teams in different categories (each one with its own league)
When I insert a new game I must be sure that the game is tied to the correct category so I want to show the filter in cascade. The solution you present in this post is optimous, but my hosting does not allow .NET Framework 4. Maybe I can write the code for the QueryableFilterUserControl class?

Stephen J. Naughton said...

Hi Alessio, this issue in DD 3.5 is there is only one filter that does all filtering, id DD4 we have a new filtering system added.

However if you add the old filter from the old Futures project see Dynamic Data Futures VS2008 SP1 RTM if you add this you could still use the cascading filter but it would need a good deal of modifications.

I don't have time to work on DD1 stuff at the moment due to paid work loads, and DD4 is more fun :)

Steve