CODEWITHSUNDEEP
c#oracle-databaseentity-framework-coreef-code-firstentity-framework-migrations
sinsedrix
sinsedrix

Reputation: 4775

Database creation with EF Core + Code First + migrations

In the case of an Entity Framework Core app with code first and migrations, I don't manage to have the database created if it's not existing.

My first call is:

using (MyDbContext context = new MyDbContext())
{
    context.Database.Migrate();
}

It runs overridden methods:

// 1
protected override void OnConfiguring(DbContextOptionsBuilder optionBuilder)
{
    optionBuilder.UseOracle($"ENLIST=dynamic;USER ID={UserId};POOLING=True;CONNECTION TIMEOUT=15;PASSWORD={Pwd};DATA SOURCE=localhost:1521/xe;CONNECTION LIFETIME=0");
}

// 2
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema(UserId);
}

And throws the following exception:

System.NotSupportedException : 'Required user does not exists or invalid username/password provided'

I was expecting the database and the user would be created in the HasDefaultSchema call. What am I missing in this case?

Upvotes: 2

Views: 2679

Answers (3)

Sagar Vaja
Sagar Vaja

Reputation: 174

Seems something wrong with the username & password provided. Please cross-check the username & password with the oracle instance you are using.

Upvotes: 0

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

Unfortunately, the UseOracle method in optionBuilder does not support the creation of the schema when the schema does not exist.

The reason is because there are specific differences between what a schema is in Sql Server or Postgres, with respect to Oracle.

EnsureSchemaOperation Class

A MigrationOperation for ensuring that a schema exists. That is, the schema will be created if and only if it does not already exist.

[System.Diagnostics.DebuggerDisplay("CREATE SCHEMA {Name}")]
public class EnsureSchemaOperation : 
Microsoft.EntityFrameworkCore.Migrations.Operations.MigrationOperation

The method uses the syntax create schema which can be applied to some database engines, but not in Oracle. The reason is the following:

The CREATE SCHEMA statement does NOT actually create a schema in Oracle. The CREATE SCHEMA statement is used only to create objects (ie: tables, views) in your schema in a single SQL statement, instead of having to issue individual CREATE TABLE and CREATE VIEW statements. You can control them as a single unit if you use the CREATE SCHEMA statement.

Oracle schemas are like My Documents folders in the Windows OS. A user can grant permissions to other users to see things in their schema but an Oracle schema is essentially a user's workspace.

MS SQL Server's schemas are namespaces. While you can have Accounting and Marketing schemas, they are not tightly-coupled to individual users. Objects in an Accounting schema contain accounting information and objects in the Marketing schema have marketing information.

Oracle schemas are tightly-coupled to users and MS SQL Server schemas are primarily for classification.

In Oracle, an schema is always an user. You can create two different tables with the same name, belonging to different users/schemas. In SQL Server, schema and user are separate things. The users are only used to log in and define permissions.

I believe Microsoft did not implement the CREATE USER xxx in the corresponding method of the affected class.

Upvotes: 6

Rodrigo Chapeta
Rodrigo Chapeta

Reputation: 119

Have you checked if the migrations include the creations of the schema?

From my experience some database connectors include the automatic creation of schema by default and some don't, for example mysql and ms do but same code for MariaDB requires for the schema to be created before.

Upvotes: 1

Related Questions