Linq: Dynamic Query Construction: the query is constructed on the client side.

dynamic-linq expression-trees lambda linq linq-to-entities

Question

I've been paying close attention to this conversation:

Use Linq to build queries rather than SQL strings.

the process of building expression trees with a dynamic table name.

To that goal, I developed the addWhere Extension method, which has the following syntax:

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;
}

[Timwi is acknowledged as the source of that code]

Which really works.

I may dial:

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

and it has the same practical effect as:

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

i.e., the same rows are returned.

However, when I began to monitor the sql log, I became aware of the line:

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

The generated query is:

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

as opposed to when I use the line

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

The generated query is:

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

Therefore, rather than being included to the sql, the comparison is now performed on the client side.

This is obviously not very exciting.

Since I copied and pasted much of the addWhere code from Timwi's (slightly different) example, part of it is beyond my comprehension. If there is a change I can make to this code to have the expression become the SQL statement rather than being decided client-side, please let me know.

I appreciate you taking the time to read this, and I would appreciate any feedback, suggestions, or links that might be of use to me. Of course, if I discover the answer in another way, I'll put it here as well.

Cheers.

1
1
5/23/2017 12:26:48 PM

Accepted Answer

Your conversion of the expression tree into a delegate is the main issue. Take a look at Queryable.Where's signature; expression trees, not delegates, are used to represent it. Therefore, you're dialing Enumerable.Where instead of actually. Therefore, you must dial.AsQueryable then, but it isn't enough magic in this case. Since the delegate is still there, it doesn't exactly return to the "only expression trees internally" realm. It's now wrapped in an expression tree, but you can no longer see the internal intricacies.

What you likely want is this:

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

I'll be the first to acknowledge that I haven't reviewed the rest of your code, so there might be additional issues, but that's the main issue. A highly typed expression tree is what you want, which is why you called the generic form ofExpression.Lambda ) that you may then enter.Queryable.Where . Take a go at it:)

1
9/23/2010 8:39:19 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow