Subquery with "ANY" and local array generate nested too deep SQL Statement

Question

public IEnumerable<Table1> GetMatchingTable1(string param, double[] Thicknesses)
{
    return DBContext.Table1.Where(c => c.Field1 == param
                                    && Thicknesses.Any(Thickness => Thickness >= c.MinThickness && Thickness <= c.MaxThickness))
                           .ToList();
}

Above query return the following exception. "Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries."

So far, all my research on the web for this error pointed toward replacing "ANY" with "CONTAINS". Here is one site where they fix the problem using this solution : http://blog.hompus.nl/2010/08/26/joining-an-iqueryable-with-an-ienumerable/
But in my case, "CONTAINS" doesn't seem usable since I check a RANGE with Min and Max.

How should this query be written to have a proper SQL Statement generated by LinqToEntity?

Thanks

Answer 1

You could try to build the query dynamically:

public IEnumerable<Table1> GetAllCoilLengthSettingsWithChilds(string param, double[] Thicknesses)
{
    // Base query
    var query = LinqKit.Extensions.AsExpandable(DBContext.Table1.Where(c => c.Field1 == param));

    // All the various || between the Thickness ranges
    var predicate = LinqKit.PredicateBuilder.False<Table1>();

    foreach (double th in Thicknesses)
    {
        // Don't want a closure around th
        double th2 = th;
        predicate = predicate.Or(c => th2 >= c.MinThickness && th2 <= c.MaxThickness);
    }

    // This is implicitly in && with the other Where
    query = query.Where(predicate);

    return query.ToList();
}

The PredicateBuilder helps you build an || query. Take it from the LinqKit (source available) I've tested it with 1000 parameters (but they where DateTime, and I didn't have other query pieces), and it seems to work. Note that the program uses another extension of LinqPad, AsExpandable, used to make the PredicateBuilder "trick" work. Note that I'm using EF 6.1.3, so your mileage may vary.

If you don't want to use LinqKit, I'm appending my version of PredicateBuilder. It doesn't require the use of AsExpandable(), but its syntax is slightly different:

public class PredicateBuilder<T>
{
    // We share a single parameter for all the PredicatBuilder<T>
    // istances. This isn't a proble, because Expressions are immutable
    protected static readonly ParameterExpression Parameter = Expression.Parameter(typeof(T), "x");

    protected Expression Current { get; set; }

    // Returns an empty PredicateBuilder that, if used, is true
    public PredicateBuilder()
    {
    }

    // Use it like this: .Where(predicate) or .Any(predicate) or 
    // .First(predicate) or...
    public static implicit operator Expression<Func<T, bool>>(PredicateBuilder<T> predicate)
    {
        if (object.ReferenceEquals(predicate, null))
        {
            return null;
        }

        // Handling of empty PredicateBuilder
        Expression current = predicate.Current ?? Expression.Constant(true);

        Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(current, Parameter);
        return lambda;
    }

    public static implicit operator PredicateBuilder<T>(Expression<Func<T, bool>> expression)
    {
        var predicate = new PredicateBuilder<T>();

        if (expression != null)
        {
            // Equivalent to predicate.Or(expression)
            predicate.And(expression);
        }

        return predicate;
    }

    public void And(Expression<Func<T, bool>> expression)
    {
        if (expression == null)
        {
            throw new ArgumentNullException("expression");
        }

        var expression2 = new ParameterConverter(expression.Parameters[0], Parameter).Visit(expression.Body);
        this.Current = this.Current != null ? Expression.AndAlso(this.Current, expression2) : expression2;
    }

    public void Or(Expression<Func<T, bool>> expression)
    {
        if (expression == null)
        {
            throw new ArgumentNullException("expression");
        }

        var expression2 = new ParameterConverter(expression.Parameters[0], Parameter).Visit(expression.Body);
        this.Current = this.Current != null ? Expression.OrElse(this.Current, expression2) : expression2;
    }

    public override string ToString()
    {
        // We reuse the .ToString() of Expression<Func<T, bool>>
        // Implicit cast here :-)
        Expression<Func<T, bool>> expression = this;
        return expression.ToString();
    }

    // Small ExpressionVisitor that replaces the ParameterExpression of
    // an Expression with another ParameterExpression (to make two
    // Expressions "compatible")
    protected class ParameterConverter : ExpressionVisitor
    {
        public readonly ParameterExpression From;
        public readonly ParameterExpression To;

        public ParameterConverter(ParameterExpression from, ParameterExpression to)
        {
            this.From = from;
            this.To = to;
        }

        protected override Expression VisitParameter(ParameterExpression node)
        {
            if (node == this.From)
            {
                node = this.To;
            }

            return base.VisitParameter(node);
        }
    }
}

public static class PredicateBuilder
{
    // The value of source isn't really necessary/interesting. Its type
    // is :-) By passing a query you are building to Create, the compiler
    // will give to Create the the of the object returned from the query
    // Use it like:
    // var predicate = PredicateBuilder.Create<MyType>();
    // or
    // var predicate = PredicateBuilder.Create(query);
    public static PredicateBuilder<T> Create<T>(IEnumerable<T> source = null)
    {
        return new PredicateBuilder<T>();
    }

    // Useful if you want to start with a query:
    // var predicate = PredicateBuilder.Create<MyType>(x => x.ID != 0);
    // Note that if expression == null, then a new PredicateBuilder<T>()
    // will be returned (that by default is "true")
    public static PredicateBuilder<T> Create<T>(Expression<Func<T, bool>> expression)
    {
        // Implicit cast to PredicateBuilder<T>
        return expression;
    }
}

Use it like:

var predicate = PredicateBuilder.Create(query);

and then everything is the same (but remove the LinqKit.Extensions.AsExpandable part)