StackOverflowException in LINQ to SQL

Question

We use LINQ to SQL to work with the database in our project, and almost everything is fine, but one thing: sometimes we need to build a huge WHERE clause using some kind of universal query object that is created by user input.

To construct the predicate for entering the WHERE statement, we used the tricks http://www.albahari.com/nutshell/predicatebuilder.aspx here , but the expression constructed in this way makes LINQ to SQL throwing a StackOverflowException if the WHERE predicate includes too many conditions (on the a few hundred) when it converts the resulting expression into an SQL query.

Is there a way to build a LINQ expression with many conditions so that LINQ to SQL can look at it well?

Answer 1

I agree with the OP. I had the same StackOverflowException using the BuildContainsExpression method, which was published by many people (my expression had 6000 OR). I modified BuildContainsExpression to create a balanced tree (depth = O (log (N))). In case this 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 GetOrExpr, which replaces the use of Aggregate in the original version. GetOrExpr recursively splits the predicate list in half to create the "left side" and the "right side", and then creates an expression (lhs OR rhs). A usage example 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))))

Answer 2

, LINQ LinqPad, , , , :

http://www.linqpad.net/