Text Filtering with Dynamic Data

Dynamic Data provides a good architecture for adding custom filters to your application. To read more about understanding filtering read the following post

In this post I am going to take you through a walkthrough of how you can write your own search filter. This sample builds on writing LinqExpressions. If you want more information about Linq Expressions you can learn more from here

1. Add a FilterUserControl  called “Search” to the DynamicData/Filters template(You can use the one attached to this post)

The Search user control has a textbox which takes in the search input and a button control

2. In the Code behind of search.ascx override the GetQueryable() method to return the filtered query as follows

 public override IQueryable GetQueryable(IQueryable source)
    {

        if (Column.TypeCode != TypeCode.String)
            return source;


        string searchString = TextBox1.Text;
        if (TextBox1.Text == "")
            searchString = String.Empty;
        Type type = typeof(String);



        string searchProperty = Column.DisplayName;
        ConstantExpression searchFilter = Expression.Constant(searchString);


        ParameterExpression parameter = Expression.Parameter(source.ElementType);
        MemberExpression property = Expression.Property(parameter, this.Column.Name);
        if (Nullable.GetUnderlyingType(property.Type) != null)
        {
            property = Expression.Property(property, "Value");

        }
        MethodInfo method = typeof(String).GetMethod("Contains", new[] { typeof(String) });

        var containsMethodExp = Expression.Call(property, method, searchFilter);
        var containsLambda = Expression.Lambda(containsMethodExp, parameter);


        var resultExpression = Expression.Call(typeof(Queryable), "Where", new Type[] { source.ElementType }, source.Expression, Expression.Quote(containsLambda));

        return source.Provider.CreateQuery(resultExpression);

    }

 3. Add an event to Raise the Filter changes event. In this sample it is the ButtonClicked() event as shown

  protected void Button1_Click(object sender, EventArgs e)
    {
        OnFilterChanged();
    }

4. Add FilterUIHint(“Search”) to a string column eg. ProductName column in Products table of Northwind database.

You can download this sample that has the Search.ascx user control from here

Comments

• Anonymous
  October 13, 2010
  Its great, ive been trying this for hours, but i was freeze in GetQueryable method, however, i would like this control to have a single instance for all fields i decore with the FilterUIHint attrbute, can you give me a clue on how to do this?. Tanks in advance.

• Anonymous
  October 13, 2010
  Can you please explain more what you want. Every instance of FilterUiHint is tied to a column that it filters on.

• Anonymous
  November 11, 2010
  Hi !! I followed the Oleg's tutorial but I noticed I can filter only columns from Table "Orders" , how can I switch to another table? , you did it with the table Products...

• Anonymous
  November 13, 2010
  You need to put the FilterUIHint(msdn.microsoft.com/.../dd411802.aspx) to column of the table you want to filter by

• Anonymous
  January 11, 2011
  Thanks, worked for me first try :-)

• Anonymous
  January 11, 2011
  The comment has been removed

• Anonymous
  March 13, 2011
  Hi pranav, You have a great work and have a top rank in google search I’ve deployed your coding but it is a case sensitive search How can I use case in-sensitive search ? Please kindly advise. System.StringComparison.OrdinalIgnoreCase Thank you so much

• Anonymous
  March 14, 2011
  Can you do the same for an int column? Thanks for a great article.

• Anonymous
  March 14, 2011
  Hi mb.  FYR below.  It is a integer exact match Any experts can advise the case insensitive search ? many thx    public override IQueryable GetQueryable(IQueryable source)    {        int searchInt =  0 ;        // Match DB Column Type = Integer            if (Column.TypeCode != TypeCode.Int32)                return source;            if (String.IsNullOrEmpty(TextBox1.Text))                return source;            if (int.TryParse(TextBox1.Text, out searchInt) == false)                    TextBox1.ToolTip = "Input correct code";        Type type = typeof(Int32);        string searchProperty = Column.DisplayName;        ConstantExpression searchFilter = Expression.Constant(searchInt);        ParameterExpression parameter = Expression.Parameter(source.ElementType);        MemberExpression property = Expression.Property(parameter, this.Column.Name);        if (Nullable.GetUnderlyingType(property.Type) != null)        {            property = Expression.Property(property, "Value");        }        MethodInfo method = typeof(Int32).GetMethod("Equals", new[] { typeof(Int32) });        var containsMethodExp = Expression.Call(property, method, searchFilter);        var containsLambda = Expression.Lambda(containsMethodExp, parameter);        var resultExpression = Expression.Call(typeof(Queryable), "Where", new Type[] { source.ElementType }, source.Expression, Expression.Quote(containsLambda));        return source.Provider.CreateQuery(resultExpression);    }    protected void Page_Load(object sender, EventArgs e)    {        this.TextBox1.ToolTip = this.Column.DisplayName;        this.validator.ErrorMessage = "Invalid number format specified for " +                  this.Column.DisplayName;              this.validator.ToolTip = this.validator.ErrorMessage;    }    protected void Validate(object sender, ServerValidateEventArgs e)    {      int value;      e.IsValid = int.TryParse(e.Value, out value);    }

• Anonymous
  March 14, 2011
  Thanks for the code :) I don't get it to work though: I added the Search.aspx, Search.aspx.cs, Search.aspx.designer.cs etc and in Annotations.cs I set public class Customer_ReportingMetaData    {        [DisplayName("Customer Id")]        [UIHint("TextReadOnly")]        [FilterUIHint("Search")]        public int CustomerId { get; set; } it compiles but the search textbox is not showing up, what magic is it that will show it? I guess I'm missing something.

• Anonymous
  March 29, 2011
  Hi, Thanks for a very good post. I have a question though, I have downloaded and added the "Search" FilterUserControl, but how do I use it? I want the textbox search instead of a dropdown list used in this code:¨ <asp:FilterRepeater ID="FilterRepeater" runat="server">                <ItemTemplate>                    <asp:Label runat="server" Text='<%# Eval("DisplayName") %>' AssociatedControlID="DynamicFilter$DropDownList1" />                    <asp:DynamicFilter runat="server" ID="DynamicFilter" OnSelectedIndexChanged="OnFilterSelectedIndexChanged" />                </ItemTemplate           </asp:FilterRepeater> How do I rewrite the above code to use the "Search" user control (textbox + button) instead of the dropdownlist above? I know I have to tie the Search user control to one or multiple database columns, but at the moment I cannot see the textbox. Would be grateful for advice.

• Anonymous
  September 18, 2011
  Hi, Having problem in downloading Search.ascx control, can you pls provide alternate location to get it. Thanks

• Anonymous
  September 18, 2011
  Kushi, can you please try again. I just tried the link and it is accessible.

• Anonymous
  September 18, 2011
  Hi, Pranav, When i click "here" this is the link its opening 'skydrive.live.com/.../TextSearchFilter and showing page cannot be displayed. Anyway can u pls update me whether using FilterUIHint(“Search”) in VS2008 is possible? When i google it comes under V4.0

• Anonymous
  October 17, 2013
  Is it possible to change the column value to upper and then do the search in the above code, if yes can you suggest me how this can be modified? I want something like below Upper(columnName) Like Upper('%somevalue%')