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 byAnonymous
January 11, 2011
Thanks, worked for me first try :-)Anonymous
January 11, 2011
The comment has been removedAnonymous
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 muchAnonymous
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. ThanksAnonymous
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.0Anonymous
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%')