Wednesday, May 23, 2012

Extension method in where clause in linq to Entities

In linq to Entities we needed a method that works like "sql like". We have implemented our own extension method to IQueryable, because contains method not work for us because doesn't accept patterns like '%a%b%'



The created code is:



private const char WildcardCharacter = '%';

public static IQueryable<TSource> WhereLike<TSource>(this IQueryable<TSource> _source, Expression<Func<TSource, string>> _valueSelector, string _textSearch)
{
if (_valueSelector == null)
{
throw new ArgumentNullException("valueSelector");
}

return _source.Where(BuildLikeExpressionWithWildcards(_valueSelector, _textSearch));
}

private static Expression<Func<TSource, bool>> BuildLikeExpressionWithWildcards<TSource>(Expression<Func<TSource, string>> _valueSelector, string _textToSearch)
{
var method = GetPatIndexMethod();

var body = Expression.Call(method, Expression.Constant(WildcardCharacter + _textToSearch + WildcardCharacter), _valueSelector.Body);

var parameter = _valueSelector.Parameters.Single();
UnaryExpression expressionConvert = Expression.Convert(Expression.Constant(0), typeof(int?));
return Expression.Lambda<Func<TSource, bool>> (Expression.GreaterThan(body, expressionConvert), parameter);
}

private static MethodInfo GetPatIndexMethod()
{
var methodName = "PatIndex";

Type stringType = typeof(SqlFunctions);
return stringType.GetMethod(methodName);
}


This works correctly and the code is executed entirely in SqlServer, but now we would use this extension method inside where clause as:



myDBContext.MyObject.Where(o => o.Description.Like(patternToSearch) || o.Title.Like(patterToSerch));


The problem is that the methods used in the where clause have to return a bool result if is it used with operators like '||' , and I don't know how to make that the code I have created returns a bool and keep the code executed in sqlserver. I suppose that I have to convert the returned Expression by BuildLinqExpression method to bool, but I don't know how to do it



To sum up! First of all it's possible to create our own extensiond methods in Linq to Entities that execute the code in SqlServer? if this is possible how I have to do it?



Thanks for your help.





No comments:

Post a Comment