This is a migrated thread and some comments may be shown as answers.

Grid Filtering on Text using "lower" in SQL

7 Answers 125 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Juan
Top achievements
Rank 1
Juan asked on 18 Dec 2014, 01:27 PM
When I filter a text column in the MVC grid, the sql that gets sent to SQL server is using the "lower" function to perform case-insensitive search. However, SQL Server already does case insensitive comparisons and the call to lower prevents from the index on that column to be used.

Example of SQL being sent to SQL Server:

SELECT [Extent1].[ID] AS [ID], [Extent1].[Capture Date] AS [Capture Date], [Extent1].[Item Sequence Number] AS [Item Sequence Number], 
        FROM [dbo].[Images] AS [Extent1]
        WHERE ((LOWER([Extent1].[Account Number])) = (LOWER('112233'))) OR ((LOWER([Extent1].[Account Number]) IS NULL) AND (LOWER('112233') IS NULL))

I am using hte latest version of Telerik UI for MVC and EF 6. The datasource code in the controller is:

public ActionResult Images_Read([DataSourceRequest]DataSourceRequest request)
{
    return this.Json(this._repository.Images.ToDataSourceResult(request));
}

Can I prevent Telerik's grid from using t-sql's lower function?

7 Answers, 1 is accepted

Sort by
0
Atanas Korchev
Telerik team
answered on 22 Dec 2014, 09:05 AM
Hi Juan,

Currently there is no built-in way to disable this behavior. This is done in order to support case insensitive filtering in all possible LINQ providers. A possible workaround is to use custom binding and implement the filtering in your own code. The UI for ASP.NET MVC sample application includes a runnable demo which you can check.

Regards,
Atanas Korchev
Telerik
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
0
Chris
Top achievements
Rank 1
answered on 04 Jan 2017, 01:41 PM
Is there a way to disable this behavior when using ExpressionBuilder without using custom binding? The unnecessary lower() call causes a MAJOR performance decrease with large data sets.
0
Kostadin
Telerik team
answered on 06 Jan 2017, 11:14 AM
Hello Chris,

In order to make it case-sensitive you can use filterable ignoreCase option:
http://docs.telerik.com/kendo-ui/api/aspnet-mvc/Kendo.Mvc.UI.Fluent/GridBoundColumnFilterableBuilder#methods-IgnoreCase(System.Boolean)

Regards,
Kostadin
Telerik by Progress
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Alfaproject
Top achievements
Rank 1
answered on 21 Feb 2018, 01:22 PM

Hi,

with Kendo Grid version "2017.1.223" I have set IgnoreCase eg.: columns.Bound(c => c.CodResource).Title("CodResource").Filterable(filterable => filterable.IgnoreCase(true)).Width(180) but the sql that is sent to SQL server still use the "lower" function: 

SQL Statement is:

select ..... 
from v_ResourceIndex 
where lower(v_resource0_.CodResource)  like ('bb'+'%')

 

Any advice about avoid use of lower(..)  function in WHERE Clause which causes performance issue?

 

Regards,

Pietro.

 

 

0
Stefan
Telerik team
answered on 23 Feb 2018, 07:20 AM
Hello, Pietro,

If this is the SQL query generated by the Kendo UI methods, it is the built-in behavior as this is the part of the query which is making a case-insensitive search.

As my colleague, Atanas said in the first post, the option to modify this is to actually make custom filtering logic and the build the query programmatically without the built-in methods.

Apologies for the inconvenience this may have caused you.

Regards,
Stefan
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Alfaproject
Top achievements
Rank 1
answered on 23 Feb 2018, 02:37 PM

Hi Stefan,

could you give me an example about filtering (not only about sorting or paging like here:  https://docs.telerik.com/aspnet-mvc/helpers/grid/binding/custom-binding#custom-ajax-binding) 

Thanks in advance.

Regards,

Pietro.

0
Tsvetina
Telerik team
answered on 27 Feb 2018, 12:22 PM
Hello Pietro,

You probably have already seen this from my reply in your ticket but in case anyone else is looking for the same information, this demo shows an implementation that applies data operations manually, including filtering.

Regards,
Tsvetina
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Brad
Top achievements
Rank 1
commented on 26 Aug 2021, 03:30 PM

The demo linked by Tsvetina does not demonstrate custom filtering.

This line of code towards the bottom

data = data.Where(ExpressionBuilder.Expression<OrderViewModel>(filterDescriptors, false));

applies all of the filterDescriptors with a LOWER function in the database query.

Can this be customized to avoid the LOWER operation?

Eyup
Telerik team
commented on 30 Aug 2021, 10:00 AM

Yeah, this specific code does not change the filter expressions, however, the point here is that you have all the access here:
        public static IQueryable<OrderViewModel> ApplyOrdersFiltering(this IQueryable<OrderViewModel> data,
           IList<IFilterDescriptor> filterDescriptors)
        {
            if (filterDescriptors != null && filterDescriptors.Any())
            {
                data = data.Where(ExpressionBuilder.Expression<OrderViewModel>(filterDescriptors, false));
            }
            return data;
        }
You can traverse the filterDescriptors collection one by one and build up your custom Where clause to implement LINQ filtering.
Tags
Grid
Asked by
Juan
Top achievements
Rank 1
Answers by
Atanas Korchev
Telerik team
Chris
Top achievements
Rank 1
Kostadin
Telerik team
Alfaproject
Top achievements
Rank 1
Stefan
Telerik team
Tsvetina
Telerik team
Share this question
or