DataSourceLoadOptionsBase - provide option to skip StringToLower for specific fields #492

Activity

1. AlekseyMartynov

   added

   question

   on Sep 28, 2021
2. AlekseyMartynov commented on Sep 28, 2021

   

   AlekseyMartynov

   on Sep 28, 2021

   Contributor

   More actions

   Hello

   You can use CustomFilterCompilers to intercept and modify filter expressions.

   Example:

   static void Main(string[] args) {
       CustomFilterCompilers.RegisterBinaryExpressionCompiler(SkipStringToLowerCompiler);
   
       using(var context = new NorthwindContext()) {
           var loadResult = DataSourceLoader.Load(context.Orders, new DataSourceLoadOptionsBase {
               RequireTotalCount = true,
               StringToLower = true, // default setting
               Filter = new object[] {
                   new object[] { "ShipName", "=", "Wilman Kala" },
                   new object[] { "ShipAddress", "contains", "Keskuskatu 45" }
               }
           });
       }
   
   }
   
   static Expression SkipStringToLowerCompiler(IBinaryExpressionInfo info) {
       if(info.DataItemExpression.Type == typeof(Orders) && (info.AccessorText == "ShipAddress" || info.AccessorText == "ShipName")) {
           var left = Expression.PropertyOrField(info.DataItemExpression, info.AccessorText);
           var right = Expression.Constant(Convert.ToString(info.Value));
           switch(info.Operation) {
               case "=":
                   return Expression.Equal(left, right);
               case "contains":
                   var method = typeof(String).GetMethod("Contains", new[] { typeof(String) });
                   return Expression.Call(left, method, right);
           }
       }
       return null;
   }

   Resulting SQL:

   SELECT COUNT(*)
   FROM [Orders] AS [o]
   WHERE ([o].[ShipName] = N'Wilman Kala') AND (CHARINDEX(N'Keskuskatu 45', [o].[ShipAddress]) > 0)

   Compare with SQL w/o SkipStringToLowerCompiler:

   SELECT COUNT(*)
   FROM [Orders] AS [o]
   WHERE (LOWER([o].[ShipName]) = N'wilman kala') AND (CHARINDEX(N'keskuskatu 45', LOWER([o].[ShipAddress])) > 0)

   However, as you probably know, in SQL databases, case sensitivity is often controlled by table/column collation settings. If this is your case then you don't need to use StringToLower.
3. AlekseyMartynov

   added

   enhancement

   has-workaround

   and removed

   question

   on Sep 28, 2021
4. drekoo commented on Sep 28, 2021

   

   drekoo

   on Sep 28, 2021

   Author

   More actions

   Ok, thank you very much for workaround.