带有“ANY"的子查询和本地数组生成嵌套太深的 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"替换为CONTAINS".这是他们使用此解决方案解决问题的一个站点:http://blog.hompus.nl/2010/08/26/joining-an-iqueryable-with-an-ienumerable/
但就我而言，CONTAINS"似乎不可用，因为我用 Min 和 Max 检查了 RANGE.

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 的另一个扩展，AsExpandable，用于使 PredicateBuilder 技巧"工作.请注意，我使用的是 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，我将附加 my 版本的 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)

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