0

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.

2
  • Will not work as the predicatebuilder expects an Expression. I forgot to show the code where I use it, please see the edit where it is included. Commented Jul 6, 2015 at 10:30
  • 1
    Of course I have tried it - as far as I have figured out, the linq-2-sql provider need the expression tree to have a specific format. I'm unsure what, and how to achieve that. I don't mind extra code as about 600 LOC use the old approach, checking various dates. Commented Jul 6, 2015 at 11:58

2 Answers 2

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);                                                                          
}
Sign up to request clarification or add additional context in comments.

2 Comments

It works with a slight modofication. If I don't use Expression.AndAlso instead of Expression.And it does not short circuit the check in the expression tree. I have no idea how that translates to sql. I have found a different solution that is more in place replacement of the current, will post an answer shortly.
@hvidgaard Indeed, but in this case is doesn't really matter, it is evaluated to the same SQL query whether Expression.AndAlso or Expression.And is used, the same is with or.
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.

1 Comment

In case of a lambda treated as Expression the compiler creates a copy of referenced variables, and since DateTime is deeply immutable, it doesn't matter when it's read. Also, there's no invocation in the orginal expression, but it shouldn't be unfamiliar as it is used by the PredicateBuilder. And, well, you came up with an interesting solution.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.