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

Question

I have a class, Users.

Users has a UserId property.

I have a method that looks something like this:

static IQueryable<User> FilterById(this IQueryable<User> p, Func<int, bool> sel)
{
   return p.Where(m => sel(m.UserId));
}

Inevitably, when I call the function:

var users = Users.FilterById(m => m > 10);

I get the following exception:

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

Is there any solution to this problem? How far down the rabbit hole of Expression.KillMeAndMyFamily() might I have to go?

To clarify why I'm doing this: I'm using T4 templates to autogenerate a simple repository and a system of pipes. Within the pipes, instead of writing:

new UserPipe().Where(m => m.UserId > 10 && m.UserName.Contains("oo") && m.LastName == "Wee");

I'd like to generate something like:

new UserPipe()
  .UserId(m => m > 10)
  .UserName(m => m.Contains("oo"))
  .LastName("Wee");

Answer 1

take a look at definition of Where of Queriable:

public static IQueryable<TSource> Where<TSource>(this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate);

use same. lammda expressions convert to Expression automatically

Answer 2

Let's take UserId as an example. You want to write:

new UserPipe().UserId(uid => uid > 10);

and want this to be the same as:

new UserPipe().Where(user => user.UserID > 10);

What you need to do is to take the expression tree of the first version and translate it to the second version.

---

So, first change the signature of UserId to accept an expression tree instead of a compiled lambda:

public static IQueryable<User> UserId(
    IQueryable<User> source, Expression<Func<int, bool>> predicate)

---

Then, write a method that converts the first expression tree to the second version. Let's have a look at the two expression trees:

Input:

        Lambda
         uid
          |
       BinaryOp
          >
    /            \
Parameter     Constant
   uid           10

Output:

         Lambda
          user
            |
        BinaryOp
            >
     /            \
 Property         Constant
  UserID             10
     | 
Parameter
   user

As you can see, all you need to do is take the body of the lambda, recursively replace all occurrences of the parameter uid with the property UserIdon the parameter user and create a new lambda expression with the transformed body and the parameter user.

You can use an ExpressionVisitor to do the replacement.

Answer 3

At first glance, it looks like you're building some expressions that Linq isn't going to know how to translate into T-SQL.

I might be misunderstanding what you're trying to do, but if you want to build chain-able expressions that Linq To Sql can understand, I'd highly recommend looking at the PredicateBuilder extensions here. Even if it's not exactly what you want, understanding how it works could give you some insight on what you need to achieve under the covers to make what you're doing work.

Answer 4

Thanks to dtb, here's what I came up with:

public class ExpressionMemberMerger : ExpressionVisitor
{
    MemberExpression mem;
    ParameterExpression paramToReplace;

    public Expression Visit<TMember, TParamType>(
        Expression<Func<TParamType, bool>> exp,
        Expression<Func<TMember, TParamType>> mem)
    {
        //get member expression
        this.mem = (MemberExpression)mem.Body;

        //get parameter in exp to replace
        paramToReplace = exp.Parameters[0];

        //replace TParamType with TMember.Param
        var newExpressionBody = Visit(exp.Body);

        //create lambda
        return Expression.Lambda(newExpressionBody, mem.Parameters[0]);
    }

    protected override Expression VisitParameter(ParameterExpression p)
    {
        if (p == paramToReplace) return mem;
        else return base.VisitParameter(p);
    }
}

Now, I can convert the predicate, methinks, with something like the code below. I've done a wee bit of testing on this code; it seems to be working, but I'd be interested to hear any comments/concerns:

static IQueryable<User> FilterById(this IQueryable<User> p, Expression<Func<int, bool>> sel)
{
   var merger = new ExpressionMemberMerger();

   Expression<Func<User, int>> mem = m => m.UserId;

   var expression = (Expression<Func<User, bool>>)merger.Visit(sel, mem);

   return p.Where(expression);
}