Can you teach Entity Framework to recognize an exp

Question

I have a search function that uses the Entity Framework. One of the things you can search by is a date range. You might say something like "where Start Date is between SearchStart and Search End". It isn't that difficult to write in linq syntax, but it can get pretty verbose when you have many different date parameters to search by.

I have an extension method on DateTime that basically checks of the date is contained between a StartDate and an EndDate. I use this in other places where EF isn't an issue, but I would also like to use it with EF queries. I am creating the query dynamically by applying additional WHERE clauses before doing a ToList (which will try to run the query).

As I had expected, using the extension method throws an exception: "LINQ to Entities does not recognize the method 'Boolean IsBetween(System.DateTime, System.DateTime, System.DateTime)' method, and this method cannot be translated into a store expression."

I understand that Linq to Entities has no way of knowing what IsBetween translates to in Sql, but is there a way for me to give it instructions? I tried searching online for the answer, but it wasn't very helpful. If there some attribute I can add to the extension method or some way I can update the EF configuration?

I am guessing not, but I don't want to assume without asking.

Thanks!

UPDATE: Adding extension method code

 public static bool IsBetween(this DateTime date , DateTime start, DateTime end)
 {
   return (date >= start && date < end);
 }

Answer 1

You can use DbFunctions class to generate BETWEEN sql statement

http://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions(v=vs.113).aspx

It's called EntityFunctions in versions of EF earlier than 6.0.

Answer 2

Here is a totally generic approach, similar to what danludig's answer is doing but diving more deep in and hand building Expression trees to make it work.

We are not teaching Entity Framework how to read a new expression, instead we are breaking the expression in to its component parts to something Entity Framework already knows how to read.

public static IQueryable<T> IsBetween<T>(this IQueryable<T> query, Expression<Func<T, DateTime>> selector, DateTime start, DateTime end)
{
    //Record the start time and end time and turn them in to constants to be passed in to the query.
    //There may be a better way to pass them as parameters instead of constants but I don't have the skill with expression trees to know how to do it.
    var startTime = Expression.Constant(start);
    var endTime = Expression.Constant(end);

    //We get the body of the expression that was passed in that selects the DateTime column in the row for us.
    var selectorBody = selector.Body;

    //We need to pass along the parametres from that original selector.
    var selectorParameters = selector.Parameters;

    // Represents the "date >= start"
    var startCompare = Expression.GreaterThanOrEqual(selectorBody, startTime);

    // Represents the "date < end"
    var endCompare = Expression.LessThan(selectorBody, endTime);

    // Represents the "&&" between the two statements.
    var combinedExpression = Expression.AndAlso(startCompare, endCompare);

    //Reform the new expression in to a lambada to be passed along to the Where clause.
    var lambada = Expression.Lambda<Func<T, bool>>(combinedExpression, selectorParameters);

    //Perform the filtering and return the filtered query.
    return query.Where(lambada);
}

It generates the following SQL

SELECT 
    [Extent1].[TestId] AS [TestId], 
    [Extent1].[Example] AS [Example]
    FROM [dbo].[Tests] AS [Extent1]
    WHERE ([Extent1].[Example] >= convert(datetime2, '2013-01-01 00:00:00.0000000', 121)) AND ([Extent1].[Example] < convert(datetime2, '2014-01-01 00:00:00.0000000', 121))

Using this below program.

private static void Main(string[] args)
{
    using (var context = new TestContext())
    {
        context.SaveChanges();

        context.Tests.Add(new Test(new DateTime(2013, 6, 1)));
        context.Tests.Add(new Test(new DateTime(2014, 6, 1)));
        context.SaveChanges();

        DateTime start = new DateTime(2013, 1, 1);
        DateTime end = new DateTime(2014, 1, 1);

        var query = context.Tests.IsBetween(row => row.Example, start, end);

        var traceString = query.ToString();

        var result = query.ToList();

        Debugger.Break();
    }
}

public class Test
{
    public Test()
    {
        Example = DateTime.Now;
    }

    public Test(DateTime time)
    {
        Example = time;
    }

    public int TestId { get; set; }
    public DateTime Example { get; set; }
}

public class TestContext : DbContext
{
    public DbSet<Test> Tests { get; set; } 
}

---

Here is a utility that can convert a generic expression and map it to your specific object. This allows you to write your expression as date => date >= start && date < end and just pass it in to the converter to map the necessary columns. You will need to pass in one mapping per parameter in your original lambada.

public static class LambadaConverter
{
    /// <summary>
    /// Converts a many parametered expression in to a single paramter expression using a set of mappers to go from the source type to mapped source.
    /// </summary>
    /// <typeparam name="TNewSourceType">The datatype for the new soruce type</typeparam>
    /// <typeparam name="TResult">The return type of the old lambada return type.</typeparam>
    /// <param name="query">The query to convert.</param>
    /// <param name="parameterMapping">The mappers to go from the single source class to a set of </param>
    /// <returns></returns>
    public static Expression<Func<TNewSourceType, TResult>>  Convert<TNewSourceType, TResult>(Expression query, params Expression[] parameterMapping)
    {
        //Doing some pre-condition checking to make sure everything was passed in correctly.
        var castQuery = query as LambdaExpression;

        if (castQuery == null)
            throw new ArgumentException("The passed in query must be a lambada expression", "query");

        if (parameterMapping.Any(expression => expression is LambdaExpression == false) ||
            parameterMapping.Any(expression => ((LambdaExpression)expression).Parameters.Count != 1) ||
            parameterMapping.Any(expression => ((LambdaExpression)expression).Parameters[0].Type != typeof(TNewSourceType)))
        {
            throw new ArgumentException("Each pramater mapper must be in the form of \"Expression<Func<TNewSourceType,TResut>>\"",
                                        "parameterMapping");
        }

        //We need to remap all the input mappings so they all share a single paramter variable.
        var inputParameter = Expression.Parameter(typeof(TNewSourceType));

        //Perform the mapping-remapping.
        var normlizer = new ParameterNormalizerVisitor(inputParameter);
        var mapping = normlizer.Visit(new ReadOnlyCollection<Expression>(parameterMapping));

        //Perform the mapping on the expression query.
        var customVisitor = new LambadaVisitor<TNewSourceType, TResult>(mapping, inputParameter);
        return (Expression<Func<TNewSourceType, TResult>>)customVisitor.Visit(query);

    }

    /// <summary>
    /// Causes the entire series of input lambadas to all share the same 
    /// </summary>
    private class ParameterNormalizerVisitor : ExpressionVisitor
    {
        public ParameterNormalizerVisitor(ParameterExpression parameter)
        {
            _parameter = parameter;
        }

        private readonly ParameterExpression _parameter;

        protected override Expression VisitParameter(ParameterExpression node)
        {
            if(node.Type == _parameter.Type)
                return _parameter;
            else
                throw new InvalidOperationException("Was passed a parameter type that was not expected.");
        }
    }

    /// <summary>
    /// Rewrites the output query to use the new remapped inputs.
    /// </summary>
    private class LambadaVisitor<TSource,TResult> : ExpressionVisitor
    {
        public LambadaVisitor(ReadOnlyCollection<Expression> parameterMapping, ParameterExpression newParameter)
        {
            _parameterMapping = parameterMapping;
            _newParameter = newParameter;
        }

        private readonly ReadOnlyCollection<Expression> _parameterMapping;
        private readonly ParameterExpression _newParameter;

        private ReadOnlyCollection<ParameterExpression> _oldParameteres = null;

        protected override Expression VisitParameter(ParameterExpression node)
        {
            //Check to see if this is one of our known parameters, and replace the body if it is.
            var index = _oldParameteres.IndexOf(node);
            if (index >= 0)
            {
                return ((LambdaExpression)_parameterMapping[index]).Body;
            }

            //Not one of our known parameters, process as normal.
            return base.VisitParameter(node);
        }

        protected override Expression VisitLambda<T>(Expression<T> node)
        {
            if (_oldParameteres == null)
            {
                _oldParameteres = node.Parameters;

                var newBody = this.Visit(node.Body);

                return Expression.Lambda<Func<TSource, TResult>>(newBody, _newParameter);
            }
            else
                throw new InvalidOperationException("Encountered more than one Lambada, not sure how to handle this.");
        }
    }
}

Here is a simple test program I used to test it out, it generates well formed queries with parameters passed in where they should be.

    private static void Main(string[] args)
    {
        using (var context = new TestContext())
        {
            DateTime start = new DateTime(2013, 1, 1);
            DateTime end = new DateTime(2014, 1, 1);

            var query = context.Tests.IsBetween(row => row.Example, start, end);
            var traceString = query.ToString(); // Generates the where clause: WHERE ([Extent1].[Example] >= @p__linq__0) AND ([Extent1].[Example] < @p__linq__1)

            var query2 = context.Tests.ComplexTest(row => row.Param1, row => row.Param2);
            var traceString2 = query2.ToString(); //Generates the where clause: WHERE (N'Foo' = [Extent1].[Param1]) AND ([Extent1].[Param1] IS NOT NULL) AND (2 = [Extent1].[Param2])

            Debugger.Break();
        }
    }

    public class Test
    {
        public int TestId { get; set; }
        public DateTime Example { get; set; }
        public string Param1 { get; set; }
        public int Param2 { get; set; }
    }

    public class TestContext : DbContext
    {
        public DbSet<Test> Tests { get; set; } 
    }

    public static IQueryable<T> IsBetween<T>(this IQueryable<T> query, Expression<Func<T, DateTime>> dateSelector, DateTime start, DateTime end)
    {
        Expression<Func<DateTime, bool>> testQuery = date => date >= start && date < end;

        var newQuery = LambadaConverter.Convert<T, bool>(testQuery, dateSelector);

        return query.Where(newQuery);
    }

    public static IQueryable<T> ComplexTest<T>(this IQueryable<T> query, Expression<Func<T, string>> selector1, Expression<Func<T, int>> selector2)
    {
        Expression<Func<string, int, bool>> testQuery = (str, num) => str == "Foo" && num == 2;

        var newQuery = LambadaConverter.Convert<T, bool>(testQuery, selector1, selector2);

        return query.Where(newQuery);
    }

You can see this also fixes the "constant string" problem I was having in the first example, the DateTimes are now passed in as parameters.

Answer 3

Usage:

var start = new DateTime(2014, 01, 21);
var end = new DateTime(2014, 01, 22);
var queryable = dbContext.Set<MyEntity>();
queryable = queryable.InBetween(start, end);

Extension method

public static class EntityExtensions
{
    public static IQueryable<MyEntity> InBetween(this IQueryable<MyEntity> queryable,
        DateTime start, DateTime end)
    {
        return queryable.Where(x => x.DateColumn >= start && x.DateColumn < end);
    }
}

Now you don't get full reusage of your other extension method here. However, you can reuse this new extension method in several other queries. Just invoke .InBetween on your queryable and pass the args.

Here is another way to do it:

public static IQueryable<MyEntity> InBetween(this IQueryable<MyEntity> queryable,
    DateTime start, DateTime end)
{
    return queryable.Where(InBetween(start, end));
}

public static Expression<Func<MyEntity, bool>> InBetween(DateTime start,
    DateTime end)
{
    return x => x.DateColumn >= start && x.DateColumn < end;
}