EF6 Disable Query Plan Caching with Command Tree Interceptor

Question

I'm using IDbCommandTreeInterceptor to implement soft-delete functionality. Inside standard TreeCreated method I check whether given query command contains models with soft-delete attribute. If they do and user requested to fetch soft deleted object too --- I call my soft-delete visitor with querySoftDeleted = true. This will make my query return all object, those with true and those with false values on IsDeleted property.

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

The problem arises when I try to retrieve all objects (soft-deleted too) and then with the same query later object that are not deleted only. Something like this:

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

And then in new instance of context (not in same context)

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

Second time, ShouldFetchSoftDeleted is set to false, everything is great, but EF decides that this query was same as one before and retrieves it from cache. Retrieved query does not contain filter and thus returns all objects (soft-deleted and not). Cache is not cleared when context is disposed.

Now the question is whether there is a way, ideally, to mark constructed DbCommand so that it does not get cached. Can this be done? Or is there a way to force query recompilation?

There are ways to avoid caching, but I would rather not have to change every query in application just to fix this.

More info on Query Plan Caching can be found here.

Edit 1

I'm using new context for each request - object caching should not be the problem.

Edit 2

Here is database log. First call is with soft-delete and second is w/o. ... parts are identical so I excluded them from log. You can see that both requests are identical. First one calls CreateTree and resulted tree is cached so that when you execute, tree is retrieved from cache and my soft-delete flag is not re-applied when it should be.

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. 

As I already stated, I executed each request in its own context like so:

        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

It's important to distinguish between Query Plan Caching and Result Caching:

Caching in the Entity Framework

Query Plan Caching

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.

A Query Cache is an optimized SQL instruction plan. These plans help make EF queries faster than "Cold" Queries. These Plans are cached beyond and particular context.

Object caching:

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.

In other words, Object Caching is a soft form of Results Caching. No other kind 2nd Level Cache is available with Entity Framework unless you specifically include it. 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(); 

Or you can disable object caching for an entity using MergeOption NoTracking Option:

Will not modify cache.

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

As opposed to the AppendOnly Option

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

this is the default behavior you have been struggling with