Well I wanted to do this in EF and Preview 3 at the same time but I having an issue with that so I’m combining both the Cascading Fields and Filters together and when the bugs are ironed out of the Preview I’ll do it there also.
Firstly the issue with the Preview.
- No filters support in the DefaultEFProject
- Errors when saving using the DefaultDomainServiceProject
Well I’m going to build a new 
I’ll add the projects zipped to the end of the article.
Lets create the class file first. (I’m assuming you know how to operate VS :D )
Create a new Class Library project 
Figure 1 – Adding Assembly name and Default namespace
using System;Listing 1 - CascadeAttribute
namespace DynamicData.CascadeExtensions { /// <summary> /// Attribute to identify which column to use as a /// parent column for the child column to depend upon /// </summary> public class CascadeAttribute : Attribute { /// <summary> /// Name of the parent column /// </summary> public String ParentColumn { get; private set; } /// <summary> /// Default Constructor sets ParentColumn /// to an empty string /// </summary> public CascadeAttribute() { ParentColumn = ""; } /// <summary> /// Constructor to use when /// setting up a cascade column /// </summary> /// <param name="parentColumn">Name of column to use in cascade</param> public CascadeAttribute(string parentColumn) { ParentColumn = parentColumn; } } }
You will need one of my extension methods to extract the attribute later on:
/// <summary> /// Get the attribute or a default instance of the attribute /// if the Column attribute do not contain the attribute /// </summary> /// <typeparam name="T">Attribute type</typeparam> /// <param name="table">Column to search for the attribute on.</param> /// <returns>The found attribute or a default instance of the attribute of type T</returns> public static T GetAttributeOrDefault<T>(this MetaColumn column) where T : Attribute, new() { return column.Attributes.OfType<T>().DefaultIfEmpty(new T()).FirstOrDefault(); }
Listing 2 – GetAttributeOrDefault extension method.
I have some more extension methods to add later that will be used by both the CascadeFieldTemplate and CascadeFilterTemplates.
using System; namespace DynamicData.CascadeExtensions { /// <summary> /// Event Arguments for Category Changed Event /// </summary> public class SelectionChangedEventArgs : EventArgs { /// <summary> /// Custom event arguments for SelectionChanged /// event of the CascadingFieldTemplate control /// </summary> /// <param name="value"> /// The value of the currently selected /// value of the parent control /// </param> public SelectionChangedEventArgs(String value) { Value = value; } /// <summary> /// The values from the control of the parent control /// </summary> public String Value { get; set; } } }
Listing 3 - SelectionChangedEventArgs
Next we create two new classes called CascadeFieldTemplate and CascadeFilterTemplate.
using System; using System.Web.DynamicData; using System.Web.UI.WebControls; namespace DynamicData.CascadeExtensions { /// <summary> /// Modifies the standard FieldTEmplateUserControl /// to support cascading of selected values. /// </summary> public class CascadingFieldTemplate : FieldTemplateUserControl { /// <summary> /// Controls selected value /// </summary> public String SelectedValue { get; private set; } /// <summary> /// This controls list control /// </summary> public ListControl ListControl { get; private set; } /// <summary> /// Parent column of this column named in metadata /// </summary> public MetaForeignKeyColumn ParentColumn { get; private set; } /// <summary> /// This FieldTemplates column as MetaForeignKeyColumn /// </summary> public MetaForeignKeyColumn ChildColumn { get; private set; } /// <summary> /// Parent control acquired from ParentColumn /// </summary> public CascadingFieldTemplate ParentControl { get; set; } protected virtual void Page_Init(object sender, EventArgs e) { // get the parent column var parentColumn = Column.GetAttributeOrDefault<CascadeAttribute>().ParentColumn; if (!String.IsNullOrEmpty(parentColumn)) { ParentColumn = Column.Table.GetColumn(parentColumn) as MetaForeignKeyColumn; } // cast Column as MetaForeignKeyColumn ChildColumn = Column as MetaForeignKeyColumn; // get parent field (note you must specify the // container control type in <DetailsView> or <FormView> ParentControl = GetParentControl(); } /// <summary> /// Delegate for the Interface /// </summary> /// <param name="sender"> /// A parent control also implementing the /// ISelectionChangedEvent interface /// </param> /// <param name="e"> /// An instance of the SelectionChangedEventArgs /// </param> public delegate void SelectionChangedEventHandler( object sender, SelectionChangedEventArgs e); //publish event public event SelectionChangedEventHandler SelectionChanged; /// <summary> /// Raises the event checking first that an event if hooked up /// </summary> /// <param name="value">The value of the currently selected item</param> public void RaiseSelectedIndexChanged(String value) { // make sure we have a handler attached if (SelectionChanged != null) { //raise event SelectionChanged(this, new SelectionChangedEventArgs(value)); } } // advanced populate list control protected void PopulateListControl(ListControl listControl, String filterValue) { //get the parent column if (ParentColumn == null) { // if no parent column then just call // the base to populate the control PopulateListControl(listControl); // make sure control is enabled listControl.Enabled = true; } else if (String.IsNullOrEmpty(filterValue)) { // if there is a parent column but no filter value // then make sure control is empty and disabled listControl.Items.Clear(); if (Mode == DataBoundControlMode.Insert || !Column.IsRequired) listControl.Items.Add(new ListItem("[Not Set]", "")); // make sure control is disabled listControl.Enabled = false; } else { // get the child columns parent table var childTable = ChildColumn.ParentTable; // get query {Table(Developer).OrderBy(d => d.Name)} var query = ChildColumn.ParentTable.GetQuery(Column.Table.CreateContext()); // get list of values filtered by the parent's selected entity var itemlist = query.GetQueryFilteredByParent(ParentColumn, filterValue); // clear list controls items collection before adding new items listControl.Items.Clear(); // only add [Not Set] if in insert mode or column is not required if (Mode == DataBoundControlMode.Insert || !Column.IsRequired) listControl.Items.Add(new ListItem("[Not Set]", "")); // add returned values to list control foreach (var row in itemlist) listControl.Items.Add( new ListItem( childTable.GetDisplayString(row), childTable.GetPrimaryKeyString(row))); // make sure control is enabled listControl.Enabled = true; } } /// <summary> /// Gets the Parent control in a cascade of controls /// </summary> /// <param name="column"></param> /// <returns></returns> private CascadingFieldTemplate GetParentControl() { // get value of dev ddl (Community) var parentDataControl = GetContainerControl(); if (ParentColumn != null) { // Get Parent FieldTemplate var parentDynamicControl = parentDataControl .FindDynamicControlRecursive(ParentColumn.Name) as DynamicControl; // extract the parent control from the DynamicControl CascadingFieldTemplate parentControl = null; if (parentDynamicControl != null) parentControl = parentDynamicControl.Controls[0]
as CascadingFieldTemplate; return parentControl; } return null; } /// <summary> /// Get the Data Control containing the FiledTemplate /// usually a DetailsView or FormView /// </summary> /// <param name="control"> /// Use the current field template as a starting point /// </param> /// <returns> /// A CompositeDataBoundControl the base class for FormView and DetailsView /// </returns> private CompositeDataBoundControl GetContainerControl() { var parentControl = this.Parent; while (parentControl != null) { // NOTE: this will not work if used in // inline editing in a list view as // ListView is a DataBoundControl. var p = parentControl as CompositeDataBoundControl; if (p != null) return p; else parentControl = parentControl.Parent; } return null; } } }
Listing 4 – CascadeFieldTemplate
using System; using System.Web.DynamicData; using System.Web.UI.WebControls; namespace DynamicData.CascadeExtensions { /// <summary> /// Modifies the standard FieldTEmplateUserControl /// to support cascading of selected values. /// </summary> public class CascadingFilterTemplate : FilterUserControlBase { #region Properties /// <summary> /// This controls list control /// </summary> public ListControl ListControl { get; private set; } /// <summary> /// Paretn column of this column named in metadata /// </summary> public MetaForeignKeyColumn ParentColumn { get; private set; } /// <summary> /// This FieldTemplates column as MetaForeignKeyColumn /// </summary> public MetaForeignKeyColumn ChildColumn { get; private set; } /// <summary> /// Parent control acquired from ParentColumn /// </summary> public CascadingFilterTemplate ParentControl { get; set; } #endregion //public override IQueryable GetQueryable(IQueryable source) //{ // return source; //} protected virtual void Page_Init(object sender, EventArgs e) { // get the parent column var parentColumn = Column.GetAttributeOrDefault<CascadeAttribute>().ParentColumn; if (!String.IsNullOrEmpty(parentColumn)) ParentColumn = Column.Table.GetColumn(parentColumn) as MetaForeignKeyColumn; // cast Column as MetaForeignKeyColumn ChildColumn = Column as MetaForeignKeyColumn; // get dependee field (note you must specify the // container control type in <DetailsView> or <VormView> ParentControl = GetParentControl(); } /// <summary> /// Delegate for the Interface /// </summary> /// <param name="sender"> /// A parent control also implementing the /// ISelectionChangedEvent interface /// </param> /// <param name="e"> /// An instance of the SelectionChangedEventArgs /// </param> public delegate void SelectionChangedEventHandler( object sender, SelectionChangedEventArgs e); //publish event public event SelectionChangedEventHandler SelectionChanged; /// <summary> /// Raises the event checking first that an event if hooked up /// </summary> /// <param name="value">The value of the currently selected item</param> public void RaiseSelectedIndexChanged(String value) { // make sure we have a handler attached if (SelectionChanged != null) { //raise event SelectionChanged(this, new SelectionChangedEventArgs(value)); } } // advanced populate list control protected void PopulateListControl(ListControl listControl, String filterValue) { //get the parent column if (ParentColumn == null) { // if no parent column then just call // the base to populate the control PopulateListControl(listControl); // make sure control is enabled listControl.Enabled = true; } else if (String.IsNullOrEmpty(filterValue)) { // if there is a parent column but no filter value // then make sure control is empty and disabled listControl.Items.Clear(); listControl.Items.Add(new ListItem("[All]", "")); // make sure control is disabled listControl.Enabled = false; } else { // get the child columns parent table var childTable = ChildColumn.ParentTable; // get query {Table(Developer).OrderBy(d => d.Name)} var query = ChildColumn.ParentTable.GetQuery(Column.Table.CreateContext()); // filter the query by the parent var itemlist = query.GetQueryFilteredByParent(ParentColumn, filterValue); // clear list controls items collection before adding new items listControl.Items.Clear(); listControl.Items.Add(new ListItem("[All]", "")); // add returned values to list control foreach (var row in itemlist) listControl.Items.Add( new ListItem( childTable.GetDisplayString(row), childTable.GetPrimaryKeyString(row))); // make sure control is enabled listControl.Enabled = true; } } /// <summary> /// Gets the Parent control in a cascade of controls /// </summary> /// <returns>An the parent control or null</returns> private CascadingFilterTemplate GetParentControl() { if (ParentColumn != null) { // get the parent container var parentDataControl = GetContainerControl(); // get the parent container if (parentDataControl != null) return parentDataControl.FindFilterControlRecursive(ParentColumn.Name) as CascadingFilterTemplate; } return null; } /// <summary> /// Get the Data Control containing the FiledTemplate /// usually a DetailsView or FormView /// </summary> /// <param name="control"> /// Use the current field template as a starting point /// </param> /// <returns> /// A FilterRepeater the control that /// contains the current control /// </returns> private FilterRepeater GetContainerControl() { var parentControl = this.Parent; while (parentControl != null) { var p = parentControl as FilterRepeater; if (p != null) return p; else parentControl = parentControl.Parent; } return null; } } }
Listing 5 – CadcadingFilterTemplate
You may note that I have removed GetQueryFilteredByParent and some other local methods from both CascadeFieldTemplate and CadcadingFilterTemplate, they will be placed in the extension methods class file later.
Now we come the the differences between the Linq to SQL implementation and this the Entity Framework implementation.
The issue I had when trying to make this work with EF was that
private IQueryable GetQueryFilteredByParent (MetaTable childTable, MetaForeignKeyColumn parentColumn, object selectedParent) { // get query {Table(Developer)} var query = ChildColumn.ParentTable.GetQuery(DC); // {Developers} var parameter = Expression.Parameter(childTable.EntityType, childTable.Name); // {Developers.Builder} var property = Expression.Property(parameter, parentColumn.Name); // {value(Builder)} var constant = Expression.Constant(selectedParent); // {(Developers.Builder = value(Builder))} var predicate = Expression.Equal(property, constant); // {Developers => (Developers.Builder = value(Builder))} var lambda = Expression.Lambda(predicate, parameter); // {Table(Developer).Where(Developers => (Developers.Builder = value(Builder)))} var whereCall = Expression.Call(typeof(Queryable), "Where", new Type[] { childTable.EntityType }, query.Expression, lambda); // generate the query and return it return query.Provider.CreateQuery(whereCall); }
Listing 6 – Old GetQueryFilteredByParent method.
In here I passed in the selectedParent which contained the entity I was filtering on anyway what I found was the EF did not like that at all it said basically I want a simple value like int, String, double etc.
This is the expression I was faced with:
Table(Developer).Where(Developers => (Developers.Builder = value(Builder)))
here I was leaving the join up to L2S but EF wanted me to be more specific
Table(Developer).Where(Developers => (Developers.BuilderId = 2))
But I knew that wouldn't work because EF does not have the FK fields in it’s entities, well not if it can help it :) so I surmised that it would want something like this:
Table(Developer).Where(Developers => (Developers.Builder.Id = 2))
Where Id is the PK of the Builder entity.
And then I was lucky enough to be trying this with Preview 3 and the DefaultDomainServiceProject which if you have a look at the ForeignKey filter you will see some nice Expression creating code:
private Expression BuildQueryBody( ParameterExpression parameterExpression, string selectedValue) { IDictionary dict = new Hashtable(); Column.ExtractForeignKey(dict, selectedValue); int i = 0; ArrayList andFragments = new ArrayList(); foreach (DictionaryEntry entry in dict) { string fieldName = Column.ParentTable.Name + "." + Column.ParentTable.PrimaryKeyColumns[i++].Name; Expression propertyExpression = CreatePropertyExpression(parameterExpression, fieldName); object value = ChangeType(entry.Value, propertyExpression.Type); Expression equalsExpression = Expression.Equal( propertyExpression, Expression.Constant(value, propertyExpression.Type)); andFragments.Add(equalsExpression); } Expression result = null; foreach (Expression e in andFragments) { if (result == null) { result = e; } else { result = Expression.AndAlso(result, e); } } return result; }
Listing 7 – BuildQuery from the ForeignKey filter.
This helps build something like this (item.Categories.CategoryID = 1) which eventually produces this where expression and it would deal with composite keys.
Where(item => (item.Categories.CategoryID = 2))
So I swiped that from the ForeignKey filter and added it to my Extension methods, and built the missing bits from what I could glean with Reflector.
So we have the following in the extension methods class
#region IQueryable methods /// <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 GetQueryFilteredByParent(this IQueryable sourceQuery, MetaForeignKeyColumn fkColumn, String fkSelectedValue) { // if no filter value return the query if (String.IsNullOrEmpty(fkSelectedValue)) return sourceQuery; // {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 lambda = Expression.Lambda(body, parameterExpression); // Developers.Where(RequiredPlots => (RequiredPlots.Builders.Id = 1))} MethodCallExpression whereCall = Expression.Call(typeof(Queryable), "Where", new Type[] { sourceQuery.ElementType }, sourceQuery.Expression, Expression.Quote(lambda)); // create and return query return sourceQuery.Provider.CreateQuery(whereCall); } /// <summary> /// This builds the and where clause taking /// into account composite keys /// </summary> /// <param name="fkColumn">The column to filter the query on</param> /// <param name="fkSelectedValue">The value to filter the query by</param> /// <param name="parameterExpression">Parameter expression</param> /// <returns> /// Returns the expression for the where clause /// i.e. ((x = 1) && (Y = 2)) or (x = 1) etc. /// </returns> private static Expression BuildWhereClause( MetaForeignKeyColumn fkColumn, ParameterExpression parameterExpression, string fkSelectedValue) { // get the FK's and value into dictionary IDictionary dict = new OrderedDictionary(); fkColumn.ExtractForeignKey(dict, fkSelectedValue); // setup index into dictionary int i = 0; // setup array list to hold each AND fragment ArrayList andFragments = new ArrayList(); foreach (DictionaryEntry entry in dict) { // get fk name 'Builders.Id' string keyName = fkColumn.Name + "." + fkColumn.ParentTable.PrimaryKeyColumns[i++].Name; // Build property expression // i.e. {RequiredPlots.Builders.Id} Expression propertyExpression = BuildPropertyExpression(parameterExpression, keyName); // sets the type based on the propertyExpression's type // i.e. all the values returned from the DDL are of type string // so the type on the expression needs setting to the correct type object value = ChangeType(entry.Value, propertyExpression.Type); // join the property expression and value in an // equals expression i.e. (RequiredPlots.Builders.Id = 1) Expression equalsExpression = Expression.Equal(propertyExpression, Expression.Constant(value, propertyExpression.Type)); // add a fragment to array list andFragments.Add(equalsExpression); } // initialise result Expression result = null; // join add fragments of composite keys // together together foreach (Expression e in andFragments) { if (result == null) result = e; else result = Expression.AndAlso(result, e); } // joined fragments look something like: // (RequiredPlots.Developer.Id = 1) && (RequiredPlots.HouseType.Id = 1) return result; } /// <summary> /// Builds a property expression from the parts it joins /// the parameterExpression and the propertyName together. /// i.e. {RequiredPlots} and "Builders.Id" /// becomes: {RequiredPlots.Developers.Id} /// </summary> /// <param name="parameterExpression"> /// The parameter expression. /// </param> /// <param name="propertyName"> /// Name of the property. /// </param> /// <returns> /// A property expression /// </returns> public static Expression BuildPropertyExpression( Expression parameterExpression, string propertyName) { Expression expression = null; // split the propertyName into each part to // be build into a property expression string[] strArray = propertyName.Split(new char[] { '.' }); foreach (string str in strArray) { if (expression == null) expression = Expression.PropertyOrField(parameterExpression, str); else expression = Expression.PropertyOrField(expression, str); } // {RequiredPlots.Developer.Id} return expression; } /// <summary> /// Changes the type. /// </summary> /// <param name="value">The value to convert.</param> /// <param name="type">The type to convert to.</param> /// <returns>The value converted to the type.</returns> public static object ChangeType(object value, Type type) { // if type is null throw exception can't // carry on nothing to convert to. if (type == null) throw new ArgumentNullException("type"); if (value == null) { // test for nullable type // (i.e. if Nullable.GetUnderlyingType(type) // is not null then it is a nullable type // OR if it is a reference type if ((Nullable.GetUnderlyingType(type) != null) || !type.IsValueType) return null; else // for 'not nullable value types' return the default value. return Convert.ChangeType(value, type); } // ==== Here we are guaranteed to have a type and value ==== // get the type either the underlying type or // the type if there is no underlying type. type = Nullable.GetUnderlyingType(type) ?? type; // Convert using the type TypeConverter converter = TypeDescriptor.GetConverter(type); if (converter.CanConvertFrom(value.GetType())) { // return the converted value return converter.ConvertFrom(value); } // Convert using the values type TypeConverter converter2 = TypeDescriptor.GetConverter(value.GetType()); if (!converter2.CanConvertTo(type)) { // if the type cannot be converted throw an error throw new InvalidOperationException( String.Format("Unable to convert type '{0}' to '{1}'", new object[] { value.GetType(), type })); } // return the converted value return converter2.ConvertTo(value, type); } #endregion
Listing 8 – IQueryable extension methods
Updated:
In the BuildWhereClause method in Listing 8 I have made a minor change that resolves a major bug:
string keyName = fkColumn.ParentTable.Name + "." + fkColumn.ParentTable.PrimaryKeyColumns[i++].Name;
has been changed to:
string keyName = fkColumn.Name + "." + fkColumn.ParentTable.PrimaryKeyColumns[i++].Name; The issue here was that you would get {RequiredPlots.Developers.Id} instead of {RequiredPlots.Developer.Id} (note the plural Developers) this was fine in Entity Framework where the entity was left as it cane out of the DB but no good for Linq to SQL which uses pluralisation.So there you have it, there’s a lot more we could do with this to streamline the code make some of the extension methods more generic etc but I think I will leave it there.
Download (UPDATED)
The download is a Web Application Project for EF bit the CascadeExtensions classes, FieldTemplate and FilterUserControl are compatible with EF and L2S.
Note: Also included are the script to create the DB and some data to import in excel format.
Updated: I’ve now added filter ordering via an extension to the FilterRepeater called SortedFilterRepeater and am mapping it in web.config, also added a general sort vi IAutoFieldGenerator on all pages
10 comments:
thanks Steve. another great article. Your blog has become my default source for learning dynamic data.
Hi Steve, if it possible please can you send me the database to run the sample project
I've only got in SQL 2008 is that OK?
Yep that's fine steve.Send it to my email roopamvishwam@gmail.com or give a download link in your blog.
Thanks
Hi Steve,
I'm using your cascading fields and filters and I love it but I noticed something a little strange. When using the OnValidation() method errors are also cascaded through the cascading templates. So, I get one error showing up next to all my drop downs that implement the CascadingFieldTemplate (and for some reason I get three validation errors at the top of the page). I'm not sure why this is happening. Do you know how to prevent this sort of behavior?
Nick
Hi Nick, I never noticed that myself but as soon as I get some free time I will look into it.
P.S. fee free to contact me via e-mail :D
Steve :D
Hi, thanks for your contribution of knowledge about Dynamic Data, I have done much.
I have a questions. I can determine the SortExpression of MetaForeignKeyColumn a personalized?. If the answer is Yes. How do I do?. I would have to create a new field templates or by means of attributes.
Thanks you.
you use the DisplayColumnAttribute to set the sort and display columns.
Steve
Hi - qwondering if you can help - in this section
public partial class ForeignKey_EditField : CascadingFieldTemplate
{
protected void Page_Init(object sender, EventArgs e)
{
// add event handler if dependee exists
if (ParentControl != null)
{
// subscribe to event
ParentControl.SelectionChanged += SelectedIndexChanged;
}
}
The ParentControl var is always null so the SelectedIndexChanged event is never getting subscribed to. I think I am doing everything right as far as implimenting your code. Any thoughts?? Thanks
Hi there, have you tried the sample?
Steve
Post a Comment