CODEWITHSUNDEEP
c#.netdatabaseentity-framework-core
SBFrancies
SBFrancies

Reputation: 4240

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

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.

Upvotes: 6

Views: 8110

Answers (1)

rhytonix
rhytonix

Reputation: 998

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

Upvotes: 4

Related Questions