Efcore: Precompiled queries assume the model for a given DbContext will never change

[JanEggers]

Steps to reproduce

when I use EF.CompileQuery to generate a Query it fails to find the result. executing the linq normally returns the result.

I also verified that it is not a threading issue. the item is there all the time and if im setting the debugger to call FindItemQuery again it still returns null.

The behavior is observed in a unittest. using xunit. If the test is executed alone it works most of the time. If I execute all the tests it sometimes returns null

```c#

var services = new ServiceCollection()
.AddDbContext(o => o.UseInMemoryDatabase(nameof(TestClass)+ nameof(TestMethod)))
.BuildServiceProvider();

private static readonly Func FindItemQuery =
EF.CompileQuery((ctx, key) => ctx.Items
.FirstOrDefault(x => x.Key == key));

    protected override Item FindItem(DbContext dbContext, string key)
    {
        var found = FindItemQuery(dbContext, key);

        if (found == null)
        {
            var xxx = dbContext.Items.FirstOrDefault(t => t.Key== key);
            //xxx is not null
        }

        return found ?? new Item()
        {
            Key = key
        };
    }

```

Further technical details

EF Core version: Microsoft.EntityFrameworkCore.InMemory Version="2.1.2"
Operating system: Win 10
IDE: (Visual Studio 2017 15.8)

[JanEggers]

updating to Microsoft.EntityFrameworkCore.InMemory Version="2.1.4" did not resolve the issue

[JanEggers]

from the in memoryQuery context i get from the precompiled view when removing firstordefault I see that the table contains the values but the enumerable is based on a snapshot that does not contain the values!

_innerEnumerable.results.source.source. Microsoft.EntityFrameworkCore.InMemory.Storage.Internal.InMemoryTableSnapshot = 0
!=
InMemoryQueryContext.Store.Tables.InMemoryTable.Rows.Count = 3

[JanEggers]

creating a new compiled query also returns the correct result

[JanEggers]

meh I dont get it if I do the exact same code as in EF.CompileQuery in my class it works if I use the default method I get an empty snapshot.

[JanEggers]

removing static from the FindItemQuery also fixed the issue, so ill put the Precompiled Queries into a di Singleton instead of using static

[smitpatel]

Likely, you are not using same InMemoryDatabase. InMemoryDatabase may change if service provider is re-created.

[ajcvickers]

@JanEggers Can you post a runnable project/solution or complete code listing that demonstrates the issue?

@smitpatel That was my thought too, but then, I can't reconcile that with, "If the test is executed alone it works most of the time. If I execute all the tests it sometimes returns null."

[JanEggers]

I will try to create a repro.

[JanEggers]

here is a repro:
https://github.com/JanEggers/EfCoreRepro_PrecompiledQuery

and yes it is another context but that should not be an issue as I pass the new context to the precompiled query when executing it.

[JanEggers]

so this is the problem?

https://github.com/aspnet/EntityFrameworkCore/blob/a3f0a78c41fe209924f8fb39fc77c421236f1bbe/src/EFCore/Query/Internal/CompiledQueryBase.cs#L90-L116

I think that if the context is captured inside the compiled query it should be provided as a constructor argument. passing it with every execution makes no sense when there is some state leaking from the first usage.

[JanEggers]

why isnt that something like:

(with a const or some reflection magic to get the context name)

```c#
if (typeof(TContext).GetTypeInfo().IsAssignableFrom(parameterExpression.Type.GetTypeInfo()))
{
return Expression.Parameter(
parameterExpression.Type,
"context")
}

https://github.com/aspnet/EntityFrameworkCore/blob/a3f0a78c41fe209924f8fb39fc77c421236f1bbe/src/EFCore/Query/Internal/CompiledQueryBase.cs#L58-L65

and here:

```c#
queryContext.AddParameter(
                    "context",
                    context);

[ajcvickers]

Note for triage: something strange is going on here with the in-memory database. Repro code is below. Running with SQL Server results in:

Query one: One
Query two: One
Query one: Two
Query two: Two

while with the only change being UseSqlServer to UseInMemoryDatabase the results are:

Query one: One
Query two: One
Query one:
Query two:

Note that the internal service provider is being managed by EF, The service provider created in the code is only used for AddDbContext and resolving that context.

SQL Server log fragment:

dbug: Microsoft.EntityFrameworkCore.Query[10101]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Compiling query model:
      '(from Item <generated>_1 in DbSet<Item>
      select [<generated>_1]).SingleOrDefault()'
dbug: Microsoft.EntityFrameworkCore.Query[10104]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Optimized query model:
      '(from Item <generated>_1 in DbSet<Item>
      select [<generated>_1]).SingleOrDefault()'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      (QueryContext queryContext) => IEnumerable<Item> _InterceptExceptions(
          source: IEnumerable<Item> _TrackEntities(
              results: IEnumerable<Item> _ToSequence(() => Item SingleOrDefault(IEnumerable<Item> _ShapedQuery(
                          queryContext: queryContext,
                          shaperCommandContext: SelectExpression:
                              SELECT TOP(2) [i].[Id], [i].[Name]
                              FROM [Items] AS [i],
                          shaper: UnbufferedEntityShaper<Item>))),
              queryContext: queryContext,
              entityTrackingInfos: { itemType: Item },
              entityAccessors: List<Func<Item, object>>
              {
                  Func<Item, Item>,
              }
          ),
          contextType: MyDbContext,
          logger: DiagnosticsLogger<Query>,
          queryContext: queryContext)
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'FOO' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'FOO' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [i].[Id], [i].[Name]
      FROM [Items] AS [i]

...

dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'BAR' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'BAR' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [i].[Id], [i].[Name]
      FROM [Items] AS [i]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [i].[Id], [i].[Name]
      FROM [Items] AS [i]

In-memory log:

dbug: Microsoft.EntityFrameworkCore.Query[10101]
      => Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryModelVisitor
      Compiling query model:
      '(from Item <generated>_1 in DbSet<Item>
      select [<generated>_1]).SingleOrDefault()'
dbug: Microsoft.EntityFrameworkCore.Query[10104]
      => Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryModelVisitor
      Optimized query model:
      '(from Item <generated>_1 in DbSet<Item>
      select [<generated>_1]).SingleOrDefault()'
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      => Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryModelVisitor
      (QueryContext queryContext) => IEnumerable<Item> _InterceptExceptions(
          source: IEnumerable<Item> _TrackEntities(
              results: IEnumerable<Item> _ToSequence(() => Item SingleOrDefault(IEnumerable<Item> EntityQuery(
                          queryContext: queryContext,
                          entityType: EntityType: Item,
                          key: Key: Item.Id PK,
                          materializer: (IEntityType entityType | MaterializationContext materializationContext) =>
                          {
                              instance = new Item()
                              instance.<Id>k__BackingField = int TryReadValue(ValueBuffer materializationContext.get_ValueBuffer(), 0, Item.Id)
                              instance.<Name>k__BackingField = string TryReadValue(ValueBuffer materializationContext.get_ValueBuffer(), 1, Item.Name)
                              return instance
                          }
                          ,
                          queryStateManager: True))),
              queryContext: queryContext,
              entityTrackingInfos: List<EntityTrackingInfo>
              {
                  EntityTrackingInfo,
              }
              ,
              entityAccessors: List<Func<Item, object>>
              {
                  Func<Item, Item>,
              }
          ),
          contextType: MyDbContext,
          logger: DiagnosticsLogger<Query>,
          queryContext: queryContext)

Repro code:

```C#
public class MyDbContext : DbContext
{
public MyDbContext(DbContextOptions options)
: base(options)
{
}

private static readonly LoggerFactory Logger
    = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseLoggerFactory(Logger);

public DbSet<Item> Items { get; set; }

}

public class Item
{
public int Id { get; set; }

public string Name { get; set; }

}

public class MyServiceCollection : ServiceCollection
{
public MyServiceCollection(string dbName)
{
this.AddDbContext(
o => o.UseInMemoryDatabase($@"Server=(localdb)\mssqllocaldb;Database={dbName};ConnectRetryCount=0"));
}
}

public class UnitTest1
{
public static readonly Func PreCompiled = EF.CompileQuery(ctx => ctx.Items.SingleOrDefault());

public void Test1()
{
    using (var services = new MyServiceCollection("FOO").BuildServiceProvider())
    {
        using (var scope = services.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var ctx = scope.ServiceProvider.GetRequiredService<MyDbContext>();

            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();

            ctx.Add(new Item { Name = "One" });

            ctx.SaveChanges();

            var item = PreCompiled(ctx);
            Console.WriteLine($"Query one: {item?.Name}");
        }

        using (var scope = services.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var ctx = scope.ServiceProvider.GetRequiredService<MyDbContext>();
            var item = PreCompiled(ctx);
            Console.WriteLine($"Query two: {item?.Name}");
        }
    }

    using (var services = new MyServiceCollection("BAR").BuildServiceProvider())
    {
        using (var scope = services.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var ctx = scope.ServiceProvider.GetRequiredService<MyDbContext>();
            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();

            ctx.Add(new Item { Name = "Two" });

            ctx.SaveChanges();

            var item = PreCompiled(ctx);
            Console.WriteLine($"Query one: {item?.Name}");
        }

        using (var scope = services.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var ctx = scope.ServiceProvider.GetRequiredService<MyDbContext>();
            var item = PreCompiled(ctx);
            Console.WriteLine($"Query two: {item?.Name}");
        }
    }
}

}
```

[JanEggers]

I investigated further and guess I found the issue:

https://github.com/aspnet/EntityFrameworkCore/blob/c599721a3f9bcc71a35f7e363bcb1e46ef92d8ba/src/EFCore.InMemory/Storage/Internal/InMemoryStore.cs#L119

the table is not found at that line because because the EntityType are not ReferenceEqual because one is from the first model and the other is from the second model

the easiest fix would be to enable matching by name that is already in place but I dont know why it is disabled by default

[ajcvickers]

@JanEggers Did you root cause why the in-memory database is rooted in a different internal service provider?

[JanEggers]

@ajcvickers I did not check. but the different database is because of the different strings provided in UseInMemoryDatabase. my understanding is that each unique string gets its own database. and that is the way I intended my tests so I get a fresh isolated database for each test.

https://github.com/aspnet/EntityFrameworkCore/blob/39acb62595ba64347e68ac986d55040ddd496395/src/EFCore.InMemory/Storage/Internal/InMemoryStoreCache.cs#L62-L63

from my point of view the real problem is that the entity types are not ref equal. so I guess i could also workaround the issue by creating the model first with a modelbuilder and then use the same model for multiple contexts.

[ajcvickers]

Notes from triage: we think there are two things going here:

• The in-memory database is creating a different model instance when used with the second external service provider. This is probably because a new internal service provider is being created, which may be by-design, but should be investigated anyway. @ajcvickers to investigate this aspect.
• A query that was compiled with one model is then being re-used with another model without re-compiling. This is the more serious issue which we may need to fix for 2.2. @smitpatel to investigate this aspect.

[smitpatel]

Currently the query cache has 4 core components

• Expression query
• Model
• QueryTrackingBehaviour
• Async
  Relational adds UseRelationalNulls & SqlServer adds RowNumberPaging.

So we are safe from query cache side.

[kroymann]

Is there any update on this issue? I just spent a few hours updating a bunch of code to use precompiled queries only to discover that my unit tests are now all broken because of this issue, which means I'll have to shelve those performance improvements and hope that I can resurrect them at some future date when this is fixed. I see that @JanEggers's proposed solution was rejected. Is there any other known work-around?

[JanEggers]

@kroymann my workaround was to place the precompiled queries on a singleton service that way they get rebuild each time the service collection is rebuild

[smitpatel]

@ajcvickers - Can you try this on 3.1? I am getting even weirder output.
I see you are already assigned to it in 5.0. I will leave it for you then. 😄

[smitpatel]

This is working in current daily. Not sure what to add for regression test here.

[ajcvickers]

@smitpatel This still repros when the in-memory database is rooted in a different internal service provider:

```C#
public class MyDbContext : DbContext
{
public MyDbContext(DbContextOptions options)
: base(options)
{
}

public DbSet<Item> Items { get; set; }

}

public class Item
{
public int Id { get; set; }

public string Name { get; set; }

}

public class MyServiceCollection : ServiceCollection
{
public MyServiceCollection(string dbName, bool sensitive)
{
this.AddDbContext(
o =>
{
o.UseInMemoryDatabase($@"Server=(localdb)mssqllocaldb;Database={dbName};ConnectRetryCount=0");

            if (sensitive)
            {
                o.EnableSensitiveDataLogging(); // Force new internal service provider
            }
        });
}

}

public class UnitTest1
{
public static readonly Func PreCompiled = EF.CompileQuery(ctx => ctx.Items.SingleOrDefault());

public static void Main()
{
    using (var services = new MyServiceCollection("FOO", true).BuildServiceProvider())
    {
        using (var scope = services.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var ctx = scope.ServiceProvider.GetRequiredService<MyDbContext>();

            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();

            ctx.Add(new Item { Name = "One" });

            ctx.SaveChanges();

            var item = PreCompiled(ctx);
            Console.WriteLine($"Query one: {item?.Name}");
        }

        using (var scope = services.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var ctx = scope.ServiceProvider.GetRequiredService<MyDbContext>();
            var item = PreCompiled(ctx);
            Console.WriteLine($"Query two: {item?.Name}");
        }
    }

    using (var services = new MyServiceCollection("BAR", false).BuildServiceProvider())
    {
        using (var scope = services.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var ctx = scope.ServiceProvider.GetRequiredService<MyDbContext>();
            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();

            ctx.Add(new Item { Name = "Two" });

            ctx.SaveChanges();

            var item = PreCompiled(ctx);
            Console.WriteLine($"Query one: {item?.Name}");
        }

        using (var scope = services.GetRequiredService<IServiceScopeFactory>().CreateScope())
        {
            var ctx = scope.ServiceProvider.GetRequiredService<MyDbContext>();
            var item = PreCompiled(ctx);
            Console.WriteLine($"Query two: {item?.Name}");
        }
    }
}

}

Query one: One
Query two: One
Query one:
Query two:
```

[smitpatel]

Precompiled query does not use Model/QueryTrackingBehavior. If you change those thing on context underlying, the executor won't change.

[ajcvickers]

@smitpatel Here's a repro that more clearly shows what is happening and removes the in-memory part completely. The compiled query is using the model from the first time this context type was used, even though it is now being used with a different model. This means it generates the following query:

      SELECT TOP(2) [o].[Id], [o].[Name]
      FROM [OneTable] AS [o]
````

This fails because the second model does not map the entity type to `OneTable`.

The underlying issue here, as stated in the [comment above](https://github.com/dotnet/efcore/issues/13483#issuecomment-427486101), is that the compiled query is coupled to the DbContext with an implicit assumption that the model for that context type will not change. This is not a valid assumption.

```C#
public class MyDbContext : DbContext
{
    private readonly string _databaseName;

    public MyDbContext(string databaseName)
    {
        _databaseName = databaseName;
    }

    public DbSet<Item> Items { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Item>().ToTable(_databaseName + "Table");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer($@"Server=(localdb)\mssqllocaldb;Database={_databaseName};ConnectRetryCount=0")
            .LogTo(Console.WriteLine, new[] {RelationalEventId.CommandExecuting});

        if (_databaseName == "One")
        {
            optionsBuilder.EnableSensitiveDataLogging();
        }
    }
}

public class Item
{
    public int Id { get; set; }

    public string Name { get; set; }
}

public class UnitTest1
{
    public static readonly Func<MyDbContext, Item> PreCompiled =
        EF.CompileQuery<MyDbContext, Item>(ctx => ctx.Items.SingleOrDefault());

    public static void Main()
    {
        using (var context = new MyDbContext("One"))
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Item {Name = "One"});

            context.SaveChanges();
        }

        using (var context = new MyDbContext("Two"))
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Item {Name = "Two"});

            context.SaveChanges();
        }

        using (var context = new MyDbContext("One"))
        {
            var item = PreCompiled(context);
            Console.WriteLine($"Query one: {item?.Name}");
        }

        using (var context = new MyDbContext("Two"))
        {
            var item = PreCompiled(context);
            Console.WriteLine($"Query one: {item?.Name}");
        }
    }
}

dbug: 9/4/2020 16:02:09.698 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [o].[Id], [o].[Name]
      FROM [OneTable] AS [o]
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'OneTable'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)

[smitpatel]

That is what said that CompileQuery does not change if Model/QueryTrackingBehavior changes.
CompileQuery is a Func<QueryContext, TResult> QueryContext does not depend on those things because query cache takes care of it and generates different delegate. Compile query does not have information about any of the services, it has only DbContext which it uses as service locator for IQueryCompiler. So introducing additional states to compute the delegate will make pre-compiled query same as implicitly complied query.

[ajcvickers]

Note from triage: consider this as part of #14551