c# - EF6 使用命令树拦截器禁用查询计划缓存

Question

我正在使用 IDbCommandTreeInterceptor 来实现软删除功能。在标准 TreeCreated 方法中，我检查给定的查询命令是否包含具有软删除属性的模型。如果他们这样做并且用户也请求获取软删除对象 --- 我用 querySoftDeleted = true 调用我的软删除访问者。这将使我的查询返回所有对象，那些在 IsDeleted 属性上具有 true 和 false 值的对象。

public class SoftDeleteInterceptor : IDbCommandTreeInterceptor {
    public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext) {
        ...            

        bool shouldFetchSoftDeleted = context != null && context.ShouldFetchSoftDeleted;

        this.visitor = new SoftDeleteQueryVisitor(ignoredTypes, shouldFetchSoftDeleted);

        var newQuery = queryCommand.Query.Accept(this.visitor);

        ...
    }
}


public class SoftDeleteQueryVisitor {

    ...

    public override DbExpression Visit(DbScanExpression expression)
    {
        // Skip filter if all soft deleted items should be fetched
        if (this.shouldFetchSoftDeleted)
            return base.Visit(expression);

        ...
        // TODO Apply `IsDeleted` filter.
    }
}

当我尝试检索所有对象(也被软删除)然后使用相同的查询稍后仅未删除的对象时，问题就出现了。像这样:

context.ShouldFetchSoftDeleted = true;
var retrievedObj= context.Objects.Find(obj.Id);

然后在上下文的新实例中(不在同一上下文中)

var retrievedObj= context.Objects.Find(obj.Id);

第二次，ShouldFetchSoftDeleted 设置为 false，一切正常，但 EF 认为此查询与之前的查询相同，并从缓存中检索它。检索到的查询不包含过滤器，因此返回所有对象(软删除和非软删除)。释放上下文时不会清除缓存。

现在的问题是，在理想情况下，是否有一种方法可以标记构造的 DbCommand，这样它就不会被缓存。这可以做到吗？或者有没有办法强制查询重新编译？

有一些方法可以避免缓存，但我宁愿不必为了解决这个问题而更改应用程序中的每个查询。

可以找到有关查询计划缓存的更多信息 here .

编辑 1

我正在为每个请求使用新的上下文 - 对象缓存应该不是问题。

编辑2

这是数据库日志。第一个调用是软删除，第二个调用是 w/o。 ... 部分相同，因此我将它们排除在日志之外。您可以看到这两个请求是相同的。第一个调用 CreateTree 并缓存结果树，以便在您执行时从缓存中检索树并且我的软删除标志不会在应该重新应用时重新应用。

Opened connection at 16.5.2015. 2:34:25 +02:00

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[IsDeleted] AS [IsDeleted], 
    ...
    FROM [dbo].[Items] AS [Extent1]
    WHERE [Extent1].[Id] = @p__linq__0


-- p__linq__0: '1' (Type = Int64, IsNullable = false)

-- Executing at 16.5.2015. 2:34:25 +02:00

-- Completed in 22 ms with result: SqlDataReader



Closed connection at 16.5.2015. 2:34:25 +02:00

The thread 0x1008 has exited with code 259 (0x103).
The thread 0x1204 has exited with code 259 (0x103).
The thread 0xf94 has exited with code 259 (0x103).
Opened connection at 16.5.2015. 2:34:32 +02:00

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[IsDeleted] AS [IsDeleted], 
    ...
    FROM [dbo].[Items] AS [Extent1]
    WHERE [Extent1].[Id] = @p__linq__0


-- p__linq__0: '1' (Type = Int64, IsNullable = false)

-- Executing at 16.5.2015. 2:34:32 +02:00

-- Completed in 16 ms with result: SqlDataReader



Closed connection at 16.5.2015. 2:34:32 +02:00

'vstest.executionengine.x86.exe' (CLR v4.0.30319: UnitTestAdapter: Running test): Loaded 'C:\Windows\assembly\GAC_MSIL\Microsoft.VisualStudio.DebuggerVisualizers\12.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.DebuggerVisualizers.dll'. Cannot find or open the PDB file.

正如我已经说过的，我在其自己的上下文中执行了每个请求，如下所示:

        using (var context = new MockContext())
        {
            // Test overrided behaviour 
            // This should return just deleted entity
            // Enable soft-delete retrieval
            context.ShouldFetchSoftDeleted = true;

            // Request 1 goes here
            // context.Items.Where(...).ToList()
        }

        using (var context = new MockContext())
        {
            // Request 2 goes here
            // context.Items.Where(...).ToList()
        }

Answer 1

区分查询计划缓存和结果缓存很重要:

Caching in the Entity Framework

查询计划缓存

The first time a query is executed, it goes through the internal plan compiler to translate the conceptual query into the store command (e.g. the T-SQL which is executed when run against SQL Server). If query plan caching is enabled, the next time the query is executed the store command is retrieved directly from the query plan cache for execution, bypassing the plan compiler.

The query plan cache is shared across ObjectContext instances within the same AppDomain. You don't need to hold onto an ObjectContext instance to benefit from query plan caching.

查询缓存是优化的 SQL 指令计划。这些计划有助于使 EF 查询比“冷”查询更快。这些计划被缓存在特定上下文之外。

对象缓存:

By default when an entity is returned in the results of a query, just before EF materializes it, the ObjectContext will check if an entity with the same key has already been loaded into its ObjectStateManager. If an entity with the same keys is already present EF will include it in the results of the query. Although EF will still issue the query against the database, this behavior can bypass much of the cost of materializing the entity multiple times.

换句话说，对象缓存是结果缓存的一种软形式。 Entity Framework 没有其他类型的二级缓存可用，除非您特别包含它。 Second-Level Caching in the Entity Framework and Azure

AsNoTracking

Returns a new query where the entities returned will not be cached in the DbContext or ObjectContext

Context.Set<Objects>().AsNoTracking();

或者您可以使用 MergeOption 禁用实体的对象缓存NoTracking 选项:

Will not modify cache.

context.Objects.MergeOption = MergeOption.NoTracking; 
var retrievedObj= context.Objects.Find(obj.Id);

相对于 AppendOnly 选项

Will only append new (top level-unique) rows. This is the default behavior.

这是你一直在挣扎的默认行为