Dynamic Linq where clause throws OutOfMemoryException

Question

I am a novice at Linq and a true beginner with expression trees.

I have a generic expression routine that builds a simple Linq where clause that I found at:
https://www.simple-talk.com/dotnet/net-framework/dynamic-linq-queries-with-expression-trees/

public Func<TSource,bool> SimpleFilter<TSource> (string property, object value)
{
    var type = typeof(TSource);
    var pe = Expression.Parameter(type, "p");
    var propertyReference = Expression.Property(pe,property);
    var constantReference = Expression.Constant(value);
    var ret = Expression.Lambda<Func<TSource, bool>>
        (Expression.Equal(propertyReference, constantReference), new[] { pe });
    return ret.Compile();
}

When I call the function as SimpleFilter("JobCustomerID", 449152) it yields (p => p.JobCustomerId == 449152) which is correct.

If I manually place that criteria in my Linq statement, I get the correct return.

var jj = db.VW_Job_List.Where((p => p.JobCustomerId == 449152));

However when called via the filter function, the Linq throws an OutOfMemoryException. It is called in my application as:

var jj = db.VW_Job_List.Where(SimpleFilter<VW_Job_List>("JobCustomerID", 449152));

If I call the function with a text criterion, it returns properly:

var jj = db.VW_Job_List.Where(SimpleFilter<VW_Job_List>("CompanyCode", "LCS"));

Is there something specific about using an integer variable that needs to be accommodated? Do I have something coded incorrectly? Any thoughts or insights will be appreciated.

Answer 1

The two calls

var jj = db.VW_Job_List.Where((p => p.JobCustomerId == 449152));

and

var jj = db.VW_Job_List.Where(SimpleFilter<VW_Job_List>("JobCustomerID", 449152));

are not equivalent. The first resolves to Queryable.Where, hence the filter is applied inside the database, while the second - to Enumerable.Where, thus causing loading the whole table in memory and applying the filter there.

The problem is that the return type of your SimpleFilter is Func<TSource, bool>. In order to make them equivalent, it should be Expression<Func<TSource, bool>>. Note that although they look visually the same, there is a huge difference between lambda expression and lambda delegate due to the different overload resolution when applied to IQueryable<T>.

So, change the method like this and try again:

public Expression<Func<TSource,bool>> SimpleFilter<TSource> (string property, object value)
{
    var type = typeof(TSource);
    var pe = Expression.Parameter(type, "p");
    var propertyReference = Expression.Property(pe,property);
    var constantReference = Expression.Constant(value);
    var ret = Expression.Lambda<Func<TSource, bool>>
        (Expression.Equal(propertyReference, constantReference), new[] { pe });
    return ret; // No .Compile()
}