Extension method in where clause in linq to Entities

Question

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.

Answer 1

No, you cannot educate EF to process your custom extension methods, even though you have code that builds expressions that would be usable to EF.

Either:

• use the SqlFunctions methods directly in your EF Where expression
• use an ExpressionVisitor to combine multiple expressions into a composite (OrElse / AndAlso) expression (note this won't help you have code like you want, but it will let you use your two methods and perform a || on them - it will look complex, though)

The first is simpler and clearer.

Answer 2

Answered here: https://stackoverflow.com/a/10726256/84206

His code: http://pastebin.com/4fMjaCMV

Allows you to tag an extension method as [Expandable] and so long as the expression it returns works with linq2entities, it will replace your function call with the inner expression. Note that inline lambdas give errors, so I had to declare them as local variables or static variable such as the IsCurrent variable here:

static Expression<Func<PropertyHistory, bool>> IsCurrent = (p) => p.Starts <= DateTime.Now;

[ExpandableMethod]
public static IQueryable<PropertyHistory> AsOf(this IQueryable<PropertyHistory> source)
{
  return source.Where(IsCurrent);
}