Extension Method convertion to LINQ Expressions and Common Methods

Question

I have an Extension method that does the following:

public static bool Between(this DateTime target, DateTime startDate, DateTime endDate)
{
    return target >= startDate && target <= endDate;
}

and I can call it like this

if (expectedDate.Between(minDate, maxDate)) { do code }

I'm now trying to use this in a Linq/Lambda expression like

    return list.Where(item => targetDate.Between(item.StartDate, item.EndDate));
OR  if (!list.Any(pp => targetDate.Between(pp.StartDate, pp.EndDate)))

and I get the following error in runtime:

LINQ to Entities does not recognize the method 'Boolean Between(System.DateTime, System.DateTime, System.DateTime)' method, and this method cannot be translated into a store expression.

But this is fine

if (!list.Any(item => targetDate >= item.StartDate && quoteDate.EventDate <=item.EndDate)))

I would like to have a common method to call. What are my options?

score 4 · Accepted Answer · answered Nov 18 '11 at 10:20

Simple modification of Brian's solution that doesn't require AsExpandable():

public static IQueryable<TSource> Between<TSource, TKey>(
    this IQueryable<TSource> source, TKey key,
    Expression<Func<TSource, TKey>> lowSelector,
    Expression<Func<TSource, TKey>> highSelector)
    where TKey : IComparable<TKey>
{
    Expression low = lowSelector.Body;
    Expression high = highSelector.Body;

    Expression lowerBound = Expression.LessThanOrEqual(
        low, Expression.Constant(key));
    Expression upperBound = Expression.LessThanOrEqual(
        Expression.Constant(key), high);

    var lowLambda = Expression.Lambda<Func<TSource, bool>>(
        lowerBound, lowSelector.Parameters);
    var highLambda = Expression.Lambda<Func<TSource, bool>>(
        upperBound, highSelector.Parameters);

    return source.Where(lowLambda).Where(highLambda);
}

Check the translated SQL and see if it really translated to BETWEEN. — the_drow, Nov 20 '11 at 06:23

the_drow · Answer 2 · 2011-11-17T00:14:54.377

3

Since you are not using LINQ to Object the query isn't being executed but translated to an expression tree to be translated to SQL.
LINQ to Entities does not know how to translate a method you wrote to SQL since you implemented it.
I never used LINQ to Entities but there must be a way to extend the expression tree builder to enable LINQ to Entities to translate your method to SQL, LINQ to NHibernate has a way to do so.
Here is an example for how to extend the LINQ to Entities provider.

edited Nov 17 '11 at 00:14

answered Nov 16 '11 at 22:43

the_drow

18,571
25
126
193

Is it possible to do something like this: list.Where(item => targetDate.Between(item.StartDate, item.EndDate)) ? Do I need to have it return an expression, Func, Predicate? – Brian Nov 16 '11 at 23:20
@Brian: You need to extend the Abstract Syntax Tree parser of LINQ to Entities. It might be possible, but some microsoft products are too carelessly designed. You should ask someone who actually used LINQ to Entities. [This](http://fluffyidentity.blogspot.com/2008/09/extending-linq-to-entities.html) is the first example I found on google, however considering the fact that you don't actually know how LINQ works behind the scenes I doubt you will succeed without understanding what an Abstract Syntax Tree is and how it is implemented in the .NET Framework. – the_drow Nov 17 '11 at 00:08
This is close, http://stackoverflow.com/questions/1447635/linq-between-operator . I’m trying to get a list of where the target date is known, and the start and end are properties of the list item – Brian Nov 17 '11 at 16:46
@Brian: This might not translate to an SQL BETWEEN statement though. – the_drow Nov 17 '11 at 18:04

score 1 · Answer 3 · edited May 23 '17 at 12:07

I got it working using a couple different methods. Building off of LINQ Between Operator I created a new method

public static IQueryable<TSource> Between<TSource, TKey>(this IQueryable<TSource> source, TKey key, Expression<Func<TSource, TKey>> lowSelector, Expression<Func<TSource, TKey>> highSelector)
    where TKey : IComparable<TKey>
{
    Expression low = Expression.Invoke(lowSelector, lowSelector.Parameters.ToArray());
    Expression high = Expression.Invoke(highSelector, highSelector.Parameters.ToArray());

    Expression lowerBound = Expression.LessThanOrEqual(low, Expression.Constant(key));
    Expression upperBound = Expression.LessThanOrEqual(Expression.Constant(key),  high);

    Expression<Func<TSource, bool>> lambda = Expression.Lambda<Func<TSource, bool>>(lowerBound, lowSelector.Parameters);
    Expression<Func<TSource, bool>> lambda2 = Expression.Lambda<Func<TSource, bool>>(upperBound, highSelector.Parameters);
    return source.AsExpandable().Where(lambda).Where(lambda2);
}

This didn't work unless I used AsExpandable or ToList().

AsExpandable() is from LinkKit

ToList() forces it from Linq to entities into Linq To Objects, but executes the SQL.

Thanks for your help and suggestions

Extension Method convertion to LINQ Expressions and Common Methods

3 Answers3