DataSourceLoadOptionsBase - provide option to skip StringToLower for specific fields

[drekoo]

Hello, is possible to implement new DataSourceLoadOptionsBase.SkipStringToLower option. When I have some filter, I need case insensitive comparison just for some fields, but not for others (because of index search).

Best regards, Darko

[AlekseyMartynov]

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.

[drekoo]

Ok, thank you very much for workaround.