Case insensitive search

Jul 17, 2012 at 4:38 PM

It was suggested to me that the built-in grid search should be case-insensitive.  I made the following changes that appear to make this happen:

GridCustomerBindingHelperBase.cs  in ConvertFilterValue():

        protected object ConvertFilterValue(FilterDescriptor filterDescriptor)
        {
            var gridPropertyInfo = GetGridPropertyInfo(filterDescriptor.Member);

            var value = filterDescriptor.Value;

            var hasResolver = _valueResolvers.HasResolverFor<TEntity, TViewModel>(gridPropertyInfo, value.GetType());

            if (hasResolver)
            {
                return _valueResolvers.Resolve<TEntity, TViewModel>(gridPropertyInfo, value);
            }

            // The filterDescriptor.Value returns a decimal if the property type was a long!
            if (gridPropertyInfo.PropertyType == typeof(long))
            {
                return Convert.ToInt64(value);
            }

            // 2012-07-17 - JLEWIS - Convert string values to upper-case invariant for comparison purposes
            // See corresponding change to predicate in GridPropertyInfo.cs line 18
            if (gridPropertyInfo.PropertyType == typeof(string))
            {
                return ((string) value).ToUpperInvariant();
            }

            //return Convert.ChangeType(value, gridPropertyInfo.PropertyType);
            return value;
        }

 

In GridPropertyInfo.cs constructor:

        public GridPropertyInfo(Type propertyType, string propertyPath)
        {
            Guard.IsNotNull(() => propertyType);
            Guard.IsNotNullOrEmpty(() => propertyPath);

            PropertyType = propertyType;
            // 2012-07-17 - JLEWIS - Converting property values to Upper-case invariant for comparison purposes
            // See corresponding change in GridCustomBindingHelperBase line 233
            PropertyPath = propertyPath.ToUpperInvariant();
        }

Jul 26, 2012 at 11:01 PM

For some reason this causes an issue when filtering on strings containing an "."

Coordinator
Jul 26, 2012 at 11:17 PM

Thanks for the tip, I'm reviewing and refining the code for the final release, then I will analyze this problem and keep you informed.

Coordinator
Aug 5, 2012 at 5:51 AM

I get to a hypothetical solution, but there is a side effect, the implementation that I developed works well with linq-to-nhibernate and QueryOver, but it will not work with EntityFramwork (please read this answer on stackoverflow: http://stackoverflow.com/a/3843382/401576)

I still have not compiled the binaries, but you can see the code in these two tests: CaseInsensitiveSearchTests and DBCaseInsensitiveSearchTests

Basically it works like this: to use case insensitive search in any and every query add this setting at the beginning of your application;

GridModelMapper.Configuration.UseCaseInsensitiveSearch();

Or if you want more granular control, you can use this;

var gridCustomBindingHelper = new GridCustomBindingHelper<Foo, FooModel>(command, data).UseCaseInsensitiveSearch();

I hope this may prove useful for you, feel free to express any opinion.

 

Tanks.

http://stackoverflow.com/a/3843382/401576
Aug 5, 2012 at 3:07 PM
Awesome! I will try it out tomorrow.
Thank you, sir.


From: Luis_Fernando [email removed]
Sent: Saturday, August 04, 2012 11:51 PM
To: Lewis, John
Subject: Re: Case insensitive search [TGH:371331]

From: Luis_Fernando

I get to a hypothetical solution, but there is a side effect, the implementation that I developed works well with linq-to-nhibernate and QueryOver, but it will not work with EntityFramwork (please read this answer on stackoverflow: http://stackoverflow.com/a/3843382/401576)

I still have not compiled the binaries, but you can see the code in these two tests: CaseInsensitiveSearchTests and DBCaseInsensitiveSearchTests

Basically it works like this: to use case insensitive search in any and every query add this setting at the beginning of your application;

GridModelMapper.Configuration.UseCaseInsensitiveSearch();

Or if you want more granular control, you can use this;

var gridCustomBindingHelper = new GridCustomBindingHelper<Foo, FooModel>(command, data).UseCaseInsensitiveSearch();

I hope this may prove useful for you, feel free to express any opinion.

Tanks.

http://stackoverflow.com/a/3843382/401576

______________________________________________________

This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended exclusively for the person(s) to whom it is addressed. Any use, copying, retention or disclosure by any person other than the intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended recipient or their designee, please notify the sender immediately by return e-mail and delete all copies.
Aug 6, 2012 at 5:05 PM

I have reviewed the code and it looks good.  From my further research I have seen that settings on the database side also have a significant impact on how this will behave.  When using Oracle 11g, I will likely need to use a combination of your solution and either Oracle session parameters or a custom view.  I'll reply as soon as I get some final results.

Aug 7, 2012 at 4:11 PM

Due to specific requirements from the business owners of my project, I am simply going to use a view on the database side to make searchable data upper-case and automatically convert the search parameters entered by the user to upper case.  This solution does not require using any of your new code, but I think the case-sensitivity selection will prove useful in the future.  Thanks for taking the time to investigate this issue.

Coordinator
Aug 8, 2012 at 12:33 AM

The good news is that you get a solution to your problem.

Oct 23, 2012 at 7:02 PM

Ah, now I gotta do case insensitive search for my application!  Going to give this a try, let you know how it goes.

Oct 23, 2012 at 7:04 PM

Please do. I appreciate it.

From: Noyabronok [email removed]
Sent: Tuesday, October 23, 2012 1:03 PM
To: Lewis, John
Subject: Re: Case insensitive search [TGH:371331]

From: Noyabronok

Ah, now I gotta do case insensitive search for my application! Going to give this a try, let you know how it goes.


______________________________________________________

This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended exclusively for the person(s) to whom it is addressed. Any use, copying, retention or disclosure by any person other than the intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended recipient or their designee, please notify the sender immediately by return e-mail and delete all copies.
Oct 31, 2012 at 6:04 PM

It works, but I'm not sure that I will use it for performance reasons.  TGH applies a `tolower` function to the column which prevents the index from being used.  This is OK if all the string values are varying cases, but I have a bunch of columns where the values are always capitalized.

What would help me out is to apply TGH's case insensitive search per column with the following two options:

  1. Apply `tolower` or `toupper` on the column and the value being searched (same as what TGH has now but more granular).
  2. Let me choose through a delegate what functions I want to apply to the filtered value provided by the user.  For my purposes I would choose something like (x => x.Trim().ToUpper()) 

I would apply option 1 for columns where case is not guaranteed, and then use option 2 for columns where case is guaranteed.  Wherever I would use option 2 I would also get the benefit of the index on that column should one exist.

Oct 31, 2012 at 6:33 PM

It sounds like you are describing the need to have a pre-filter expression made available for each column in the grid. I guess the expression would be set at the column level:

columns.Bound(c => c.UserName).FilterWithExpression(filter => filter.ToUpper());

FilterWithExpression would be an extension method with an expression as a parameter:

public virtual GridBoundColumnBuilder<TModel> FilterWithExpression(this GridBoundColumnBuilder<TModel> column, Expression<Func<TModel, TValue>> preFilter)

{

// ???

}

That's very rough. I've been studying how to implement extension methods and Expressions, but I don't quite get it yet.

Thanks,

John

From: Noyabronok [email removed]
Sent: Wednesday, October 31, 2012 12:05 PM
To: Lewis, John
Subject: Re: Case insensitive search [TGH:371331]

From: Noyabronok

It works, but I'm not sure that I will use it for performance reasons. TGH applies a `tolower` function to the column which prevents the index from being used. This is OK if all the string values are varying cases, but I have a bunch of columns where the values are always capitalized.

What would help me out is to apply TGH's case insensitive search per column with the following two options:

1. Apply `tolower` or `toupper` on the column and the value being searched (same as what TGH has now but more granular).

2. Let me choose through a delegate what functions I want to apply to the filtered value provided by the user. For my purposes I would choose something like (x => x.Trim().ToUpper())

I would apply option 1 for columns where case is not guaranteed, and then use option 2 for columns where case is guaranteed. Wherever I would use option 2 I would also get the benefit of the index on that column should one exist.


______________________________________________________

This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended exclusively for the person(s) to whom it is addressed. Any use, copying, retention or disclosure by any person other than the intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended recipient or their designee, please notify the sender immediately by return e-mail and delete all copies.
Oct 31, 2012 at 6:41 PM

I would prefer to see this modification within TGH and not as an extension for Telerik's fluent grid definition which generates browser side code.  TGH knows what the models are used before `BuildGridModel` is called so I guess we can specify per column filter settings at that point.

Coordinator
Nov 9, 2012 at 12:41 PM

Hello guys, I'm hearing you, and you now have more granular control over case-insensitive searchs, now you can;

  1. Use case-insensitive search on both client and server side, just as before. Like this: 
    //globally
    GridModelMapper.Configuration.UseCaseInsensitiveSearch();
    
    //by query
    GridCustomBindingHelper<Foo, FooModel>(command, queryable).UseCaseInsensitiveSearch();
    
  2. Use case-insensitive search only on client side. Like this: 
    //globally
    GridModelMapper.Configuration.UseCaseInsensitiveSearch(options => options.ClientSideOnly());
    
    //by query
    GridCustomBindingHelper<Foo, FooModel>(command, queryable).UseCaseInsensitiveSearch(options => options.ClientSideOnly());
    
  3. Use case-insensitive search only on server side. Like this: 
    //globally
    GridModelMapper.Configuration.UseCaseInsensitiveSearch(options => options.ServerSideOnly());
    
    //by query
    GridCustomBindingHelper<Foo, FooModel>(command, queryable).UseCaseInsensitiveSearch(options => options.ServerSideOnly());
    
  4. Use case-insensitive search on specific property or set of properties, like this:
    //single property
    GridCustomBindingHelper<Foo, FooModel>(command, queryable).UseCaseInsensitiveSearch(options => options.Use(foo => foo.Name));
    
    //set of properties
    GridCustomBindingHelper<Foo, FooModel>(command, queryable).UseCaseInsensitiveSearch(options => options.Use(foo => new { foo.Name, foo.Description })));
    
  5. Ignore case-insensitive search on specific property or set of properties, like this:
    //single property
    GridCustomBindingHelper<Foo, FooModel>(command, queryable).UseCaseInsensitiveSearch(options => options.Ignore(foo => foo.Name));
    
    //set of properties
    GridCustomBindingHelper<Foo, FooModel>(command, queryable).UseCaseInsensitiveSearch(options => options.Ignore(foo => new { foo.Name, foo.Description })));
    
  6. You can also mix options, like this:
    GridCustomBindingHelper<Foo, FooModel>(command, queryable).UseCaseInsensitiveSearch(options => options.ClientSideOnly().Use(foo => foo.LastName));
    
    GridCustomBindingHelper<Foo, FooModel>(command, queryable).UseCaseInsensitiveSearch(options => options.ClientSideOnly().Ignore(foo => new { foo.FirstName, foo.Description }));
    

I hope this is helpful to you and tell me if something is wrong.

Coordinator
Nov 9, 2012 at 12:59 PM

Oh, I forgot one thing, it was possible to use the ValueResolvers to add custom functions, but I improved it further by adding the same ValueResolvers to queries.

So you can do things like "Trim().ToUpper()" on values, this can also be configured for all columns, only one or a few, or even ignore specific columns.

It is a very complex functionality, so I ask you to see the details of the implementation in unit tests.

Nov 9, 2012 at 3:14 PM

Thanks so much for all your hard work!

Nov 9, 2012 at 3:27 PM

Excellent. I can't wait to try it out. Thank you, sir.

From: Luis_Fernando [email removed]
Sent: Friday, November 09, 2012 7:00 AM
To: Lewis, John
Subject: Re: Case insensitive search [TGH:371331]

From: Luis_Fernando

Oh, I forgot one thing, it was possible to use the ValueResolvers to add custom functions, but I improved it further by adding the same ValueResolvers to queries.

So you can do things like "Trim().ToUpper()" on values, this can also be configured for all columns, only one or a few, or even ignore specific columns.

It is a very complex functionality, so I ask you to see the details of the implementation in unit tests.


______________________________________________________

This e-mail transmission may contain information that is proprietary, privileged and/or confidential and is intended exclusively for the person(s) to whom it is addressed. Any use, copying, retention or disclosure by any person other than the intended recipient or the intended recipient's designees is strictly prohibited. If you are not the intended recipient or their designee, please notify the sender immediately by return e-mail and delete all copies.
Nov 9, 2012 at 5:39 PM

Are the new features only for use with NHibernate?  I'm working my way through the source and I might be getting confused. 

Separate issue: For some reason the TelerikMvcGridCustomBindingHelper.NHibernate project won't load.  I get an MsBuild error: "Cannot evaluate the item metadata "%(FullPath)".  The item metadata "%(FullPath)" cannot be aplied to the path "bin\Debug\CodeContracts\TelerikMvcGridCustomBindingHelper.NHibernate.Contracts.dll"

Coordinator
Nov 9, 2012 at 6:34 PM

Absolutely not! The new features presented here work in both the core and in the sub-project for NHibernate. I'm sorry if something seems confusing, I tried to build everything as simple as possible, but unfortunately have not had time to document my work here. Please tell me exactly what you're trying to do and where you're having trouble, we'll fix it.

Now, about the second issue, I have no idea, since there is no such library ("bin\Debug\CodeContracts\TelerikMvcGridCustomBindingHelper.NHibernate.Contracts.dll") in this project, try reinstalling all with Nuget.

Nov 9, 2012 at 6:53 PM

Very good.  Now I'll be looking at the source code with correct assumptions.   I'm very interested in how you have implemented Expressions. 

Pretty weird on the msbuild error, I'll get the source from NuGet instead of downloading from Source Code page and see if that helps.

Coordinator
Nov 18, 2012 at 9:52 PM

Guys, is everything working here? Did you need some help, or is there anything I can do for you?

Did you have some feedback?

 

@Noyabronok, you mentioned functions like "(x => x.Trim().ToUpper())", did you know that you can use ValueResolvers to apply this kind of manipulation in the values ​​that come from the user interface before they reach the query?

 

One more thing, after 5 RC versions it's time to launch 1.0 stable version, I'd love if you test TGH with enough effort this week, lets tie up all the loose ends and go to next step. Can you do this for me?

  • API changes can still be accepted, provided they make sense.
  • Bug reports are always welcome.
  • Pull request? you are so cool!
Coordinator
Nov 18, 2012 at 9:57 PM

@Noyabronok, I think you missed my updates here: http://tgh.codeplex.com/workitem/5 and here: http://tgh.codeplex.com/workitem/19

Nov 19, 2012 at 1:41 AM

Hi Luis.

Sorry for not getting to these items sooner.  I really appreciate all your efforts.  I'll make sure to allocate the time this week to provide feedback on all the changes you've made and ValueResolvers.

Thanks again!

Alex

Nov 20, 2012 at 6:39 PM
Edited Nov 20, 2012 at 7:37 PM

Gents. It took me a while to set up a new environment to test this. I am using Entity Framework 5 and Oracle Data Access 4.112.3.0.

 

My first test was to use the code as is. I was expecting to get 1 record and that's what I got because I made sure my case was correct. My total was wrong on the grid but that's probably a separate issue.

 

I Added .UseCaseInsensitiveSearch() to my GridCustomBindingHelper statement and ran the same test and I got an error:

 

LINQ to Entities does not recognize the method 'System.String ToLowerInvariant()' method, and this method cannot be translated into a store expression.

Nov 20, 2012 at 7:28 PM
Edited Nov 20, 2012 at 7:43 PM

More Results: If I use a custom ValueResolver as shown in the unit test, I do not get an error.  If in my database I continue to use a view which converts everything with Upper() and use ToUpperInvariant - this seems to be a good combination.

 

internal class CaseInsensitiveSearchQueryValueResolver : QueryValueResolver<string, string>

 

{

protected override string ResolveCore(string source)

{

return source.Trim().ToUpperInvariant();

}

Nov 20, 2012 at 8:16 PM

@Luis I just tested helper instance and per property value resolvers and both worked perfectly.  I will end up using the property level resolvers since that's what gives me the most control.

Coordinator
Nov 20, 2012 at 8:28 PM

Thanks to both of you guys.

@jlewis00 everything worked fine in my tests with NHibernate, it seems that EF integration is broken, I'll try alternatives to solve this...