Grid Filtering on Text using "lower" in SQL

4 posts, 0 answers
  1. Juan
    Juan avatar
    3 posts
    Member since:
    Jun 2012

    Posted 18 Dec 2014 Link to this post

    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?
  2. Atanas Korchev
    Admin
    Atanas Korchev avatar
    8462 posts

    Posted 22 Dec 2014 Link to this post

    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.

     
  3. Chris
    Chris avatar
    3 posts
    Member since:
    Jul 2016

    Posted 04 Jan Link to this post

    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.
  4. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 06 Jan Link to this post

    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.
Back to Top