Include lambda in linq-2-sql expression

Question

I', trying to refactor some linq-2-sql magic and there is something I apperantly cannot wrap my head around. The code uses this predicate builder

public static class PredicateBuilder {
    public static Expression<Func<T, bool>> True<T>() {
        return f => true;
    }

    public static Expression<Func<T, bool>> False<T>() {
        return f => false;
    }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                  Expression<Func<T, bool>> expr2) {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
            (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                   Expression<Func<T, bool>> expr2) {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
            (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}

Used in the folloowing way:

predicate =
    predicate.And(
        f =>
            f.Created != null
                ? args.Dato1.Date < f.Created.Value.Date &&
                  f.Created.Value.Date < args.Dato2.Date
                : false);

A lot.

So I was thinking maybe use a more descriptive name and less lines in the following way:

private Expression<Func<DAL.Faktura, bool>> beforeInclusiveExpression(Func<DAL.Faktura, DateTime?> getDateTime, DateTime date) {
    return f => getDateTime(f).HasValue && getDateTime(f).Value.Date <= date.Date;
}

And then build the predicate in the following way:

predicate =
    predicate
        .And(beforeInclusiveExpression(f => f.Created, d.Dato2)
        .And(afterInclusiveExpression(f => f.Created, d.Dato1);

But that does not work, it just throws the following error Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.. I understand that it's because the linq-2-sql provider does not know what to do with the lambda, but how can I translate it to something that will enable me to refactor to something more maintainable.

Answer 1

I finally found an answer after googling around for a while. Dziennys answer seems to work, but relies on Invoke which the original does not. Not being too comfortable in expressions and linq-2-sql, I want to keep the refactoring as close to the original as possible.

Stepping back we have a selector, a parameter (a datetime), and an operator between the two. That gives us this signature

Expression<Func<DAL.Faktura, DateTime?>>, DateTime?, Func<Expression, Expression, BinaryExpression> -> Expression<Func<DAL.Faktura, bool>>

From that we must create a new expression:

private Expression<Func<DAL.Faktura, bool>> dateTimeOperatorExpression(
    Expression<Func<DAL.Faktura, DateTime?>> selector, DateTime? date,
    Func<Expression, Expression, BinaryExpression> func) {

    //We only need the Date part of the DateTime. This lambda does the trick.
    var dateSelector = (Expression<Func<DateTime?, DateTime>>) (dt => dt.Value.Date);
    //f.Created != null
    var dateTimeNotNullPredicate = Expression.NotEqual(selector.Body,
        Expression.Constant(null, typeof (DateTime?)));

    //This transforms dateSelector: dt => dt.Value.Date
    //and selector: f => f.Created
    //into a lambda expression: f => f.Created.Value.Date
    var swap = new SwapVisitor(dateSelector.Parameters[0], selector.Body);
    var selectedPropertyDate = Expression.Lambda<Func<DAL.Faktura, DateTime>>(swap.Visit(dateSelector.Body),
        selector.Parameters);

    //Apply the supplied operator, here Expression.GreaterThanOrEqual or
    //Expression.LessThanOrEqual
    var predicate = func(selectedPropertyDate.Body, Expression.Constant(date.Value.Date, typeof (DateTime)));

    var combined = Expression.And(dateTimeNotNullPredicate, predicate);
    return Expression.Lambda<Func<DAL.Faktura, bool>>(combined, selector.Parameters);
}

The ExpressionVisitor helper, not sure exactly who the original author is, but I found it here on SO.

class SwapVisitor : ExpressionVisitor {
    private readonly Expression from, to;
    public SwapVisitor(Expression from, Expression to) {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node) {
        return node == from ? to : base.Visit(node);
    }
}

So, the original code

predicate.And(
    f =>
        f.Created != null
            ? d.Dato1.Date < f.Created.Value.Date &&
              f.Created.Value.Date < d.Dato2.Date
            : false);

Created an expression which looks roughly like this:

expr2
{ f => 
    if (f.Created != Convert(null)) 
    (
        (value(MyType+<>c__DisplayClass36).d.Dato1.Date <= f.Created.Value.Date)
        AndAlso 
        (f.Created.Value.Date <= value(MyType+<>c__DisplayClass36).d.Dato2.Date)
    )
    else
        (False)
}   System.Linq.Expressions.Expression<System.Func<DAL.Faktura,bool>>

While the above code and this method

private Expression<Func<DAL.Faktura, bool>> betweenInclusiveExpression(
    Expression<Func<DAL.Faktura, DateTime?>> selector, DateTime? beginingDateTime, DateTime? endDateTime) {
    var afterPredicate = dateTimeOperatorExpression(selector, beginingDateTime, Expression.GreaterThanOrEqual);
    var beforePredicate = dateTimeOperatorExpression(selector, endDateTime, Expression.LessThanOrEqual);

    var combined = Expression.AndAlso(afterPredicate.Body, beforePredicate.Body);
    return Expression.Lambda<Func<DAL.Faktura, bool>>(combined, selector.Parameters);
}

produce this expression:

expr2
{
    f => 
        (
            ((f.Created != null) AndAlso (f.Created.Value.Date >= 07-07-2015 00:00:00))
            AndAlso 
            ((f.Created != null) AndAlso (f.Created.Value.Date <= 07-07-2015 00:00:00))
        )
}   System.Linq.Expressions.Expression<System.Func<DAL.Faktura,bool>>

The only thing I'm not sure about, is if the constant date is different compared to the original. I would think that once the linq-2-sql provider translates the expression to SQL, it captures the variable. In other words it just happens a bit ealier now.

Answer 2

In order to make beforeInclusiveExpression method work for linq-to-sql you should change the parameter

Func<DAL.Faktura, DateTime?> getDateTime 

to

Expression<Func<DAL.Faktura, DateTime?>> getDateTime

But then you can't simply call it, you have to translate everything to Expression and create the expression tree.

Try:

private static Expression<Func<DAL.Faktura, bool>> beforeInclusiveExpression(Expression<Func<DAL.Faktura, DateTime?>> getDateTime, DateTime date)
{
    // return f => getDateTime(f).HasValue && getDateTime(f).Value.Date <= date.Date;
    var parameterF = Expression.Parameter(typeof(DAL.Faktura), "f");                          // f
    var getDateTimeInvocation = Expression.Invoke(getDateTime, parameterF);                   // getDateTime(f)
    var getDateTime_HasValue = Expression.Property(getDateTimeInvocation, "HasValue");        // getDateTime(f).HasValue
    var getDateTime_Value = Expression.Property(getDateTimeInvocation, "Value");              // getDateTime(f).Value
    var getDateTime_Value_Date = Expression.Property(getDateTime_Value, "Date");              // getDateTime(f).Value.Date

    return Expression.Lambda<Func<DAL.Faktura, bool>>(Expression.AndAlso(getDateTime_HasValue,// getDateTime(f).HasValue &&
        Expression.LessThanOrEqual(getDateTime_Value_Date, Expression.Constant(date.Date))),  // getDateTime(f).Value.Date <= date.Date
        parameterF);                                                                          
}