New to Telerik UI for WinForms? Download free 30-day trial

Server side auto complete for RadDropDownList

Environment

Date Posted Product Author
September 19, 2014 RadDropDownList for WinForms Georgi Georgiev

Problem:

How to dynamically fetch data from the database while typing?

Solution:

To achieve this, we can create AutoComplete providers which will send queries to the server (using EntityFramework) or filter the items in memory depending on the collection. This will work with any IEnumerable collection.

First of all lets take all the common functionality in a single class. It will be a singleton, just in case you need to use it in a multithreaded environment.

public static ExpressionBuilder Instance
{
    get
    {
        if (instance == null)
        {
            lock (syncRoot)
            {
                if (instance == null)
                {
                    instance = new ExpressionBuilder();
                }
            }
        }

        return instance;
    }
}

public bool Optimize<T>(IQueryable<T> collection)
{
    if (this.optimizationCache.Contains(typeof(T)))
    {
        return false;
    }

    this.optimizationCache.Add(typeof(T));
    collection.ToList();
    return true;
}

The Optimize method will be used to send the first query to the database and establish a connection so further fetching of the data does not take time.
Next, we need to build expressions dynamically, so we can use any data type and any property to perform the operations. You can read more about Expressions here.

public Expression<Func<T, TResult>> BuildMethodCallExpression<T, TResult>(string parameter, string property, Type ownerType,
    string methodName, int parametersCount)
{
    var param = Expression.Parameter(typeof(T));
    var constant = Expression.Constant(parameter);
    var prop = Expression.Property(param, property);
    var method = ownerType.GetMethods().First(x => x.Name == methodName && x.GetParameters().Length == parametersCount);
    var body = Expression.Call(prop, method, constant);

    return Expression.Lambda<Func<T, TResult>>(body, param);
}

public Expression<Func<T, bool>> BuildContainsExpression<T>(string property, string filter)
{
    var dataItemsExp = this.BuildMethodCallExpression<T, bool>(filter, property, typeof(String), "Contains", 1);
    return dataItemsExp;
}

public Expression<Func<T, string>> BuildSelectExpression<T>(string property)
{
    var param = Expression.Parameter(typeof(T));
    var prop = Expression.Property(param, property);
    var expression = Expression.Lambda<Func<T, string>>(prop, param);
    return expression;
}

public Expression<Func<T, bool>> BuildStartsWithExpression<T>(string property, string filter)
{
    var lambda = this.BuildMethodCallExpression<T, bool>(filter, property, typeof(String), "StartsWith", 1);
    var newBody = Expression.And(lambda.Body,
        Expression.NotEqual(Expression.Property(lambda.Parameters.First(), property), Expression.Constant(filter)));
    var newExpression = Expression.Lambda<Func<T, bool>>(newBody, lambda.Parameters);

    return newExpression;
}

Now we need to create our auto complete helpers. They will take IEnumerable in their constructor. Then this IEnumerable will be converted to IQueriable to allow queries to be build using our ExpressionBuilder. Now we need to create our auto complete helpers. They will take IEnumerable in their constructor. Below you can see the implementation of the append helper:

public class ServerAutoCompleteAppendHelper<T> : AutoCompleteAppendHelper
{
    public IQueryable<T> Data { get; private set; }

    public ServerAutoCompleteAppendHelper(RadDropDownListElement owner, IEnumerable<T> data)
        : base(owner)
    {
        this.Data = data.AsQueryable();
        ExpressionBuilder.Instance.Optimize(this.Data);
    }

    public override void AutoComplete(KeyPressEventArgs e)
    {
        string findString = this.CreateFindString(e);

        var whereExp = ExpressionBuilder.Instance.BuildStartsWithExpression<T>(this.Owner.AutoCompleteValueMember, findString);
        var selectExp = ExpressionBuilder.Instance.BuildSelectExpression<T>(this.Owner.AutoCompleteValueMember);

        string result = this.Data.Where(whereExp).Select(selectExp).OrderBy(x => x.Length).FirstOrDefault();
        if (result != null)
        {
            Owner.EditableElementText = result;
            Owner.SelectionStart = findString.Length;
            Owner.SelectionLength = Owner.EditableElementText.Length;
            e.Handled = true;
        }
    }

    private string CreateFindString(KeyPressEventArgs e)
    {
        string findString = "";
        if (Owner.SelectionLength == 0)
        {
            findString = Owner.EditableElementText + e.KeyChar;
        }
        else
        {
            findString = Owner.EditableElementText.Substring(0, Owner.SelectionStart) + e.KeyChar;
        }

        return findString;
    }
}

The suggest helper is quite similar, the difference is that it needs to fill the dropdown with items. The MaxItems property will limit how many items are shown at a time in the dropdown list, improving the performance:

public class ServerAutoCompleteSuggestHelper<T> : AutoCompleteSuggestHelper
{
    public IQueryable<T> Data { get; private set; }
    public int MaxItems { get; set; }

    public ServerAutoCompleteSuggestHelper(RadDropDownListElement owner, IEnumerable<T> data,
        int maxItems = 1000)
        : base(owner)
    {
        this.Data = data.AsQueryable();
        this.MaxItems = maxItems;
        ExpressionBuilder.Instance.Optimize(this.Data);
    }

    public override void ApplyFilterToDropDown(string filter)
    {
        this.DropDownList.BeginUpdate();

        this.DropDownList.ListElement.Items.Clear();

        var dataItemsExp = ExpressionBuilder.Instance.BuildContainsExpression<T>(this.Owner.AutoCompleteValueMember, filter);
        var dataItemsQuery = this.Data.Where(dataItemsExp).Take(this.MaxItems);
        var dataItems = dataItemsQuery.ToList();

        var selectExp = ExpressionBuilder.Instance.BuildSelectExpression<T>(this.Owner.AutoCompleteValueMember);
        var displayItemsQuery = dataItemsQuery.Select(selectExp);
        var displayItems = displayItemsQuery.ToList();

        for (int i = 0; i < dataItems.Count; i++)
        {
            var dataItem = dataItems[i];
            var displayMember = displayItems[i];
            this.DropDownList.ListElement.Items.Add(new RadListDataItem(displayMember, dataItem));
        }

        this.DropDownList.EndUpdate();
        this.Owner.SelectionLength = this.Owner.Text.Length;
    }
}

Now, you only need to create the helpers and assign them some data:

RadDropDownList list = new RadDropDownList
{
    Parent = this,
    Dock = DockStyle.Top
};

LargeDataEntities dbContext = new LargeDataEntities();
list.AutoCompleteValueMember = "Name";

list.DropDownListElement.AutoCompleteSuggest =
    new ServerAutoCompleteSuggestHelper<Datum>(list.DropDownListElement, dbContext.Data);

list.DropDownListElement.AutoCompleteAppend = new ServerAutoCompleteAppendHelper<Datum>(list.DropDownListElement, dbContext.Data);

A complete solution in C# and VB.NET can be found here.

In this article