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?) });
I need to replace the method call to use patindex instead:
expressionBody = Expression.Call(GetLowerCasePropertyAccess(memberExpression), ContainsMethod, Expression.Constant(comparedValue1.ToLower()));
