Tuesday, 9 September 2008

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 smile_thinking.

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 smile_teeth

10 comments:

Anonymous said...

As Linq to Sql is being left behind is there going to be an example that works with Linq to Entity?

Steve said...

I'll look into it. What errors ar you getting?
Steve :D

Anonymous said...

The Error I get when I add a .edmx to the samples is 'The method 'Skip' is only supported for sorted input in LINQ to Entities'

Can these samples be easily converted to Linq to Entity?

Thanks in advance

Steve said...

It seems that Entity Framework yet again lacks somthing that is in Linq to SQL :( I don't see an issue with converting these sample to EF however as I'm now involved in Freelance work it may be some time before I get around to it. However you could try the DD Futures Filters which these are based on to work with EF and no doubt the wont then post a question on the Dynamic Data forum here http://forums.asp.net/1145.aspx hope this helps :D

Steve

Carl said...

if your DataContext ain't in the executing assembly you have to replace the line in the GetContextKey(MetaColumn column) method to:

return String.Format("{0}#{1}#{2}", column.Table.DataContextType.AssemblyQualifiedName, column.Table.Name, column.Name);

Steve said...

Thanks for that Carl, Much appreciated.

Steve :D

Pedro Ferreira said...

Thanks for the post, it gave me some lights on how to build dynamic queries.

Do you know how can we select more than one property with Expression.Call(typeof(Queryable), "Select"...)?

I've been playing around with this but couldn't find a way.

Thanks

Pedro

Anonymous said...

Download doesn't work

Anonymous said...

Hi,

Great articles, very helpful.
I was hoping if you could have more than one AutoComplete Filter and have them both be part of the filter process?

Thanks in advance.

Steve said...

yes the filtering works in DD4 is you alter the query then pass that query on to the next etc. so multiple autocompletes are OK :)

Steve