Expression not evaluated by SQL Server

Question

I'm trying to have an Expression resolved by LINQ/SQL Server, but it seems it can't handle the expression and just skips it altogether (it works on an in-memory dataset though).

    public static Func<TSource, bool> WhereNotNullClause<TSource>(string propertyName)
    {
        var type = typeof(TSource);
        var expression = Expression.Parameter(type, "p");
        var propertyNameReference = Expression.Property(expression, propertyName);
        var propertyValueReference = Expression.Constant(null);

        return Expression.Lambda<Func<TSource, bool>>(
            Expression.NotEqual(propertyNameReference, propertyValueReference),
            new[] { expression }).Compile();
    }

Called as follows:

var whereNotNullSelector = Expressions.WhereNotNullClause<ContactItem>("property");
var contactItems = context.ContactItems.Where(whereNotNullSelector).ToList();

The SQL generated does not include the where clause, so the where seems to be executed after the query resolves. What more things do I need to look after before I can get this to resolve properlY?

DavidG · Accepted Answer · 2017-07-13T10:28:02.900

Your method is returning a Func rather than an Expression. The code is having to use Enumerable.Where instead of Queryable.Where. This means it needs to materialise the data, drag the entire data set from the database and run the filter locally. Simply change the return type to be an expression and remove the .Compile:

public static Expression<Func<TSource, bool>> WhereNotNullClause<TSource>(string propertyName)
{
    var type = typeof(TSource);
    var expression = Expression.Parameter(type, "p");
    var propertyNameReference = Expression.Property(expression, propertyName);
    var propertyValueReference = Expression.Constant(null);

    return Expression.Lambda<Func<TSource, bool>>(
        Expression.NotEqual(propertyNameReference, propertyValueReference),
        new[] { expression });
}

Some additional reading here on why you Expression instead of Func. Think of Expressions as data rather than actual delegates, that means Linq to SQL can convert the expression tree into SQL whereas a Func is essentially a black box.

Expression not evaluated by SQL Server

1 Answers1