EF Code Migrations vs DB Scheme MS SQL and Oracle

Question

I am using Entity Framework Code Fist with migrations. I have a problem that migrations are bound with concrete DB schema. This is not such a problem with MS SQL. But in Oracle: schema = user. So my data model is bound with a DB User that can change.

When I change default schema with modelBuilder.HasDefaultSchema("SCHEME_NAME") I have to generate a new migration but I want to be able to deploy my app to any DB user in Oracle without having to change code and recopmile the project.

Márk Gergely Dolinka · Accepted Answer · 2015-05-15 09:04:11Z

1

Well, you have multiple options for achieving this, such as:

Using automatic migrations and making the modelBuilder.HasDefaultSchema(dbUserName) functions use an input parameter. But this has multiple disadvantages such as not being able to create migrations, instead every time it is automatically created which has limits when deploying (not being able to create scripts from it for deploy etc.)
You can implement a custom migration step which inherits from the CreateTableOperation class but as an input it does not take ("SCHEMA_NAME.TABLE_NAME" ...) but "TABLE_NAME" and dynamically gets the schema name when it is run (see one of my post about creating a custom migration operation to get the general idea)
Retrieving the user schema name and concatenating at migrations.

If you want the fastest solution I would choose the third option, which would simply look like this:

var schemaName = MigrationHelper.GetUserSpecificSchemaName();
CreateTable(String.Format("{0}.People", schemaName),
    c => new
    {
        Id = c.Int(nullable: false, identity: true),
    })
.PrimaryKey(t => t.Id);

Because don't forget that basically the code in these migrations run just like any other C# code, which is invoked through the Add-Migration PowerShell script method.

For implementing the GetUserSpecificSchemaName you can use ADO.NET that retrieves it from your Oracle database.

answered May 15, 2015 at 9:04

Márk Gergely Dolinka

1,4602 gold badges12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Volek Over a year ago

I actually found another way. When I use Oracle database I do not initiate database from code (so it does not throw an incompatible model exception). I only use migrations to generate SQL scripts. I use batch replace for schema on that scripts and then run scripts manually. It just a woraround. It is not ideal but I am able to use migrations and change DB schema too (with no incompatible model exception).

Collectives™ on Stack Overflow

EF Code Migrations vs DB Scheme MS SQL and Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related