Is there any way to directly query the in memory database used by Entity Framework Core?

.net c# database entity-framework-core

Question

I'm using the in-memory database provider to do some tests of a .Net Core 2.2 application. I need to make one of the tables in the application inaccessible - either by renaming it or dropping it. Is there any way to run that kind of query against the in memory DB directly? I've tried to get the connection using:

context.Database.GetDbConnection();

but that throws an error

Relational-specific methods can only be used when the context is using a relational database provider.

I am aware I can destroy the whole database with:

context.Database.EnsureDeleted();

but I need to retain it and only destroy or rename the one table.

1
1
1/16/2020 4:10:03 PM

Accepted Answer

The InMemory provider isn't backed by a relational database, and has a different set of operations that won't support your case.

You can instead use the SQLite in-memory mode (doc), then create an interceptor that intercepts the issued EF commands, and either suppress the creation of the table, or suppress other queries targeted at that table.

public class MyInterceptor : DbCommandInterceptor {
    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result) {
        if (ContainsBannedTable(command)) {
            //suppress the operation
            result = InterceptionResult<int>.SuppressWithResult(0);
        }
        return base.NonQueryExecuting(command, eventData, result);
    }

    private bool ContainsBannedTable(DbCommand command) {
        //or custom logic
        return command.CommandText.Contains("ToDeleteEntity");
    }
}

The following will throw an exception (Microsoft.EntityFrameworkCore.DbUpdateException... SqliteException: SQLite Error 1: 'no such table: ToDeleteEntity') when trying to access the undesired table.

var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();
var options = new DbContextOptionsBuilder<MyContext>()
                  .UseSqlite(connection)
                  .AddInterceptors(new MyInterceptor())
                  .Options
                  ;

var context = new MyContext(options);
context.Database.EnsureCreated();

context.AllowedEntity.Add(new AllowedEntity { Id = 1 });
context.SaveChanges();
Console.WriteLine(context.AllowedEntity.FirstOrDefault()?.Id); //1 - ok

context.ToDeleteEntity.Add(new ToDeleteEntity { Id = 1 });
//will throw an exception
context.SaveChanges();
Console.WriteLine(context.ToDeleteEntity.FirstOrDefault()?.Id);

//close the connection and clean up
//...
public class MyContext : DbContext {
    public MyContext(DbContextOptions options) : base(options) {
    }

    public DbSet<AllowedEntity> AllowedEntity { get; set; }
    public DbSet<ToDeleteEntity> ToDeleteEntity { get; set; }
}

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

public class AllowedEntity {
    public int Id { get; set; }
}
2
1/16/2020 9:21:32 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow