c# - 为什么这个查询会引发 "SQL statement is nested too deeply"？

Question

昨天，一个好人帮我建了一个PredicateBuilder对于 Linq to Entities here .
似乎工作正常，但是完整的查询生成了这个可怕的 70 000 行长的东西 here (太长无法粘贴)，并提高SQL statement is nested too deeply .

这里是上下文:
用户正在寻找符合他标准的动物列表，特别是关于能力。
在 GUI 中，对于每种能力类型(例如:“可操纵性”、“敏捷性”等)，用户可以选择一个修饰符(“>”、“<”或“=”)和一个值。
例如，他可能想要显示“所有具有能力潜力 > 3 敏捷的动物”，或“所有具有能力技能 < 10 的动物和能力潜力 = 2 的敏捷性”

关于数据库:

Player带列 Id
Animal带列 Id
Ability带列:

• Id
• AnimalId
• TypeId (代表 Enum，可以是“Potential”、“BirthPotentiel”或“Skill”)
• AbilityId (代表可以是“Agility”或“Manibility”的枚举)
• Value

因此，每只动物都有一个 AllAbilities ICollection<Ability> 属性.

这是搜索功能(所有参数之前都已由用户在 GUI 中输入或留空)。

public async Task<List<Animal>> Search
    (
        Player player,
        int speciesId,
        int breedId,
        int coatId,
        int genderId,
        int minAge,
        int maxAge,
        int priceModifier, // int representing an Enum Criteria.ModifierE: ">", "<" or "="
        int priceValue,
        string ownerPseudo,
        bool isSearchingOwn,
        int minHeight,
        int maxHeight,
        int minWeight,
        int maxWeight,
        List<int> character, // representing list of Enum Flags
        List<int> abilitySkillModifiers, // representing list of Enum ModifierE: ">", "<" or "="
        List<int> abilitySkillValues,
        List<int> abilityPotentialModifiers, // representing list of Enum ModifierE: ">", "<" or "="
        List<int> abilityPotentialValues
    )
    {
        // You can see "PredicateUtils" class following the first link of this post
        var filter = PredicateUtils.Null<Animal>();

        filter = filter.And(e => speciesId != -1 ? e.SpeciesId == speciesId : true);
        filter = filter.And(e => breedId != -1 ? e.BreedId == breedId : true);
        filter = filter.And(e => coatId != -1 ? e.CoatId == coatId : true);
        filter = filter.And(e => genderId != -1 ? e.GenderId == genderId : true);
        filter = filter.And(e => minAge != -1 ? e.age >= minAge : true);
        filter = filter.And(e => maxAge != -1 ? e.age <= maxAge : true);

        string pseudo = isSearchingOwn ? player.Pseudo : ownerPseudo;
        filter = filter.And(e => !string.IsNullOrEmpty(ownerPseudo) ? e.Owner.Pseudo.Equals(pseudo, StringComparison.InvariantCultureIgnoreCase) : true);
        filter = filter.And(e => minHeight > 0 ? e.FinalHeight >= minHeight : true);
        filter = filter.And(e => maxHeight > 0 ? e.FinalHeight <= maxHeight : true);
        filter = filter.And(e => minWeight > 0 ? e.FinalWeight >= minWeight : true);
        filter = filter.And(e => maxWeight > 0 ? e.FinalWeight <= maxWeight : true);
        filter = filter.And(e => character.All(c => (e.character & c) == c));

        for (int i = 0; i < abilitySkillValues.Count; i++)
        {
            filter = filter.And(
                AbilitySkillFilter
                (
                    (Criteria.ModifierE)abilitySkillModifiers[i], // ">", "<", or "="
                    i,
                    abilitySkillValues[i] // value entered by the user for the current ability
                )
            );
        }

        for (int i = 0; i < abilityPotentialValues.Count; i++)
        {
            filter = filter.And(
                AbilityPotentialFilter
                (
                    (Criteria.ModifierE)abilityPotentialModifiers[i], // ">", "<", or "="
                    i,
                    abilityPotentialValues[i] // value entered by the user for the current ability
                )
            );
        }
        return await GetAll(filter);
    }

能力过滤函数:

static Expression<Func<Animal, bool>> AbilitySkillFilter(Criteria.ModifierE modifier, int abilityId, int userValue)
    {
        if (modifier == Criteria.ModifierE.More) // User chose ">"
            return e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId)
                ? e.AllAbilities.FirstOrDefault(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId).Value >= userValue
                : value <= 0;
        else if (modifier == Criteria.ModifierE.Equal) // User chose "<"
            return e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId)
                ? e.AllAbilities.FirstOrDefault(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId).Value == userValue
                : value == 0;
        else if (modifier == Criteria.ModifierE.Less) // User chose "<"
            return e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId)
                ? e.AllAbilities.FirstOrDefault(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId).Value <= userValue
                : value >= 0;
        else
            return null;
    }

    static Expression<Func<Animal, bool>> AbilityPotentialFilter(Criteria.ModifierE modifier, int abilityId, int userValue)
    {
        if (modifier == Criteria.ModifierE.More)
            return e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId)
                ? e.AllAbilities.FirstOrDefault(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId).Value >= userValue
                : e.AllAbilities.FirstOrDefault(a => a.TypeId == (int)Ability.TypeE.BirthPotential && a.AbilityId == abilityId).Value >= userValue;
        else if (modifier == Criteria.ModifierE.Equal)
            return e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId)
                ? e.AllAbilities.FirstOrDefault(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId).Value == userValue
                : e.AllAbilities.FirstOrDefault(a => a.TypeId == (int)Ability.TypeE.BirthPotential && a.AbilityId == abilityId).Value == userValue;
        else if (modifier == Criteria.ModifierE.Less)
            return e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId)
                ? e.AllAbilities.FirstOrDefault(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId).Value <= userValue
                : e.AllAbilities.FirstOrDefault(a => a.TypeId == (int)Ability.TypeE.BirthPotential && a.AbilityId == abilityId).Value <= userValue;
        else
            return null;
    }

解释:
在数据库中，Ability带有 TypeId == Potential 的行或 TypeId == Skill可能不存在，而 TypeId == BirthPotential总是这样。

• 万一TypeId == Potential当前动物和当前 AbilityId 不存在, 我想将用户值(value)与 TypeId == BirthPotential 进行比较行值(始终存在)。
• 万一TypeId == Skill当前动物和当前 AbilityId 不存在, 我想将用户值(value)与 0 进行比较。

如果有人对为什么这个查询产生如此糟糕的输出有任何建议并且有改进，我将非常感激。如果您需要更多信息，请不要犹豫。

解决方案:

感谢 juharr，它终于成功了建议(使用简单的 if 而不是三元的 if 以在不必要时不添加子句)，合并为 Ivan Stoev解决方案。
以年龄、性别、物种、伪装、最小高度、最大高度、性格、一项技能能力和一项潜在能力为标准，这是新的 SQL 输出:将近 70 000 行到 60 行!
Result here

非常感谢!

Answer 1

在做动态过滤的时候，尽量在表达式之外做更多的静态求值。这样您将获得更好的查询，因为目前 EF 不会优化常量表达式，除了构建静态 IN (...) 列表条件。

但是您当前代码的主要问题是在您的能力过滤器中使用 FirstOrDefault。通常尽量避免使用任何可能导致 SQL 子查询的查询构造类型，因为如您所见，出于某种原因 EF 嵌套了所有子查询，因此您会得到那个怪异的 SQL 和错误。安全的构造是使用 Any，它被转换为 SQL EXISTS 子查询，没有嵌套。

那么试试这个，看看你会得到什么:

static Expression<Func<Animal, bool>> AbilitySkillFilter(Criteria.ModifierE modifier, int abilityId, int userValue)
{
    Expression<Func<GameAnimal, bool>> filter = null;
    bool includeMissing = false;
    if (modifier == Criteria.ModifierE.More) // User chose ">"
    {
        filter = e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId && a.Value >= userValue);
        includeMissing = userValue <= 0;
    }
    else if (modifier == Criteria.ModifierE.Equal) // User chose "="
    {
        filter = e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId && a.Value == userValue);
        includeMissing = userValue == 0;
    }
    else if (modifier == Criteria.ModifierE.Less) // User chose "<"
    {
        filter = e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId && a.Value >= userValue);
        includeMissing = userValue >= 0;
    }
    if (filter != null && includeMissing)
        filter = filter.Or(e => !e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Skill && a.AbilityId == abilityId));
    return filter;
}

static Expression<Func<Animal, bool>> AbilityPotentialFilter(Criteria.ModifierE modifier, int abilityId, int userValue)
{
    if (modifier == Criteria.ModifierE.More)
        return e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId)
            ? e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId && a.Value >= userValue)
            : e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.BirthPotential && a.AbilityId == abilityId && a.Value >= userValue);
    else if (modifier == Criteria.ModifierE.Equal)
        return e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId)
            ? e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId && a.Value == userValue)
            : e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.BirthPotential && a.AbilityId == abilityId && a.Value == userValue);
    else if (modifier == Criteria.ModifierE.Less)
        return e => e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId)
            ? e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.Potential && a.AbilityId == abilityId && a.Value <= userValue)
            : e.AllAbilities.Any(a => a.TypeId == (int)Ability.TypeE.BirthPotential && a.AbilityId == abilityId && a.Value <= userValue);
    else
        return null;
}