Specifying Func to send to database

Question

I have a generic method where I want to specify the IQueryable to retrieve from, the field to use as an ID, and the name of the field to return.

But I get the error:

Method '' has no supported translation to SQL.

How can properly specify the valueExpression below so that it knows how to convert the expression to SQL? What am I doing wrong here?

public void RunTest()
{
    Test<DocumentType>(ctx.Query<DocumentType>(), x => x.DocTypeID, x => x.DocType);
}

public void Test<TTable>(IQueryable<TTable> table, Func<TTable, int> idFunc, Expression<Func<TTable, string>> nameExpr)
{
    var intVal = 1; 
    Expression<Func<TTable, bool>> valueExpression = item => idFunc(item) == intVal;

    //errors on the Where() here.
    var dbName = table.Where(valueExpression).Select(nameExpr).SingleOrDefault();
    //make assertions
}

Note: the intVal will be changing in a loop in the Test<>() method. I simplified it here for the question.

Answer 1

idFunc needs to be an Expression, and not a Func in order for the query provider to be able to translate it into SQL.

Once you have that, you can use the Compose method below to transform the id selector to a predicate comparing that value to the ID that you have by composing the expression with another:

public static Expression<Func<TFirstParam, TResult>>
    Compose<TFirstParam, TIntermediate, TResult>(
    this Expression<Func<TFirstParam, TIntermediate>> first,
    Expression<Func<TIntermediate, TResult>> second)
{
    var param = Expression.Parameter(typeof(TFirstParam), "param");

    var newFirst = first.Body.Replace(first.Parameters[0], param);
    var newSecond = second.Body.Replace(second.Parameters[0], newFirst);

    return Expression.Lambda<Func<TFirstParam, TResult>>(newSecond, param);
}

internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}
public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}

You can now write:

public void Test<TTable>(IQueryable<TTable> table,
    Expression<Func<TTable, int>> idSelector,
    Expression<Func<TTable, string>> nameSelector)
{
    int idValue = 1;
    var filter = idSelector.Compose(id => id == idValue);

    var dbName = table.Where(filter)
        .Select(nameSelector)
        .SingleOrDefault();
    //make assertions
}

Answer 2

A slightly simpler variation of the above is

 public static void Test<TTable>(IQueryable<TTable> table, Expression<Func<TTable, int>> idFunc, Expression<Func<TTable, string>>> nameExpr)
 {
   var intVal = 1;

   var constant = Expression.Constant(intVal, typeof(int));
   var equalExpr = Expression.Equal(idFunc.Body, constant);
   var lambaWrap = Expression.Lambda<Func<TTable, bool>>(equalExpr, (ParameterExpression)((MemberExpression)idFunc.Body).Expression);

   var dbName = table.Where(lambaWrap).Select(nameExpr).SingleOrDefault();

   //assert
 }

Resulting:

The problem with this is that every time the variable intVal is changed, the expression needs to be recomputed. To solve this, you would need to "grab" the outer variable. Since I don't know how to do that with expressions only, I usually wrap everything in yet another lambda expression that returns an expression itself and handles the internals of the grabbing:

 public static void Test2<TTable>(IQueryable<TTable> table, Expression<Func<TTable, int>> idFunc, Expression<Func<TTable, string>>> nameExpr)
 {
   var intVal = 1;

   var variableParam = Expression.Parameter(typeof(int));
   var equalExpr = Expression.Equal(idFunc.Body, variableParam);
   var lambaWrap = Expression.Lambda<Func<TTable, bool>>(equalExpr, (ParameterExpression)((MemberExpression)idFunc.Body).Expression);
   var lambdaDoubleWrap = Expression.Lambda<Func<int, Expression<Func<TTable, bool>>>>(lambaWrap, variableParam).Compile();

   var dbName = table.Where(lambdaDoubleWrap(intVal)).Select(nameExpr).SingleOrDefault();

   //assert
 }

The previously constant lambda wrap now becomes: And the question is where does that "Param_0" come from. This is where the second wrapping comes into play:

Read simply: the double wrap is a func that creates my expression of func.