I am struggling to see how to use Entity Framework Code First migrations to target multiple identical Oracle databases, where each database exists in its own schema. I have found others with the same issue - looking at several forums, and almost the exact issue has been here raised before:
Schema independent Entity Framework Code First Migrations
We serve several clients with their own self-hosted Oracle databases, and we develop against our Oracle database containing many copies of the client databases, with each client separated into their own user/schema, for example:
Client 1
- Table 1
- Table 2
Client 2
- Table 1
- Table 2
The problem stems from the insistence that an explicit schema is used with Code First migrations, either “dbo”, (the Code First default) or another schema specified by:
modelBuilding.HasDefaultSchema(“SchemaName”)
This will mean that a set of migrations targeting SQL Server databases, using default schema “dbo”, can be used to update any matching database (Either Client 1 or Client 2 in the example above).
However, on an Oracle system, the migrations will end up being tied to a specific schema, meaning the migrations would only work for one of these, i.e. if the migrations were generated against the Client 1 schema, but the app was pointed at Client 2, it would not work.
Here are a collection of ideas I've had for dealing with this problem, and solutions I have tried:
Using HasDefaultSchema(String.Empty). This fails when generating an initial migration, with the error: "Value cannot be null. Parameter name: seqOwner". This suggests to me that the OracleMigrationSqlGenerator is not intended to work without specific Schema names.
Finding some way to pull the Oracle user name out of the current connection string, and use that as the schema name in the migration. I've already had to do this in the app itself, to stop Entity Framework looking in the "dbo" Schema for an Oracle database, but that was done by examining ConfigurationManager.ConnectionStrings when the model is built, I'm not sure how the same would be achieved from inside migration code.
Similar to #2, but just using a variable somewhere - maybe in the appsettings element in the Web.config, to store the Oracle schema, and referring to that from inside the migration code. An Oracle migration script would then refer to this variable instead to find out the schema. Both this and #2 seem very hacky though.
How could the above be achieved, and would there be any major issues with using approach 2/3 above? I feel I must be missing something very simple here.
