sql-server - 数据库拦截导致 SQL 嵌套太深

Question

我在 Entity Framework 6(代码优先模型)中使用数据库拦截来实现软删除功能，但是间歇性地从 SQL Server 中抛出一个异常，指出“你的 SQL 语句的某些部分是嵌套的太深了。重写查询或将其分解为更小的查询。”

这是正在生成的 sql 的示例，正​​如您所看到的，生成的 SQL 非常离谱:

https://gist.github.com/junderhill/87caceac728809a8ca837b9d8b5189f3

我的EF拦截的代码如下:

 public class SoftDeleteInterceptor : IDbCommandTreeInterceptor
{
    public const string IsDeletedColumnName = "IsDeleted";
    public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
    {
        if (interceptionContext.OriginalResult.DataSpace != DataSpace.SSpace)
        {
            return;
        }

        var queryCommand = interceptionContext.Result as DbQueryCommandTree;
        if (queryCommand != null)
        {
            interceptionContext.Result = HandleQueryCommand(queryCommand);
        }
    }

    private static DbCommandTree HandleQueryCommand(DbQueryCommandTree queryCommand)
    {
        var newQuery = queryCommand.Query.Accept(new SoftDeleteQueryVisitor());
        return new DbQueryCommandTree(
            queryCommand.MetadataWorkspace,
            queryCommand.DataSpace,
            newQuery);
    }

    public class SoftDeleteQueryVisitor : DefaultExpressionVisitor
    {
        public override DbExpression Visit(DbScanExpression expression)
        {
            var table = (EntityType)expression.Target.ElementType;
            if (table.Properties.All(p => p.Name != IsDeletedColumnName))
            {
                return base.Visit(expression);
            }

            var binding = expression.Bind();
            return binding.Filter(
                binding.VariableType
                    .Variable(binding.VariableName)
                    .Property(IsDeletedColumnName)
                    .NotEqual(DbExpression.FromBoolean(true)));
        }
    }

Answer 1

更新:

这确实工作正常。但是，随着时间的推移，某些原因导致我们的服务器速度显着下降。每次我们重置 IIS 应用程序池时，它都会很快，但似乎 EF 以某种方式缓存或泄漏内存并减慢速度。我们暂时把拦截器拉了出来。

---

我们在工作中遇到了这个问题，经过很长一段时间后，这里有一个解决方案(尚未经过广泛测试；如果需要，我会用任何更新来更新它):

从根开始，在SoftDeleteQueryVisitor中，如果需要添加过滤器，则返回return binding.Filter(...)，修改查询.这反过来会触发 TreeCreated。这会遍历树，再次找到 DbScanExpression，然后重新添加过滤器。显而易见的解决方案是，如果过滤器已经存在，则不添加过滤器。

查看树的结构，我们发现filter是scan的一个父节点:

+       queryCommand    {DbQueryCommandTree
|_Parameters
| |_p__linq__0 : Edm.Guid
|_Query : Collection{Record['IDGUID'=Edm.Guid, 'Name'=Edm.String, 'FooField'=Edm.String, 'BarField'=Edm.Boolean, 'Group'=Edm.String, 'IsDeleted'=Edm.Boolean]}
  |_Project
    |_Input : 'Limit1'
    | |_Limit
    |   |_Filter
    |   | |_Input : 'Extent1'
    |   | | |_Filter
    |   | |   |_Input : 'Var_8'
    |   | |   | |_Scan : CodeFirstDatabase.LeaderboardSuite
    |   | |   |_Predicate
    |   | |     |_
    |   | |       |_Var(Var_8).IsDeleted
    |   | |       |_<>
    |   | |       |_True
    |   | |_Predicate
    |   |   |_
    |   |     |_Var(Extent1).IDGUID
    |   |     |_=
    |   |     |_@p__linq__0
    |   |_2
    |_Projection
      |_NewInstance : Record['IDGUID'=Edm.Guid, 'Name'=Edm.String, 'FooField'=Edm.String, 'BarField'=Edm.Boolean, 'Group'=Edm.String, 'IsDeleted'=Edm.Boolean]
        |_Column : 'IDGUID'
        | |_Var(Limit1).IDGUID
        |_Column : 'Name'
        | |_Var(Limit1).Name
        |_Column : 'FooField'
        | |_Var(Limit1).FooField
        |_Column : 'BarField'
        | |_Var(Limit1).BarField
        |_Column : 'Group'
        | |_Var(Limit1).Group
        |_Column : 'IsDeleted'
          |_Var(Limit1).IsDeleted}  System.Data.Entity.Core.Common.CommandTrees.DbQueryCommandTree

但是，一旦我们意识到我们同时拥有访问者和访问者的生命周期，我们就会意识到我们可以存储状态:

var newQuery = queryCommand.Query.Accept(new SoftDeleteQueryVisitor());

我们还知道 Filter 节点将在 Scan 节点之前被命中，因为它是 Scan 节点的父节点。此时，我们不得不考虑两种可能:查询中没有where子句，查询中有where子句。

此时，我只是遍历树，确定查询谓词是否使用了 Equals 或 NotEquals 子句中的 IsDeleted 字段，如果是这样，就不会在那里添加节点。我没有费心检查 Or 或 Xor 子句，因为这些情况极不可能发生。不过，如果您发现需要它们，它们很容易添加。

public class SoftDeleteQueryVisitor : DefaultExpressionVisitor
{
    private readonly List<DbScanExpression> _scanExpressions = new List<DbScanExpression>();

    public override DbExpression Visit(DbScanExpression expression)
    {
        var table = (EntityType)expression.Target.ElementType;
        if (table.Properties.All(p => p.Name != InterceptorConstants.IsDeletedColumnName))
        {
            return base.Visit(expression);
        }

        if (_scanExpressions.Contains(expression))
        {
            return base.Visit(expression);
        }

        var binding = expression.Bind();
        return binding.Filter(
            binding.VariableType
                .Variable(binding.VariableName)
                .Property(InterceptorConstants.IsDeletedColumnName)
                .NotEqual(DbExpression.FromBoolean(true)));
    }

    private bool HasField(DbScanExpression expression)
    {
        var table = (EntityType)expression.Target.ElementType;
        return table.Properties.Any(p => p.Name == InterceptorConstants.IsDeletedColumnName);
    }

    private bool HasExpression(DbExpression predicate)
    {
        if (predicate is DbAndExpression andExpression)
        {
            return HasExpression(andExpression.Left) || HasExpression(andExpression.Right);
        }

        if (predicate is DbComparisonExpression comparisonExpression &&
            (comparisonExpression.ExpressionKind == DbExpressionKind.NotEquals ||
            comparisonExpression.ExpressionKind == DbExpressionKind.Equals))
        {
            if (comparisonExpression.Right is DbPropertyExpression rightPropertyExpression &&
                rightPropertyExpression.Property.Name == InterceptorConstants.IsDeletedColumnName ||
                comparisonExpression.Left is DbPropertyExpression leftPropertyExpression &&
                leftPropertyExpression.Property.Name == InterceptorConstants.IsDeletedColumnName)
            {
                return true;
            }
        }

        return false;
    }

    public override DbExpression Visit(DbFilterExpression expression)
    {
        if (expression.Input.Expression is DbScanExpression scanExpression)
        {
            if (HasField(scanExpression))
            {
                if (HasExpression(expression.Predicate))
                {
                    _scanExpressions.Add(scanExpression);
                }
            }
        }

        return base.Visit(expression);
    }
}