Add support for "Like" operations

[JDMilne]

Search using "co" (contains) is often inefficient. Adding support for Like comparisons would be a great enhancement. Some "user instruction" for those unfamiliar with SQL Like operations is advised.

I've successfully tested one possible implementation that requires adding a dependancy for Microsoft.EntityFrameworkCore 3.19. The approach I used was to piggy-back on the "contains" path and use Like when a wildcard is detected in the search term. It is of note that I've only tested this with string values. I remember reading somewhere that casting to string is not automatic when performing like operations with numeric value.

The following code is added to your StringSearchExpressionProvider class.

// Like Method Info declaration
private static readonly MethodInfo _likeMethod = typeof(DbFunctionsExtensions)
.GetMethod("Like", new[] { typeof(DbFunctions), typeof(string), typeof(string) });

// Supported wildcard characters
private static char[] likeOperators = { '%', '_', '[' };

The switch statement in GetComparison method is updated as follows. Note that I also use Like for the "StartsWith" operations.

        switch (op.ToLower())
        {
            // JAMX case StartsWithOperator: return Expression.Call(left, StartsWithMethod, right, IgnoreCase);
            case StartsWithOperator:                    
                if (term.IndexOfAny(likeOperators) != -1)
                {
                    return Expression.Call(null, _likeMethod, Expression.Constant(EF.Functions), left, right);
                }                    
                return Expression.Call(left, StartsWithMethod, right);
            case ContainsOperator:                    
                if (term.IndexOfAny(likeOperators) != -1)
                {
                    return Expression.Call(null, _likeMethod, Expression.Constant(EF.Functions), left, right);
                }
                return Expression.Call(left.TrimToLower(), _stringContainsMethod, right.TrimToLower());
            case EqualsOperator: return Expression.Equal(left.TrimToLower(), right.TrimToLower());
            default: return base.GetComparison(left, op, right);
        }

A limitation of this approach is that it only supports the single parameter Like operation provided by DbFunctionsExtensions. The drawback is apparent if you are searching for a string containing a wildcard character. The two parameter operation allows you to specify an escape character (e.g. Linq example: query = query.Where(o => EF.Functions.Like(o.OrderNumber, OrderNumber, "\\")); ). Easily compensated for by adjusting the user documentation to define an acceptable escape character for your implementation and adjusting the MethodInfo declaration.

One more thing. I'm not sure if it is my environment, but the "sw" - StartsWith feature does not work when using the two parameter variant with the string comparison method parameter. I redefined the MethodInfo declaration as follows to fix it:

private static readonly MethodInfo StartsWithMethod = typeof(string)
.GetMethods()
.First(x => x.Name == "StartsWith" && x.GetParameters().Length == 1);
// .First(x => x.Name == "StartsWith" && x.GetParameters().Length == 2);