Using a member access lambda expression to parametrise a LINQ to SQL predicate

Question

I have a query that needs to be reused all over the place and I need to vary which property/column gets used for a join.

What I'd like to be able to do is something like:

query = RestrictByProp(query, x=>x.ID);

An extremely simplified RestrictByProp() could be*:

private static IQueryable<Role> RestrictByProp(IQueryable<Role> query, 
                                               Func<Role, int> selector)
{
    return query.Where(x => selector(x) == 1);
}

The problem is that even this simple implementation causes a runtime exception:

Method 'System.Object DynamicInvoke(System.Object[])' has no 
supported translation to SQL.

**(Here I'm just adding a simple 'where' clause - in my real code I'd be using the lambda to pick which property to use for a join).*

I find this strange because if the member access lambda is done inline it is fine:

 private static IQueryable<Role> RestrictByID(IQueryable<Role> query)
 {
     return query.Where(x=> x.ID == 1);
 }

LINQ to SQL is also happy if you pass in an Expression<Func<Role, bool>> (i.e. when the parameter is x=>x.ID == 1) but that defeats the object because I need the value of the right-hand operand to be determined within the query.

Is there a way to somehow munge the lambda expression in RestrictByProp() so that LINQ to SQL knows how to generate the SQL?

Answer 1

First, you need to change your method signature:

private static IQueryable<Role> RestrictByProp(IQueryable<Role> query, 
    Expression<Func<Role, int>> selector)

That will mean your lambda expression is converted into an expression tree instead of a delegate.

You'll then need to build an Expression<Func<Role, bool>> from the existing expression tree.

It will look something like this:

LambdaExpression lambda = (LambdaExpression) selector;
var predicate = Expression.Equal(selector, Expression.Constant(1));
var lambdaPredicate = Expression.Lambda<Func<Role, bool>>(predicate,
                                                          lambda.Parameters);
return query.Where(lambdaPredicate);