Cleanest way to change database schema in EF6

Question

Currently we are writing a page in mvc5, with an oracle sql database connected with entitiy framework 6.

We currently have two schemas in the oracle database, one for testing and the other for development. The model in entitiy framework is generated from the development database, and works perfectly with it.

The problem comes, when changing the connection string to the testing schema. When the connection string is changed the application is unable to locate the tables (as they still reference the development schemes).

Currently I can fix this, by deleting all the tables from the model, and recreating the model from the correct schema, or manually editing every file referencing the schema. Both solutions are kinda tiresome and error prone.

How is this scenario usually dealt with?

EDIT

It seems that changing the database and retaining the schema, does not produce any error. So this is only schema related.

Answer 1

I guess this is a perfect use case for using entity framework command interceptors. I just tried and it works perfectly, even for Entity Framework DB-First approach.

You can register a custom command interceptor like this:

DbInterception.Add(new ReplaceSchemaInterceptor(newSchema: "[my]"));

This line will replace [dbo] schema name with the [my] schema name, before the query reaches the database. Luckily, schema name is enclosed with square brackets when Entity Framework generates the command text, so it's easy to match and replace. BTW, I'm not an Oracle expert, so I'm assuming that Oracle queries also include schemas in the same format. If not, then maybe you will have to tweak the implementation a bit (to replace the schema from whatever format it is generated by EF).

ReplaceSchemaInterceptor is a class that implements IDbCommandInterceptor interface. Inside this class, you need to replace the schema with your own schema. Below is the implementation of this class:

class ReplaceSchemaInterceptor : IDbCommandInterceptor 
{
    private readonly string _newSchema;

    public ReplaceSchemaInterceptor(string newSchema)
    {
        _newSchema = newSchema;
    }

    public void NonQueryExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void NonQueryExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        command.CommandText = command.CommandText.Replace("[dbo]", _newSchema);
    }

    public void ReaderExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
    {
        command.CommandText = command.CommandText.Replace("[dbo]", _newSchema);
    }

    public void ScalarExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        command.CommandText = command.CommandText.Replace("[dbo]", _newSchema);
    }
}

And lastly, the code is not perfect. You need to add some null checks for the constructor parameters, and maybe get rid of the code duplication inside implementation methods when replacing command text (extract into reusable method?). Right now it just does what you had asked for.

Answer 2

With fluent mappings in Entity Framework code-first you can indicate the default schema at runtime. This is one statement in OnModelCreating in your DbContext subclass, for instance:

modelBuilder.HasDefaultSchema("dev");

You're used to regenerating the model from the database, from which I conclude that the model doesn't contain many (or any) customizations that would make model generation a painstaking operation. This also should make it relatively easy to move to code-first. So I'd recommend you do that.

In Visual Studio, you can generate a code-first model from an existing database by adding an "ADO.Net Entity Data Model" from the templates that come with Entity Framework tools for Visual Studio. (Probably pre-installed). Choose the option "Code First from database" and follow the guidelines.

If you do that, you'll find a connection string in the project containing the model. This connection string may serve as template for the connection string you will put in the config file of your executing assembly. You'll notice that it doesn't look like...

metadata=res://* ... provider=System.Data.SqlClient;provider connection string="...""

This is the connection string that belongs to a database-first edmx model. It contains a path to the metadata files that are generated as resources into the assembly. Instead, the connection string will be a simple ADO.Net connection string. With code-first, EF will generate the meta data at runtime.

If you have this, you can add an entry in your config file for the default database schema and use that to set the schema as I showed above.