How to bind a value to a variable in a programmatically generated Linq Expression

Question

I am trying to programmatically generate an expression tree (that will eventually get used in Linq-to-entity framework).

I can get the query to work just fine with one exception - it does not parametrize the query - which I want for Sql Server query plan reuse.

I read that in order for the generated sql to be parametrized, the expression needs to compare based on a variable. However, I cannot figure out how to assign the value to the variable in the expression tree. If I just use Expression.Constantit works (but is not parametrized).

So basically:

public Expression<Func<T, bool>> FooEquals<T>(
    Expression<Func<T, int>> propertyExpression, int value)
{
    ParameterExpression param = propertyExpression.Parameters.Single();

    int targetValueForEqualityComparison = 9;

    //There's some "special sauce" here which is why I don't 
    //use propertyExpression directly
    var body = Expression.Property(param, GetPropertyName(propertyExpression)); 

    //If I just use Expression.Constant, it works, but doesn't parametrize.
    //var equalExpression = ParameterExpression.Equal(body,
    //   Expression.Constant(targetValueForEqualityComparison, typeof(int)));

    var variable = Expression
                   .Variable(typeof(int), "targetValueForEqualityComparison");

    var assigned = Expression.Assign(variable, 
            Expression.Constant(targetValueForEqualityComparison, typeof(int)));

    //throws InvalidOperaitonException: "The parameter was not bound in the 
    //specified Linq to Entities query expression
    var equalExpression = ParameterExpression.Equal(body, variable); 

    //throws NotSupportedException: "Unknown LINQ expression of type 'Assign'.
    var equalExpression = ParameterExpression.Equal(body, assigned); 

    return Expression.Lambda<Func<T, bool>>(equalExpression, param);
}

How do I properly bind a value to the variable expression so that Linq-to-EntityFramework will parametrize the query?

Answer 1

I went ahead and tried it out because I was curious. The following seem to result in identical SQL, at least when used with Linq-to-SQL (which LINQPad does more readily than EF). I'd imagine it should work just the same with EF though.

Seems like a pretty convoluted way to just pass an integer in, but because this is what the compiler generates for a plain lambda function, I guess this is what the SQL generator looks for.

// Given this class, which is equivalent to the compiler-generated class
class Holder {
    public int AnInteger;
}

int id = 1;

// You get the same SQL with a plain lambda function

var query = db.Items.Where(i => i.Id == id);

// or with a handwritten expression:

var arg = Expression.Parameter(typeof(Item), "i");

var paramHolder = new Holder { AnInteger = id };

// essentially, (i) => i.Id == paramHolder.AnInteger
var lambda = Expression.Lambda<Func<Item, bool>>(
    Expression.Equal(
        Expression.Property(arg, "Id"),
        Expression.Field(
            Expression.Constant(paramHolder), "AnInteger")),
    arg);

// the SQL this translates to is equivalent to that of the first query
var query2 = db.Items.Where(lambda);