Oracle Support for .NET 6 and Entity Framework Core 6

[frankyjquintero]

Is your feature request related to a problem? Please describe. Establish a support for Oracle in the route design as soon as the version compatible with .Net 6 is released by oracle

Describe the solution you'd like

• Add Oracle support

• Generation of migrations with Oracle support

  Describe alternatives you've considered none

  Additional context

https://medium.com/oracledevs/oracle-support-for-net-6-and-entity-framework-core-6-statement-of-direction-cfa5d1c59ff8

https://www.nuget.org/packages/Oracle.EntityFrameworkCore/

[frankyjquintero]

https://www.nuget.org/packages/Oracle.EntityFrameworkCore/6.21.4

[frankyjquintero]

@iammukeshm

[frankyjquintero]

https://github.com/fullstackhero/dotnet-webapi-boilerplate/blob/eada489cb5fef99e67f9717759a4d02029b0ed8e/src/Infrastructure/Persistence/ModelBuilderExtensions.cs

https://github.com/fullstackhero/dotnet-webapi-boilerplate/blob/eada489cb5fef99e67f9717759a4d02029b0ed8e/src/Infrastructure/Persistence/ModelBuilderExtensions.cs#L16

https://github.com/fullstackhero/dotnet-webapi-boilerplate/blob/eada489cb5fef99e67f9717759a4d02029b0ed8e/src/Infrastructure/Persistence/ModelBuilderExtensions.cs#L21

https://github.com/fullstackhero/dotnet-webapi-boilerplate/blob/eada489cb5fef99e67f9717759a4d02029b0ed8e/src/Infrastructure/Persistence/ModelBuilderExtensions.cs#L27

https://github.com/fullstackhero/dotnet-webapi-boilerplate/blob/eada489cb5fef99e67f9717759a4d02029b0ed8e/src/Infrastructure/Persistence/ModelBuilderExtensions.cs#L32

https://github.com/fullstackhero/dotnet-webapi-boilerplate/blob/eada489cb5fef99e67f9717759a4d02029b0ed8e/src/Infrastructure/Persistence/ModelBuilderExtensions.cs#L37

https://github.com/fullstackhero/dotnet-webapi-boilerplate/blob/eada489cb5fef99e67f9717759a4d02029b0ed8e/src/Infrastructure/Persistence/ModelBuilderExtensions.cs#L42

https://github.com/fullstackhero/dotnet-webapi-boilerplate/blob/eada489cb5fef99e67f9717759a4d02029b0ed8e/src/Infrastructure/Persistence/ModelBuilderExtensions.cs#L46

For Oracle migrations the schema will be totally different due to its behavior (Schema is user), in addition to having to be refactored and replaced by capital letters because Oracle.EntityFrameworkCore when performing the validation will look for the user under query to the engine users table .

Based on this implementation, a user called IDENTITY should be created and later the user "FULLSTACK" that will be used for the standard tables.

[frankyjquintero]

The previous change will force to create new migrations for the other database providers (or modify the existing ones)

[frankyjquintero]

I leave a repository link where the migrations for Oracle are initially implemented:

https://github.com/frankyjquintero/dotnet-webapi-boilerplate

https://github.com/frankyjquintero/dotnet-webapi-boilerplate/commit/6ea7b43da069563ec51f6af462e673880d69283e

Steps to verify migrations running perfectly:

• Install Docker

• register and pull image of Oracle:

https://hub.docker.com/_/oracle-database-enterprise-edition

• run container oracle docker run -d -it --name oracle -p 49154:1521 store/oracle/database-enterprise:12.2.0.1
• connect using default sys credentials

by default it has the following pdb

ORCLCDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 49154))
    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLCDB.localdomain)))

ORCLPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 49154))
    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1.localdomain)))

sqlplus sys /Oradoc_db1@ORCLCDB as sysdba

or

• run demo script to create required users (demo only, manage proper permissions with DBA):

create user Identity identified by password123;
grant connect to Identity;

grant all privileges to Identity identified by password123;

create user fullstack identified by password123;
grant connect to fullstack;

grant all privileges to fullstack identified by password123;

• change credentials:

{
  "DatabaseSettings": {
    "DBProvider": "oracle",
    "ConnectionString": "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=49154))(CONNECT_DATA =(SERVER=DEDICATED)(SERVICE_NAME=ORCLPDB1.localdomain)));User Id=fullstack;Password=password123"
  }
}

• Generate migrations

dotnet ef migrations add initial --project .././Migrators/Migrators.Oracle/ --context TenantManagementDbContext -o Migrations / Root
dotnet ef migrations add initial --project .././Migrators/Migrators.Oracle/ --context ApplicationDbContext -o Migrations / Application

• Run app

[razeenkhan]

When i dotnet run the WebAPI and when Applying Root Migrations, i am getting this error Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01435: user does not exist.......

[CanMehmetK]

Hi, Oracle is a bit different world ... In that world this is a generic errors, this one of them.. Check documentation please. And good luck with locks

https://support.oracle.com/knowledge/Oracle%20Cloud/2718606_1.html