Entity Framework code first auto migration not working with Oracle database

Question

I have some issue with an Oracle database. When I run my Windows Forms application using Entity Framework 6 to apply all database related changes to the Oracle database, I get this error:

Automatic migrations that affect the location of the migrations history system table (such as default schema changes) are not supported.
Please use code-based migrations for operations that affect the location of the migrations history system table.

Oracle database version: "Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0"

I am using a code-first approach with auto migration enabled. This code first approach working perfectly when I connect to SQL Server database (note: SQL Server database connection only for cross check) but have this issue with Oracle.

What I tried from my side

I added code-based migration script i.e. Add-Migration CreateNewDB and then applied this migration to Oracle database and it works.

But I want to auto-update the database and apply any changes to the Oracle database which is still not working automatically (auto migration). Currently I need to create a code-based migration and apply that to Oracle database every time.

Sample code on model create

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {

            base.OnModelCreating(modelBuilder);
            
            modelBuilder.HasDefaultSchema("PDBADMIN");
       
       
            modelBuilder.Entity<ADHOCCHECK>()
                .Property(e => e.sortrev)
                .IsUnicode(false);

            modelBuilder.Entity<ADHOCCONSTRAINT>()
                .Property(e => e.fldtype)
                .IsUnicode(false);

            modelBuilder.Entity<ADHOCCONSTRAINT>()
                .Property(e => e.fldstr1)
                .IsUnicode(false);
                }

Any help would be appreciated.

Thanks!

Could you post the code of your `OnModelCreating` in DbContext? — dglozano, Mar 24 '21 at 07:13
@dglozano Yes, but i am looking some alternate and good solution to resolve this issue. Kind of schema independent. — Vipul Odhavani, Mar 25 '21 at 09:05
I don't think you will find an alternate solution for using a custom schema name, oracle database provider and automatic migrations, it's just not supported. You either use the default schema name, or turn off automatic migrations or change provider. The best viable "alternate and good solution" in this case is to turn off automatic migrations. — dglozano, Mar 25 '21 at 09:08
@dglozano Yes, if i turn off auto-migration and use code-based migration then again issue. Because code based migration or auto migration always create __MigrationHistory table. So, when code based migration execute then it's create __MigrationHistory table with some specific schema name i.e. PDBADMIN. which is still blocker for me.Also in our application multi db concept so when i want to create or connect SQL db then __MigrationHistory table with PDBADMIN schema only always. So i am looking some change for custom schema because it's specific requirement. — Vipul Odhavani, Mar 25 '21 at 09:13
I see... but those other problems that you mention seem to be unrelated to the original topic if this question. You might want to consider opening a different question to address that. Anyway, good luck with it :) — dglozano, Mar 25 '21 at 09:16
@dglozano I already open question for that. You can check here and any solutions so let me know. https://stackoverflow.com/q/66779829/7825850 — Vipul Odhavani, Mar 25 '21 at 09:18

dglozano · Answer 1 · 2021-03-24T07:57:15.600

You are changing your schema to PDBADMIN in this line modelBuilder.HasDefaultSchema("PDBADMIN");. Unfortunately, you can't use automatic migrations with the Oracle provider using a custom schema name.

From Oracle's documentation

Code First Automatic Migrations is limited to working with the dbo schema only. Due to this limitation it is recommended to use code-based migrations, that is, add explicit migrations through the Add-Migration command.

So you have to either use the default schema name dbo, or disable automatic migrations and use code based migrations.

In my personal opinion, even if you hadn't come across this particular problem with the Oracle provider, I would strongly recommend usindg code based migrations anyway.

It's also the official recommendation when working on a team:

You can intersperse automatic and code-based migrations but this is not recommended in team development scenarios. If you are part of a team of developers that use source control you should either use purely automatic migrations or purely code-based migrations. Given the limitations of automatic migrations we recommend using code-based migrations in team environments.

I agreed with the code-based migration with automatic migration set to false. But in my case i have multiple Oracle database added runtime so when i add migration with specific schema i.e. PDBADMIN and when i run with the same database then it will work. But i am going to add new database and run then it gives me error. Because it is considering migration history to PDBADMIN. But it should consider with our new database schema. So we want to set custom schema to all ready added migration script via C# code. — Vipul Odhavani, Mar 28 '21 at 11:40
@VipulOdhavani I don't fully understand your scenario, and I don't have experience working with EF Core and multi schema databases, but there are a couple of questions already asked that might guide you in the right direction https://stackoverflow.com/questions/46205602/ef-core-migrations-with-multiple-db-schemas But once again, that problem seems to be out of the scope of this question, so IMO that should be followed in a different issue and this one be closed :) — dglozano, Mar 28 '21 at 11:49

Entity Framework code first auto migration not working with Oracle database

1 Answers1