Dynamic Data Futures – Part 3 – AnyColumnAutocomplete Filter

1. Part 1 - Getting Dynamic Data Futures filters working in a File Based Website.
2. Part 2 - Create the AnyColumn filter from the Dynamic Data Futures Integer filter.
3. Part 3 – Creating the AnyColumnAutocomplete filter.

Creating the AnyColumnAutocomplete filter

This version of the Autocomplete filter was adapted in response to this article on the Dynamic Data forum by levalencia.

The issue is that the Autocomplete filter and AutocompleteFilter web service is designed to work with Foreign Key columns, I thought this would be like the previous article in this series and be quite simple, instead it turned out to be a bit more complicated .

Making the AnyColumnAutocomplete FieldTemplate.

The issue initially was that the Autocomplete filter was assuming MetaForeignKeyColumn fkColumn and then referencing the ParentTable from it this meant that we needed a new FieldTemplate no just some tweaking.

protected void Page_Init(object sender, EventArgs e)
{
    var fkColumn = Column as MetaForeignKeyColumn;

    //// dynamically build the context key so the web service knows which table we're talking about
    autoComplete1.ContextKey = AutocompleteFilterService.GetContextKey(fkColumn.ParentTable);
    ...
}

Listing 1 – Autocomplete filter Page_Init

And then in the AutocompleteFilter web service has no reference to the column this meant that either we create a new web service of modify the current I decided to modify it as there were only a few new methods needed to handle the non foreign key columns.

So without any further ado here’s the changes to the FieldTemplate to make the AnyColumnAutocomplete filter.

We remove the line:

var fkColumn = Column as MetaForeignKeyColumn;

and replace all referenced to fkColumn.PartentTable with Column.Table or Column

e.g. change

MetaTable parentTable = fkColumn.ParentTable;

to

MetaTable parentTable = Column.Table;

And change the following line

autoComplete1.ContextKey = AutocompleteFilterService.GetContextKey(fkColumn.ParentTable);

to

autoComplete1.ContextKey = AutocompleteFilterService.GetContextKey(Column);

And that's the changes to make the AnyColumnAutocomplete filter.

Now the changes to the AutocompleteFilter web service

The first change to the AutocompleteFilter web service was to add an overloaded GetContextKey that takes a MetaTable as remember this line from above:

autoComplete1.ContextKey = AutocompleteFilterService.GetContextKey(Column);

So the overloaded method takes a MetaColumn as it’s parameter not MetaTable:

public static string GetContextKey(MetaColumn column)
{
    return String.Format("{0}#{1}#{2}", column.Table.DataContextType.FullName, column.Table.Name, column.Name);
}

Listing 2 – GetContextKey method

Note: That from the column it gets similar properties as the original but also get the Column.Name

Next the changes to the GetCompletionList which returns the list of results.

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] GetCompletionList(string prefixText, int count, string contextKey) {
    MetaTable table = GetTable(contextKey);

    IQueryable queryable = BuildFilterQuery(table, prefixText, count);

    return queryable.Cast<object>().Select(row => CreateAutoCompleteItem(table, row)).ToArray();
}

Listing 3 – GetCompletionList original method

[System.Web.Services.WebMethod]
[System.Web.Script.Services.ScriptMethod]
public string[] GetCompletionList(string prefixText, int count, string contextKey)
{
    MetaTable table;
    String[] param = contextKey.Split('#');

    if (param.Length > 2)
    {
        table = GetTable(contextKey);
        var list = BuildFilterQuery(table, prefixText, count, param[2]);

        return list;
    }
    else
    {
        table = GetTable(contextKey);
        IQueryable queryable = BuildFilterQuery(table, prefixText, count);

        return queryable.Cast<object>().Select(row => CreateAutoCompleteItem(table, row)).ToArray();
    }
}

Listing 4 – GetCompletionList modified method

In the modified version we split the contectKey into param local variable and check the number of entries to determine whether it’s dealing with ForeignKey column or any other column.

Also note the minor change to the GetTable method where the number of parameters is check and added a check for 2 OR 3 paramers.

Debug.Assert(param.Length == 2  param.Length == 3, String.Format("The context key '{0}' is invalid", contextKey));

After this I have added another overloaded method for the BuildFilterQuery which also takes the column name.

private static String[] BuildFilterQuery(
    MetaTable table,
    string prefixText,
    int maxCount,
    String columnName)
{
    var column = table.GetColumn(columnName);

    // query = {Table(Customer)}
    var query = table.GetQuery();

    // row
    // entityParam = {row}
    var entityParam = Expression.Parameter(column.Table.EntityType, "row");

    // row.DisplayName
    //var property = Expression.Property(entityParam, columnName);
    //property = {row.City}
    var property = Expression.Property(entityParam, column.EntityTypeProperty);

    // row => row.Property
    // columnLambda = {row => row.City}
    var columnLambda = Expression.Lambda(property, entityParam);

    // "prefix"
    // constant = {"Lo"}
    var constant = Expression.Constant(prefixText);

    // row.DisplayName.StartsWith("prefix")
    // startsWithCall = {row.City.StartsWith("Lo")}
    var startsWithCall = Expression.Call(
        property,
        typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) }),
        constant);

    // row => row.DisplayName.StartsWith("prefix")
    // whereLambda = {row => row.City.StartsWith("Lo")}
    var whereLambda = Expression.Lambda(startsWithCall, entityParam);

    // Customers.Where(row => row.DisplayName.StartsWith("prefix"))
    // whereCall = {Table(Customer).Where(row => row.City.StartsWith("Lo"))}
    var whereCall = Expression.Call(
        typeof(Queryable),
        "Where",
        new Type[] { table.EntityType },
        query.Expression,
        whereLambda);

    // query.Select(row => row.Property)
    // selectCall = {Table(Customer).Where(row => row.City.StartsWith("Lo")).Select(row => row.City)}
    var selectCall = Expression.Call(
        typeof(Queryable),
        "Select",
        new Type[] { query.ElementType, columnLambda.Body.Type },
        whereCall,
        columnLambda);

    // query.Select(row => row.Property).Distinct
    // distinctCall = {Table(Customer).Where(row => row.City.StartsWith("Lo")).Select(row => row.City).Distinct()}
    var distinctCall = Expression.Call(
        typeof(Queryable),
        "Distinct",
        new Type[] { column.EntityTypeProperty.PropertyType },
        selectCall);

    // Customers.Where(row => row.DisplayName.StartsWith("prefix")).Take(20)
    // distinctCall = {Table(Customer).Where(row => row.City.StartsWith("Lo")).Select(row => row.City).Distinct().Take(20)}
    var takeCall = Expression.Call(
        typeof(Queryable),
        "Take",
        new Type[] { typeof(String) },
        distinctCall,
        Expression.Constant(maxCount));

    var result = query.Provider.CreateQuery(takeCall);
    List<String> list = new List<string>();
    foreach (var item in result)
    {
        list.Add(AutoCompleteExtender.CreateAutoCompleteItem(item.ToString(), item.ToString()));
    }

    return list.ToArray();
    //return query.Provider.CreateQuery(distinctCall);
}

Listing 5 – overloaded BuildFilterQuery method

Listing 5 essentially does what Sample 1 does; which get the first two cities from the Customer table where they begin with “Br” and makes sure they are distinct.

var DC = new NWDataContext();
var q = (from c in DC.Customers
        where c.City.StartsWith("Br")
        select c.City).Distinct().Take(2);

Sample 1 – getting the first 2 customer whose City starts with “Br”

And then returns an array that is ready to pass back to the AjaxToolkit Autocomplete control.

And here’s the Metadata classes

[MetadataType(typeof(Customer_MD))]
public partial class Customer
{
    public class Customer_MD
    {
        [Filter(FilterControl = "AnyColumnAutocomplete")]
        public object City { get; set; }
    }
}

Listing 6 – metadata classes

Website project file (not including Dynamic Data Futures project)

Hope this helps