子查询与"任何"和本地阵列生成嵌套太深SQL语句 [英] Subquery with "ANY" and local array generate nested too deep SQL Statement

问题描述

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();
}

上面的查询返回以下异常。 你的SQL语句的某些部分嵌套太深。重写查询或将其分解成更小的查询。

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."

到目前为止，我对网络这个错误在所有的研究与走向包含取代ANY指出。这里是一个网站，他们使用该解决方案解决该问题：的 http://blog.hompus.nl/2010/08/26/joining-an-iqueryable-with-an-ienumerable/ 结果
，而在我的情况，包含似乎并不实用，因为我请与最小值和最大值的范围。

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.

如何应此查询被写入到必须通过LinqToEntity产生一个适当的SQL语句？

感谢

推荐答案

您可以尝试建立动态查询：

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();
}

的

PredicateBuilder 帮助您生成一个 || 查询。从拿去 LinqKit （来源可用）
我已经测试过1000项参数（但他们其中的DateTime ，我没有其他查询件），它似乎工作。需要注意的是该程序使用LinqPad的另外一个分机，用来使 PredicateBuilder 绝招工作 AsExpandable 。请注意，我用EF 6.1.3，所以你的里程可能会有所不同。

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.

如果你不想使用LinqKit，我追加的我的的PredicateBuilder版本。它不需要使用的AsExpandable（），但它的语法稍有不同：

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;
    }
}

使用它像：

var predicate = PredicateBuilder.Create(query);

和那么一切都是相同的（但删除 LinqKit.Extensions.AsExpandable 部分）

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

这篇关于子查询与"任何"和本地阵列生成嵌套太深SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！