using Extension Method or Func in EF lambda expression

Question

I have an extension method that is fairly simple. When trying to use it in an Entity framework I get this

LINQ to Entities does not recognize the method 'Boolean Between[DateTime](System.DateTime, System.DateTime, System.DateTime, Boolean)' 

Many others are getting the same issue and I understand the error. There is always a way to make it work.

I have been digging around trying to figure out how i can re-implement this method and have it be linq to EF friendly.

for this specific method its just checking if one IComparable is between two others. so really it would just expand to

.Where(x=> x.Date >= startDate && x.Date <= endDate)

and all i am really trying to do is make it easier on the eyes and express it like

.Where(x=> x.Date.Between(startDate, endDate))

Being that I am very new to the Func and such Im sure there is a way to approach extension methods (even if written specifically for EF) so that they will be friendly with EF linq

I have done some digging around SO and other sites and come across some interesting answers but not able to get to past the finish line with them.

Thanks in advance!

Answer 1

The query provider is going to be tasked with taking the information provided in the expression that you give it and translating that into SQL. If you take the code that you have and compile it into a C# method then the query provider has no way of inspecting it to see what the original source code was and using that to create corresponding SQL code. You need to use some means of creating Expression objects that it can understand, the easiest means of doing this is generally through lambdas.

What we can do here is create a new extension method for our queries that will accept a query, an Expression that represents the date in question, along with the constant date values that they should be between. Using this we can construct our own expression that represents what the expression would have looked like had you manually typed out the comparisons in the lambda itself:

public static IQueryable<T> WhereBetweenDates<T>(
    this IQueryable<T> query,
    Expression<Func<T, DateTime>> selector,
    DateTime startDate,
    DateTime endDate)
{
    var predicate = selector.Compose(date => date >= startDate && date <= endDate);
    return query.Where(predicate);
}

Here we're using a Compose method. This method accepts one expression that maps a value to another, along with a second expression that maps that value to something else, and it creates a new expression that represents mapping the original value from the first expression to the result of the second. It can do this by replacing all uses of the parameter in the second expression with the body of the first expression:

public static Expression<Func<TFirstParam, TResult>>
    Compose<TFirstParam, TIntermediate, TResult>(
    this Expression<Func<TFirstParam, TIntermediate>> first,
    Expression<Func<TIntermediate, TResult>> second)
{
    var param = Expression.Parameter(typeof(TFirstParam), "param");

    var newFirst = first.Body.Replace(first.Parameters[0], param);
    var newSecond = second.Body.Replace(second.Parameters[0], newFirst);

    return Expression.Lambda<Func<TFirstParam, TResult>>(newSecond, param);
}

Here we're using a method to replace all instances of one expression with another. This can be done using the following helper method:

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

---

Now I know that this does seem like a lot of code, but the idea here is that you're not using all of this just to write this one method. Everything after that first method is a fundamental building block that you can use to manipulate expressions in reasonably straightforward (and in particular, statically typed) ways. The Compose method can be re-used in all sorts of other contexts to apply a frequently used operation on a constantly changing sub-expression.