StackOverflowException in LINQ to SQL

Question

We are using LINQ to SQL to work with database in our project and almost all is fine but one thing: sometimes we have to build a huge WHERE condition using some universal query object which is built by user input.

To build the predicate to put in WHERE statement we used tricks explained here http://www.albahari.com/nutshell/predicatebuilder.aspx but expression built in such a way makes LINQ to SQL throwing StackOverflowException if WHERE predicate includes too many conditions (several hundreds actually) when it is converting resulting expression into SQL query.

Is there a way to build LINQ expression with bunch of conditions so that LINQ to SQL treats it well?

Answer 1

If I were you, I would play with your LINQ query in LinqPad to see what you can do about getting around the error, it has a very neat expression builder:

http://www.linqpad.net/

Answer 2

I agree with the OP. I had the same StackOverflowException using the BuildContainsExpression method that many people have posted (my expression had 6000 ORs). I modified BuildContainsExpression to produce a balanced tree (depth = O(log(N))). In case it might be useful to someone, here it is:

 public static Expression<System.Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(
 Expression<System.Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)
    {
        if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
        if (null == values) { throw new ArgumentNullException("values"); }
        ParameterExpression p = valueSelector.Parameters.Single();

        // p => valueSelector(p) == values[0] || valueSelector(p) == ...
        if (!values.Any())
        {
            return e => false;
        }

        var equals = values.Select(
                 value => (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue))));

        //The use of ToArray here is very important for performance reasons.
        var body = GetOrExpr(equals.ToArray());

        return Expression.Lambda<System.Func<TElement, bool>>(body, p);
    }

    private static Expression GetOrExpr(IEnumerable<Expression> exprList)
    {
        return GetOrExpr(exprList, 0, exprList.Count() - 1);
    }

    private static Expression GetOrExpr(IEnumerable<Expression> exprList, int startIndex, int endIndex)
    {           
        if (startIndex == endIndex)
        {
            return exprList.ElementAt(startIndex);
        }
        else
        {
            int lhsStart = startIndex;
            int lhsEnd = (startIndex + endIndex - 1) / 2;
            int rhsStart = lhsEnd + 1;
            int rhsEnd = endIndex;
            return Expression.Or(GetOrExpr(exprList, lhsStart, lhsEnd), GetOrExpr(exprList, rhsStart, rhsEnd));
        }
    }

The key to the change is the GetOrExpr method, which replaces the use of Aggregate in the original version. GetOrExpr recursively splits the list of predicates in half to create a 'left hand side' and 'right hand side' and then creates the expression (lhs OR rhs). An example use would be something like this:

var customerIds = Enumerable.Range(1, 5);

Expression<Func<Customer, bool>> containsExpr = BuildContainsExpression<Customer, int>(c => c.CustomerId, customerIds);
Console.WriteLine(containsExpr);

This generates an expression like this:

c => (((c.CustomerId = 1) Or (c.CustomerId = 2)) Or ((c.CustomerId = 3) Or ((c.CustomerId = 4) Or (c.CustomerId = 5))))