Application Programming Interface

DatabaseFacade Class

ODP.NET EF Core contains additional extension methods and changes to method default behavior for the DatabaseFacade class.

DatabaseFacade.IsOracle

This method returns true if ODP.NET is the currently used database provider.

// C#
public static bool IsOracle()

Returns a bool value.

Note:

The provider is only known after the provider is set in the DbContext.

DatabaseFacade.EnsureCreated

This property ensures that the tables for the schema defined in the current context exists.

Declaration

// C#
public static bool EnsureCreated()

Return Value

A bool

Remarks

If any of the tables in the schema exist, then no action is taken. Pre-existing tables are not checked for compatibility with the EF Core context model.

If none of the tables in the schema exist, then all the defined context model objects are created.

If the user/schema specified in the connection string does not exist, then an error is thrown and no action is taken to create the user/schema. The administrator must create the user/schema and assign the appropriate privileges prior to using this method.

The return value is true if all the objects defined in the context are created. It is false if any of the tables for the schema already exist.

Exception

NotSupportedException() is thrown when a non-existent user/schema is specified in the connection string.

Type: NotSupportedException()

Message: Required user does not exist or invalid user name/password provided

DatabaseFacade.EnsureCreated(string[])

This property ensures that the tables for the specified schemas in the string array exist.

Declaration

// C#
public static bool EnsureCreated (string[] schemas)

Parameters

schemas – List of schemas to check for the EF Core context’s pre-existing tables. Schema names are case-sensitive.

Return Value

A bool

Remarks

If any of the tables in the string array schema list exists, then no action is taken. Pre-existing tables are not checked for compatibility with the EF Core context model.

If none of the tables in the string array schema list exist, then all the defined context model objects are created.

If the user/schema specified in the connection string does not exist, then an error is thrown and no action is taken to create the user/schema. The administrator must create the user/schema and assign the appropriate privileges prior to using this method.

If the schemas passed to this method does not include the user/schema specified in the connection string, then that schema is implicitly added to the array of schemas.

If the array of schemas is null or length zero, then the DatabaseFacade.EnsureCreated() API is called.

The return value is true if all the objects defined in the context are created. It is false if any of the tables for the schema already exist.

Exception

NotSupportedException() is thrown when a non-existent user/schema is specified in the connection string.

Type: NotSupportedException()

Message: Required user does not exist or invalid user name/password provided

Sample Code

using (var db = DbContext())
{
    db.Database.EnsureCreated(new string[]{"SCOTT", "HR", "EFUser"});
}

DatabaseFacade.EnsureDeleted

This property ensures that all the schema user's created objects are deleted.

Declaration

// C#
public static bool EnsureDeleted()

Return Value

A bool

Remarks

If none of the EF Core context model objects exist, no action is taken. If any of the objects exist, then all the user/schema objects are dropped, except for Oracle data dictionary objects.

Warning: The dropped objects include schema objects outside of the EF Core context model, as long as the user/schema has privileges to drop those objects.

If the schema defined in the current context does not exist, then no action is taken.

The return value is true if an attempt is made to drop all user created objects related to the schema in the current context. It is false if the schema specified in the connection string does not exist.

DatabaseFacade.EnsureDeleted(string[])

This property ensures that the user/schema objects for the specified schemas in the string array are deleted.

Declaration

// C#
public static bool EnsureCreated (string[] schemas)

Parameters

schemas – List of schemas to drop user generated objects. Schema names are case-sensitive.

Return Value

A bool

Remarks

If any of the objects exist, then all the user/schema objects are dropped, except for Oracle data dictionary objects. If none of the EF Core context model objects exist, no action is taken. If the schemas passed to this method does not include the user/schema specified in the connection string, then that schema is implicitly added to the array of schemas.

Warning: The dropped objects include schema objects outside of the EF Core context model, as long as the user/schema has privileges to drop those objects.

If the specified schemas do not exist, then no action is taken.

The return value is true if an attempt is made to drop all user created objects that the user has privilege to in the specified schemas. It is false if the schema specified in the connection string does not exist.

Sample Code

using (var db = DbContext())
{
    db.Database.EnsureDeleted(new string[]{"SCOTT", "HR", "EFUser"});
}

DbContextOptionsBuilder Class

ODP.NET EF Core contains additional extension methods and changes to method default behavior for the DbContextOptionsBuilder class.

DbContextOptionsBuilder.UseOracle

This extension method sets the provider and database connection configuration to connect to Oracle Database. Developers can set any connection string attributes that are available in ODP.NET Core. The available method overloads that can be called are as follows:

```
UseOracle(string connectionString)
```

UseOracle(string connectionString, Action<OracleDbContextOptionsBuilder> oracleOptionsAction = null)

UseOracle(DbConnection connection, Action<OracleDbContextOptionsBuilder> oracleOptionsAction = null)

DbContextOptionsBuilder<TContext> UseOracle<TContext>(string connectionString, Action<OracleDbContextOptionsBuilder> oracleOptionsAction = null)

DbContextOptionsBuilder<TContext> UseOracle<TContext>(DbConnection connection,Action<OracleDbContextOptionsBuilder> oracleOptionsAction = null)

UseOracle(DbContextOptionsBuilder, Action<OracleDbContextOptionsBuilder> oracleOptionsAction = null)

UseOracle(string connectionString)

This extension method sets the provider and database connection configuration. Developers can set any connection string attributes that are available in ODP.NET Core.

Declaration

// C#
optionsBuilder.UseOracle(@"User Id=blog;Password=<password>;Data Source=pdborcl;");

UseOracle(DbContextOptionsBuilder, Action<OracleDbContextOptionsBuilder> oracleOptionsAction = null)

The following extension configures the EF Core context to connect to an Oracle database without initially setting any DbConnection nor connection string. The DbConnection or connection string must be set before the DbContext attempts to connect to a database. To set the connection using, use RelationalDatabaseFacadeExtensions.SetDbConnection or RelationalDatabaseFacadeExtensions.SetConnectionString.

Declaration

// C#
public static DbContextOptionsBuilder UseOracle(this DbContextOptionsBuilder,  Action<OracleDbContextOptionsBuilder>)

Parameters

DbContextOptionsBuilder - The builder being used to configure the context
Action<OracleDbContextOptionsBuilder> - An optional action to allow additional Oracle specific configuration

Return Value

The options builder so that further configuration can be chained.

Sample Code

// C# - Setting up the DB context
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseOracle(); 
// Using the DB context
using (var context = new DbContext()) 
{ 
context.Database.SetDbConnection(new OracleConnection(<connection string>)); 
}

Note:

optionsBuilder is of type DbContextOptionsBuilder.
Do not use Oracle built-in accounts to store Entity Framework Migrations.

UseOracleSQLCompatibility(string version)

This extension method specifies the database version generated SQL should be compatible with.

This method accepts either a value of "11" or "12" (default). By default, generated SQL is compatible with database version 12 and higher. Customers using Oracle Database version 11.2 should set UseOracleSQLCompatibility("11").

// C#
optionsBuilder.UseOracle("User Id=hr;Password=<password>;Data Source = inst1", b =>
b.UseOracleSQLCompatibility("11"));

Note:

optionsBuilder is of type DbContextOptionsBuilder.

When UseOracleSQLCompatibility is set to “11”, by convention, it will always use sequences and triggers no matter the UseIdentityColumn setting.

IQueryingEnumerable Interface

This section includes:

IQueryingEnumerable.ToQueryString Extension Method

IQueryingEnumerable.ToQueryString Extension Method

A string representation of the Oracle SQL query used. This extension method will generate SQL that can be run in Oracle Database and Oracle Autonomous Database.

When UseOracleSQLCompatibility is set to 11, additional comment lines will be generated, such as:

-- remove when executing from OracleCommand – Start
-- remove when executing from OracleCommand – End

If you execute the method's generated SQL in an OracleCommand object, remove any code between the comment lines. For example, below is a code block that may be generated. Any code between these two lines should be deleted when executed in an OracleCommand. The lines can be removed programmatically. Here's an example of what the generated ToQueryString code would look like:

-- remove when executing from OracleCommand - Start
-- if executing this script using OracleCommand, please remove lines of code as specified and explicitly bind :result_cursor from the user application.
variable result_cursor refcursor;
-- remove when executing from OracleCommand - End
DECLARE
… 
BEGIN
…
END;
-- remove when executing from OracleCommand - Start
/
print result_cursor;
-- remove when executing from OracleCommand - End

To execute the generated SQL programmatically, developers can adapt the following C# pseudo-code for their specific requirements. The pseudo-code demonstrates how to generate the script using ToQueryString() on a sample LINQ query, and then how to execute the script with an OracleCommand, depending on the database version backing the application.

using System;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using Microsoft.EntityFrameworkCore;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

class ToQueryStringPseudoCode
{
    static void Main(string[] args)
    {
        const string START_TAG = "-- remove when executing from OracleCommand - Start";
        const string END_TAG = "-- remove when executing from OracleCommand - End";

        using (ModelContext db = new ModelContext())
        {
            //sample LINQ to convert query string from
            string name = "Name";
            var query = db.Set<Instructor>().Where(c => c.Name == name);
            string sqltext = query.ToQueryString();

            //processing the generated script and removing the code between the comment tags if present.
            string newsqltext = TrimBetweenTags(START_TAG, END_TAG, sqltext);

            //’newsqltext’ can be used directly with OracleCommand.
            OracleConnection con = new OracleConnection("<Connection String>")
            con.Open();
            OracleCommand cmd = con.CreateCommand();
            cmd.CommandText = newsqltext;
            OracleDataReader reader;

            //in case of DB 11.2, explicitly bind the parameter 'result_cursor' to the OracleCommand. 
            if (<Database Version is 11.2>)
           {
                cmd.BindByName = true;
                OracleParameter outRefPrm = cmd.Parameters.Add("result_cursor", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
                cmd.ExecuteNonQuery();
                reader = ((OracleRefCursor)outRefPrm.Value).GetDataReader();
            }
	// No binding required for 12c+ DBs.
            else
            {
                reader = cmd.ExecuteReader();
            }

            //verifying the result set.
            while (reader.Read())
            {
                Console.WriteLine($"{reader[0]}, {reader[1]}, {reader[2]}, {reader[3]}");
            }
            con.Close();
        }
    }

    public static string TrimBetweenTags(string startTag, string endTag, string str)
    {
        if (String.IsNullOrEmpty(str))
            return null;

        Regex x = new Regex("(" + startTag + ")([\\s\\S]*?)(" + endTag + ")");
        return x.Replace(str, "");
    }
}

MigrationBuilder Class

MigrationBuilder.IsOracle Extension Method

Returns true if the MigrationBuilder object uses ODP.NET as its database provider.

Declaration

public static bool IsOracle(this MigrationBuilder)

Parameters

MigrationBuilder object

Return Value

A bool.

Sample Code

var migrationBuilder = new MigrationBuilder("Oracle.EntityFrameworkCore"); 
bool b_oracle = migrationBuilder.IsOracle();  //returns true for ODP.NET

ModelBuilder Class

ODP.NET EF Core contains additional extension methods and changes to method default behavior for the ModelBuilder class.

ModelBuilder UseIdentityColumn() and UseOracleIdentityColumn()

ModelBuilder UseIdentityColumn() and UseOracleIdentityColumn()

This extension method specifies whether the column is an identity column or have it associated with a sequence and a trigger to have a server generated column value, depending on the value passed to UseOracleSQLCompatibility(). By default, columns do not have this extension method enabled.

// C #
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>().Property(p => p.Id).UseIdentityColumn();
}

For EF Core 3.1 and higher versions, use UseIdentityColumn. For EF Core 3.1 Core and lower versions, you can use UseOracleIdentityColumn. Starting with EF Core 5, UseOracleIdentityColumn is no longer available to use with Oracle EF Core. These two methods have identical functionality. For the remainder of this section, the term UseIdentityColumn is synonymous with UseOracleIdentityColumn.

Using Identity Columns, Sequences, and Triggers

Oracle databases allow only one identity column to be set per table. And that column is generally used as the primary key. As primary keys must be unique, by EF Core convention, its column will be an identity column or sequence/trigger column.

For non-primary key columns, the below matrix indicates column behavior based on the UseOracleSQLCompatibility and UseIdentityColumn values and the Oracle database version.

For UseOacleSQLCompatibility=11:

Database Version	`UseOacleSQLCompatibility=11` `UseIdentityColumn=Enabled`	`UseOracleSQLCompatibility=11` `UseIdentityColumn=Disabled`
11.2	Uses sequences and triggers	No operation
12 and later	Uses sequences and triggers	No operation

For UseOracleSQLCompatibility=12:

Database Version	`UseOracleSQLCompatibility=12` `UseIdentityColumn=Enabled`	`UseOracleSQLCompatibility=12` `UseIdentityColumn=Disabled` (Default)
11.2	Not allowed/Error	No operation
12 and later	Uses identity	No operation

In general, developers would employ UseOacleSQLCompatibility and UseIdentityColumn for the purposes of backward compatibility to earlier Oracle database versions and/or keep behavior consistent with earlier versions.