Linq: Dynamic Query Contruction: query moves to client-side

Question

Hi all, I've been following with great interest the converstaion here:

http://stackoverflow.com/questions/3769141/construct-query-with-linq-rather-than-sql-strings

with regards to constructing expression trees where even the table name is dynamic.

Toward that end, I've created a Extension method, addWhere, that looks like:

static public IQueryable<TResult> addWhere<TResult>(this IQueryable<TResult> query, string columnName, string value)
{
    var providerType = query.Provider.GetType();
    // Find the specific type parameter (the T in IQueryable<T>)
    var iqueryableT = providerType.FindInterfaces((ty, obj) => ty.IsGenericType && ty.GetGenericTypeDefinition() == typeof(IQueryable<>), null).FirstOrDefault();
    var tableType = iqueryableT.GetGenericArguments()[0];
    var tableName = tableType.Name;
    var tableParam = Expression.Parameter(tableType, tableName);
    var columnExpression = Expression.Equal(
        Expression.Property(tableParam, columnName),
        Expression.Constant(value));
    var predicate = Expression.Lambda(columnExpression, tableParam);
    var function = (Func<TResult, Boolean>)predicate.Compile();
    var whereRes = query.Where(function);
    var newquery = whereRes.AsQueryable();
    return newquery;
}

[thanks to Timwi for the basis of that code]

Which functionally, works.

I can call:

query = query.addWhere("CurUnitType", "ML 15521.1");

and it's functionally equivalent to :

query = query.Where(l => l.CurUnitType.Equals("ML 15521.1"));

ie, the rows returned are the same.

However, I started watching the sql log, and I noticed with the line:

query = query.Where(l => l.CurUnitType.Equals("ML 15521.1"));

The Query generated is:

SELECT (A bunch of columns)
FROM [dbo].[ObjCurLocView] AS [t0]
WHERE [t0].[CurUnitType] = @p0

whereas when I use the line

query = query.addWhere("CurUnitType", "ML 15521.1");

The query generated is :

SELECT (the same bunch of columns)
FROM [dbo].[ObjCurLocView] AS [t0]

So, the comparison is now happening on the client side, instead of being added to the sql.

Obviously, this isn't so hot.

To be honest, I mostly cut-and-pasted the addWhere code from Timwi's (slightly different) example, so some of it is over my head. I'm wondering if there's any adjustment I can make to this code, so the expression is converted into the SQL statement, instead of being determined client-side

Thanks for taking the time to read through this, I welcome any comments, solutions, links, etc, that could help me with this. And of course if I find the solution through other means, I'll post the answer here.

Cheers.

Answer 1

The big problem is that you're converting the expression tree into a delegate. Look at the signature of Queryable.Where - it's expressed in expression trees, not delegates. So you're actually calling Enumerable.Where instead. That's why you need to call AsQueryable afterwards - but that doesn't do enough magic here. It doesn't really put it back into "just expression trees internally" land, because you've still got the delegate in there. It's now wrapped in an expression tree, but you've lost the details of what's going on inside.

I suspect what you want is this:

var predicate = Expression.Lambda<Func<TResult, Boolean>>
      (columnExpression, tableParam);
return query.Where(predicate);

I readily admit that I haven't read the rest of your code, so there may be other things going on... but that's the core bit. You want a strongly typed expression tree (hence the call to the generic form of Expression.Lambda) which you can then pass into Queryable.Where. Give it a shot :)