ASP.NET Dynamic Data – Text filter / Search

2011-03-01

I’ve implemented a Filter to apply on text fields in ASP.NET Dynamic Data websites.

To use it: include the code in your project by putting it in the DynamicData\Filters folder. Then apply the filter to the field you want to enable filtering on using the FilterUIHint attribute.

Example:

[MetadataType(typeof(Category_MetaData))]
public partial class Category
{
  class Category_MetaData
  {
    [FilterUIHint("TextFilter")]
    public string Description { get; set; }
  }
}

You have to manually trigger the application of the query filter by adding a button to the appropriate template. For example, add this to the List.aspx template:

in the code behind for that template:

protected void SearchButton_Click(object sender, EventArgs e)
{
  ((IQueryableDataSource)this.GridDataSource)
      .RaiseViewChanged();
}

If you want to see it in action, you can download the full sample code, which is based on the famous Northwind database on SQLExpress.

I got a lot of inspiration and help from msdn and this blog post to get to a decent implementation for this.

Like always I’ve compressed the code using 7-zip. The best compression tool. Ever.

12 responses to “ASP.NET Dynamic Data – Text filter / Search”

Steve

2011-03-11

Hi Jeroen,
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
Moreover, should I also use
ParameterExpression parameter = Expression.Parameter(source.ElementType, String.Empty);
instead of
ParameterExpression parameter = Expression.Parameter(source.ElementType);

Thank you so much

Reply
Jeroen Trappers

2011-03-16

If I test the code, it seems to be case insensitive on my machine. However that may be due to how the database handles the query. If you are using another data source, that may indeed be a problem.

You could instead of using the “Contains”, “StartsWith” or “EndsWith” method, you should use IndexOf(), as described in the answer on this question on StackOverflow:
http://stackoverflow.com/questions/444798/case-insensitive-containsstring

The Expression that you could use will be something like this then. It doesn’t work with OrdinalIgnoreCase on my machine however. (Linq 2 Entities doesn’t support it.)

instead of having this for contains:

case “contains”:
comparison = Expression.Call(property, typeof(string).GetMethod(“Contains”, new Type[] { typeof(string) }), value);
break;

You could try this:

case “contains”:
comparison = Expression.GreaterThan(
Expression.Call(property, typeof(string).GetMethod(“IndexOf”, new Type[] { typeof(string), typeof(StringComparison) }), value, comp),
Expression.Constant(0));
break;
// Here the comp parameter is a ConstantExpression(StringComparison.OrdinalIgnoreCase)

Or try this:

case “contains”:
comparison = Expression.GreaterThanOrEqual(
Expression.Call(property, typeof(string).GetMethod(“IndexOf”, new Type[] { typeof(string) }), value),
Expression.Constant(0));
break;

Reply
Jeroen Trappers

2011-03-16

Note that another option is to have a “UPPERCASE” version of the field you want to search on in the database, do the search term to uppercase, and compare to that field in the database. This is probably the best way to do it, if you’re having issues getting it to work. The down-side is, that it takes some space on the database.

Reply
Steve

2011-03-17

Thanks Jeroen for the 3 approach
Approach 1: i can’t work this out as LINQ2Entities not support for “IndexOf ” overloading
Approach 2: still got case sensitive result
Approach 3: how can i include both GetMethod “toUpper” and “Contains” in the same Expression.Call statement ?

Sorry for my newbie questions Thanks again for your guidance.

FYR. I have also tried using db features such as NLS_COMP and NLS_ORDER [_CI] but I’m using Oracle9i and I can only make it case-insensitive match on ‘=’ operator but not ‘like’ operator
so i need to do it on the coding level …..
http://www.orafaq.com/node/999
Setting NLS_COMP to ANSI causes Oracle9i to use the sort order specified in NLS_SORT when doing an ORDER BY. But one big limitation is that when NLS_COMP is set to ANSI, only certain SQL functions and operations will use the NLS_SORT sort order. The rest will still use the default BINARY sort order. For example, the = (equality) operator will do a case-insensitive comparison, but the “like” operator will not:

Reply
Jeroen Trappers

2011-03-18

Hi Steve,
You shouldn’t try to do the 3rd approach. Just convert the input value of the textbox toUpper before creating the expression. It doesn’t need to be part of the expression that you hand to Linq2Entities. (I tried it, and it is not supported by Linq2Entities)

So change this line of code in the filter control (code behind):

string filterValue = this.textBox.Text;

To this:
string filterValue = this.TextBox.Text.ToUpper();

the rest is the same as the original implementation. You need to make sure that you apply the search field to a column that has been “pre-calculated” in the database to be all Uppercase. You could use ToUpperInvariant() as well.

If you would like to try the ToUpper combined with Contains: this is the expression to do that:

case “contains”:
var upperProperty = Expression.Call(property, typeof(string).GetMethod(“ToUpperInvariant”));

comparison = Expression.Call(upperProperty, typeof(string).GetMethod(“Contains”, new Type[] { typeof(string) }), value);

break;

Hope this helps

Reply
Steve

2011-03-21

Thanks Jeroen again for your kindly help.

The uppercase search is working now by using
var upperProperty = Expression.Call(property, typeof(String).GetMethod(“ToUpper”, new Type[] { }));

The google search rank of this post has become higher now. Great!
EDIT: –> I remove your urls, as they are not pointing to anything relevant. I see this as spam, so please don’t do it.

Reply
riper

2011-03-29

I’ve tried adding this to my dynamic data web site but the controls don’t show up. Should
[FilterUIHint(“TextFilter”)]
public string Description { get; set; }
be enough to make it show the TextFilter’s dropdownlist and textbox? I feel there is a lot of magic going on here, please help me! 🙂

ps. I haven’t tried with your project since I don’t have the NorthWind Database.

Thanks for a great initiative!

Reply
jeroen.trappers

2011-03-30

Hi Riper,

Make sure that you apply the FilterUIHint attribute on the right property, and that the class that contains the property is \glued\ to the generated partial class with the MetadataType attribute, like in the example. Also make sure that you have the ascx and code behind file for the user control in the right location, namely Filter.

You can download Northwind for free, link is in the post. Easy to setup, just copy and paste the Northwind.mdf and .ldf file in the App_Data folder, double click and there you go.

Good luck, let me know if you have trouble getting it going.

Reply
Alexander

2011-07-25

Hi Jeroen,

I do not work .. Followed step by step the tutorial but the only thing I see is the “Search” button. I do not see the TextBox (TextFilter.ascx).

Help me? Thanks!

Reply
1. Jeroen Trappers
  
  2011-08-01
  
  Try running the full example, for which I include the source code. If you still have problems, I can’t help you without seeing your code.
  
  Reply
Esben

2011-08-05

Thank you very much for this post! Helped me a lot!! 🙂

Reply
Lucio Fassio

2012-03-23

Great ! added search like a breeze, thank you for posting

Reply

Appmire blog

ASP.NET Dynamic Data – Text filter / Search

Like this:

12 responses to “ASP.NET Dynamic Data – Text filter / Search”

Leave a ReplyCancel reply