Hy, in my code i have a method that builds a 'where expression'. It looks like this :
private static Expression<Func<T, bool>> BuildWhereExpression<T>(Context context, string memberName, string memberValue)
{
memberValue = memberValue.Trim().RemoveDiacritics().ToLower();
var param = Expression.Parameter(typeof(T), "x");
var propsNames = memberName.Split('.');
Expression propExpression = param;
foreach (var propName in propsNames)
{
propExpression = Expression.Property(propExpression, propName);
}
var methodToLower = typeof(string).GetMethods().First(x => x.Name == "ToLower" && x.GetParameters().Length == 0);
var toLowerCall = Expression.Call(propExpression, methodToLower);
var methodDiacritics = typeof(context).GetMethod("GetStringWithoutDiacritics");
var funcCall = Expression.Call(Expression.Constant(context), methodDiacritics, toLowerCall);
var methodContains = typeof(string).GetMethods().First(x => x.Name == "Contains" && x.GetParameters().Length == 1);
var containsCall = Expression.Call(funcCall, methodContains, Expression.Constant(memberValue));
var lambda = Expression.Lambda<Func<T, bool>>(containsCall, param);
return lambda;
}
where "GetStringWithouDiacritics" is a scalar function in my database (code first). This produces the folowing, when used with 'Where' :
WHERE ([dbo].[GetStringWithoutDiacritics](LOWER([Extent2].[Col1])) LIKE N'%blabla%')
This is ok but because that i am using a scalar function in where the performance is terrible. What i want to do is to write the sql code in the function 'inline'. It should produce something like :
WHERE CONVERT(VARCHAR(MAX),REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(Col1) COLLATE Latin1_General_BIN, N'ș','s') COLLATE Latin1_General_BIN, N'ț','t') COLLATE Latin1_General_BIN, N'î', 'i') COLLATE Latin1_General_BIN, N'ă', 'a') COLLATE Latin1_General_BIN, N'â', 'a'))
LIKE '%parite%'
My question is how cand i modify the method that builds the where expression. I know it is with dynamic expression but i can't figure out how.. , to replace 'Col1' with my actual column, etc..
