CODEWITHSUNDEEP
c#linqentity-framework
workabyte
workabyte

Reputation: 3765

using Extension Method or Func in EF lambda expression

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!

Upvotes: 3

Views: 2210

Answers (2)

Servy
Servy

Reputation: 203829

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.

Upvotes: 8

Scott Chamberlain
Scott Chamberlain

Reputation: 127563

You can only use a small subset of functions on types inside the expression that Entity Framework already knows how to handle. Traditionally these are methods like Contains and similar (see this page for a fuller list). Your own extension methods will not be recognized by the parser if they are inside the expression.

However one thing you could do is make a extension method for IQueryable<T> that takes in your range as parameters.

public static IQueryable<T> Between(this IQueryable<T> query, Expression<Func<T, DateTime> selector, DateTime start, DateTime end)
{
    //...
}

Now what goes inside the //... would take me a hour or so to figure it all out, and is a little too much to write up for a quick answer on the internet without being paid. But if you learn how to parse and write your own expressions (which is a good skill to have) you may be able to figure out how to do it on your own.

EDIT: Or Servy can figure it out and post it while I am typing up my answer :)

Upvotes: 1

Related Questions