Schema independent Entity Framework Code First Migrations

Question

I have troubles using Entity Framework migrations targeting Oracle databases since schema name is included in migrations code and for Oracle, schema name is also user name. My goal is to have schema-independent Code First Migrations (to be able to have one set of migrations for testing and production enviroments).

I have already tried this approach (using Entity Framework 6.1.3):

1) I have schema name in Web.config:

<add key="SchemaName" value="IPR_TEST" />

2) My DbContext takes schema name as a constructor parameter:

public EdistributionDbContext(string schemaName) 
    : base("EdistributionConnection")
{
    _schemaName = schemaName;
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema(_schemaName);
}

3) I had to implement IDbContextFactory for Entity Framework Migrations to be able to create my DbContext which does not have parameterless constructor:

public class MigrationsContextFactory : IDbContextFactory<EdistributionDbContext>
{
    public EdistributionDbContext Create()
    {
        return new EdistributionDbContext(GetSchemaName());
    }
}

4) I also configured Migration History Table to be placed within correct schema:

public class EdistributionDbConfiguration : DbConfiguration
{
    public EdistributionDbConfiguration()
    {
        SetDefaultHistoryContext((connection, defaultSchema) 
            => new HistoryContext(connection, GetSchemaName()));
    }
}

5) I modified code generated for migrations to replace hardcoded schema name. Eg. I replaced CreateTable("IPR_TEST.Users") with CreateTable($"{_schema}.Users"). (_schema field is set according to the value in Web.config).

6) I use MigrateDatabaseToLatestVersion<EdistributionDbContext, MigrationsConfiguration>() database initializer.

Having all this set up, I still have problems when I switch to different schema (eg. via web.config transformation) - an exception is thrown telling me that database does not match my model and AutomaticMigrations are disabled (which is desired). When I try to execute add-migration a new migration is generated where all object should be moved to different schema (eg: MoveTable(name: "IPR_TEST.DistSetGroups", newSchema: "IPR");, which is definitely not desired.

For me it seems that schema name is hard-wired somewhere in model string-hash in migration class (eg. 201509080802305_InitialCreate.resx), ie:

<data name="Target" xml:space="preserve">
    <value>H4sIAAAAAAAEAO09227jO... </value>
</data> 

It there a way how to tell Code First Migrations to ignore schema name?

Answer 1

You can create a derived DbContext and "override" modelBuilder.HasDefaultSchema(...) in OnModelCreating:

public class TestDbContext : ProductionDbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.HasDefaultSchema("TestSchema");
    }
}

Then you can create migrations for both contexts. See this question on how to create two migrations in one project.

The downside of this approach is that you have to maintain two seperate migrations. But it gives you the opportunity to adjust the configuration of your TestDbContext.

Answer 2

I was faced to same problem and thanks to your aproach I finally found a solution that seems to work pretty well:

1) I have the schema name in Web.config app settings:

<add key="Schema" value="TEST" />

2) I have a history context:

public class HistoryDbContext : HistoryContext
{
    internal static readonly string SCHEMA;

    static HistoryDbContext()
    {
        SCHEMA = ConfigurationManager.AppSettings["Schema"];
    }

    public HistoryDbContext(DbConnection dbConnection, string defaultSchema)
            : base(dbConnection, defaultSchema)
    { }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.HasDefaultSchema(SCHEMA);
    }
}

3) I have a db configuration that reference my history db context:

public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        SetDefaultHistoryContext((connection, defaultSchema) => new HistoryDbContext(connection, defaultSchema));
    }
}

4) And this is my db context:

public partial class MyDbContext : DbContext
{
    public MyDbContext()
        : base("name=MyOracleDbContext")
    { }

    public static void Initialize()
    {
        DbConfiguration.SetConfiguration(new MyDbConfiguration());
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDbContext, Migrations.Configuration>());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(string.Empty);
    }
}

5) Finally I call the Initialize method from the global.asax

protected void Application_Start()
{
    MyDbContext.Initialize();
}

The key is to set the default schema of the db context to String.Empty and the schema of the history context to the correct one. So when you create your migrations they are schema independant: the DefaultSchema variable of the resx of the migration will be blank. But the history db context schema is still correct to allow migrations checks to pass.

I am using the following nugets packages:

<package id="EntityFramework" version="6.2.0" targetFramework="net452" />
<package id="Oracle.ManagedDataAccess" version="12.2.1100" targetFramework="net452" />
<package id="Oracle.ManagedDataAccess.EntityFramework" version="12.2.1100" targetFramework="net452" />

You can then use Oracle migrations with success on different databases.