Cleanest way to change database schema in EF6

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.

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.

It looks like something we are doing here at my workplace.

Use synonyms for your objects!

A possibility would be to create synonyms dynamically for your test tables - and remove references to schema in your files

Say the user that connects is CONNECT_USER - must be different user as the schemas you're using which are SCHEM_DEV and SCHEM_TEST.

Here is how I would do the switch (Oracle PL/SQL scripting - connected as CONNECT_USER):

begin  for x in (select * from all_tables where owner='SCHEM_DEV')     loop    --- drop synonyms on SCHEM_DEV objects    execute immediate 'drop synonym '||table_name ;    --- create synonyms on SCHEM_TEST objects    execute immediate ' create or replace synonym '||table_name||' for SCHEM_TEST.'||table_name ;  end loop;end;/