EF Dynamic Query - Like

Question

After successfully implementing a like function with use of string function 'contains' I noticed that wildcards are escaped when translated to sql.

Eg. When I search for a street starting with p and has number 13 in it. I replace all space with '%' but EF escapes it. The output query then looks like this:

SELECT " FROM Customer WHERE (LOWER([Street]) LIKE N'%p~%13%' ESCAPE N'~')

In one of the blogs a suggestion was to use patindex instead. I just don't know how to implement this.

My current code looks like this:

public static Expression<Func<T, bool>> Create<T>(string propertyName, ComparisonOperators comparisonOperator, dynamic comparedValue1, dynamic comparedValue2 = null)
{
    ParameterExpression parameterExpression = Expression.Parameter(typeof(T), "x");
    MemberExpression memberExpression = Expression.MakeMemberAccess(parameterExpression, typeof(T).GetProperty(propertyName));
    ConstantExpression constantExpression = Expression.Constant(comparedValue1, comparedValue1.GetType());
    Expression expressionBody = null;

    switch (comparisonOperator)
    {
        ...

        case ComparisonOperators.Contains:
            expressionBody = Expression.Call(GetLowerCasePropertyAccess(memberExpression), ContainsMethod, Expression.Constant(comparedValue1.ToLower()));
            break;
    }

    return Expression.Lambda<Func<T, bool>>(expressionBody, new ParameterExpression[] { parameterExpression });
}

private static MethodCallExpression GetLowerCasePropertyAccess(MemberExpression propertyAccess)
{
    var stringExpression = GetConvertToStringExpression(propertyAccess);
    if (stringExpression == null)
        throw new Exception(string.Format("Not supported property type {0}", propertyAccess.Type));

    return Expression.Call(stringExpression, typeof(string).GetMethod("ToLower", Type.EmptyTypes));
}

private static Expression GetConvertToStringExpression(Expression e)
{
    // if property string - no cast needed
    // else - use SqlFunction.StringConvert(double?) or SqlFunction.StringConvert(decimal?);
    Expression strExpression = null;
    if (e.Type == typeof(string)) strExpression = e;
    else
    {
        var systemType = Nullable.GetUnderlyingType(e.Type) ?? e.Type;
        if (systemType == typeof(int) || systemType == typeof(long) || systemType == typeof(double) || systemType == typeof(short) || systemType == typeof(byte))
        {
            // cast int to double
            var doubleExpr = Expression.Convert(e, typeof(double?));
            strExpression = Expression.Call(StringConvertMethodDouble, doubleExpr);
        }
        else if (systemType == typeof(decimal))
        {
            // call decimal version of StringConvert method
            // cast to nullable decimal
            var decimalExpr = Expression.Convert(e, typeof(decimal?));
            strExpression = Expression.Call(StringConvertMethodDecimal, decimalExpr);
        }
    }
    return strExpression;
}

private static readonly MethodInfo ContainsMethod = typeof(String).GetMethod("Contains", new Type[] { typeof(String) });
private static readonly MethodInfo StringConvertMethodDouble = typeof(SqlFunctions).GetMethod("StringConvert", new Type[] { typeof(double?) });
private static readonly MethodInfo StringConvertMethodDecimal = typeof(SqlFunctions).GetMethod("StringConvert", new Type[] { typeof(decimal?) });

Answer 1

Like this:

ctx.Products.Where(x => SqlFunctions.PatIndex("%Something%", x.Name) > 1).ToList();

But this will be identical to:

ctx.Products.Where(x => x.Name.Contains("Something")).ToList();