Add case sensitive filtering for the MVC Grid wrapper
The Grid should allow switching between case sensitive and case insensitive filtering.
Posted on:31 Aug 2021 17:41
I ended up writing custom filters for the DataSourceRequest that apply LINQ where clauses to the IQueryable. These are applied to the IQueryable prior to ToDataSourceResult(). Basically I do all the filtering and leave the pagination to ToDataSourceResult().
My filters do not cover all the filter options that DataSourceRequest has available, but as my use case is limited that approach worked for me.
Since this is still a workaround I would still appreciate this to be patched by Telerik.
Posted on:27 Aug 2021 13:42
Were you able to implement a workaround? I looked for an example of the method you referred to, but could not find anything.
I would also appreciate a fix, but I also really need a workaround if it exists.
Posted on:01 Aug 2021 12:33
It appears that "IgnoreCase" is only implemented for client-side filtering. Server-side filtering uses ToDataSourceResult() and the case filtering flag is not passed through in DataSourceRequest. As stated in other comments this has a severe impact on query performance as the database will not use indexes if comparisons are wrapped in LOWER() functions in the generated SQL.
Especially for databases that are case insensitive, this is completely unnecessary.
A possible approach would be to add a global flag to ToDataSourceResult() to indicate that the database is case insensitive and the TOLOWER() wrapper should not be generated.
I am aware of the workaround to implement filtering server-side manually, but that takes away from the simplicity a framework should deliver.
Given that this issue has been out there for years and reported multiple times a fix would be very much appreciated.
This has been brought up in ticket 1339310 and in feedback request: https://feedback.telerik.com/kendo-jquery-ui/1360465-todatasourceresult-avoid-casting-to-lower-to-enable-index-use-in-database
Please reconsider this item - to provide an opt-out for the case-insensitive lower expression so that indexes can be leveraged on SQL string fields.
Posted on:02 Nov 2019 08:25
I Agree, huge performance decrease on large SQL tables! Please fix asap
Posted on:15 Feb 2019 08:52
I am agreeing with the rest of the comments, there is a lack of regard to the optimisation of the query being performed against the database, database developers and API developers go to great lengths to ensure case sensitive on models, databases and columns and then the Kendo library decides to lower everything causing our indexes to be worthless.
Posted on:24 Mar 2018 03:00
This has a huge performance impact on large tables as the SQL ToLower prevents the database from using the index.
Posted on:09 Jan 2017 17:04
This really should be implemented. Adding the ToLower() calls to databases that are already case insensitive is a huge performance decrease.