Application Programming Interface
ODP.NET EF Core supports standard EF Core application programming interfaces. The provider contains additional extension methods specific to the provider.
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 typeDbContextOptionsBuilder
. -
Do not use Oracle built-in accounts to store Entity Framework Migrations.
UseOracleSQLCompatibility(enum version)
This extension method specifies the database version generated SQL and functionality to be compatible with.
With Oracle EF Core 21.12.1 and higher, UseOracleSQLCompatibility
extension method now takes an enumeration type as an argument rather than a string value.
The valid enum values that can be used are:
-
OracleSQLCompatibility.DatabaseVersion19
-
OracleSQLCompatibility.DatabaseVersion21
-
OracleSQLCompatibility.DatabaseVersion23
By default, the SQL compatibility value will be OracleSQLCompatibility.DatabaseVersion21
. So generated SQL will be compatible with database version 21. If using Oracle EF Core 8 with OracleSQLCompatibility.DatabaseVersion21
, JSON database columns are supported. Otherwise, aggregate types will map to NCLOB
columns in the database instead of JSON.
Using OracleSQLCompatibility.DatabaseVersion23
, the .NET bool type will map to Oracle BOOLEAN
column type rather than NUMBER(1)
by default. To map .NET bools to NUMBER(1)
by default, use OracleSQLCompatibility.DatabaseVersion21
or lower.
The default enumeration value matches the ODP.NET version. For ODP.NET 21c, the default is OracleSQLCompatibility.DatabaseVersion21
.
The following example shows how to set UseOracleSQLCompatibility
.
// C# optionsBuilder.UseOracle("User Id=hr;Password=<password>;Data Source = inst1", b => b.UseOracleSQLCompatibility(OracleSQLCompatibility.DatabaseVersion19));
Note:
-
optionsBuilder
is of typeDbContextOptionsBuilder
. -
This extension method exists for EF Core 8 only.
IQueryingEnumerable Interface
This section includes:
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.
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.Data; using Microsoft.EntityFrameworkCore; using Oracle.ManagedDataAccess.Client; class ToQueryStringPseudoCode { static void Main(string[] args) { 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(); //’sqltext’ can be used directly with OracleCommand OracleConnection con = new OracleConnection("<Connection String>"); con.Open(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = sqltext; OracleDataReader reader; reader = cmd.ExecuteReader(); //verifying the result set while (reader.Read()) { Console.WriteLine($"{reader[0]}, {reader[1]}, {reader[2]}, {reader[3]}"); } con.Close(); } } }
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()
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
.
OracleSQLCompatibility Enumeration
The OracleSQLCompatibility
enum is used to specify database version the generated SQL should be compatible with. It is also used to determine the type mappings for .NET types.
There are currently 3 possible values of SQLCompatibility that can be set in the application using UseOracleSQLCompatibility()
API.
-
OracleSQLCompatibility.DatabaseVersion19
-
OracleSQLCompatibility.DatabaseVersion21
-
OracleSQLCompatibility.DatabaseVersion23
If using Oracle EF Core 8 with OracleSQLCompatibility.DatabaseVersion21
, JSON database columns are supported. Otherwise, aggregate types will map to NCLOB columns in the database instead of JSON.
Using OracleSQLCompatibility.DatabaseVersion23
, the .NET bool type will map to Oracle BOOLEAN
column type rather than NUMBER(1)
by default. To map .NET bools to NUMBER(1)
by default, use OracleSQLCompatibility.DatabaseVersion21
or lower.
The default enumeration value matches the ODP.NET version. For ODP.NET 21c, the default is OracleSQLCompatibility.DatabaseVersion21
.
Database Functions (EF.Functions) for Vectors
EF Core providers can have custom C# methods that invoke database functions. These methods can be used in LINQ as EF.Functions
extension methods. ODP.NET provides a number of them for vector data management. These vector methods can be used in EF Core 9 and higher with Oracle EF Core version 23.6 or higher. Vector features require connecting to Oracle Database 23ai or higher and setting OracleSQLCompatibility
to be DatabaseVersion23
or higher.
VectorDistance
This method calculates the distance between the two given vectors.
Declaration
// C# - 2 overloaded methods public static double VectorDistance(object expression1, object expression2) public static double VectorDistance(object expression1, object expression2, string metric)
Parameters
expression1
and expression2
represent the two vectors for which the distance is to be calculated.
metric
is a string which indicates the metric used to calculate the distance.
Return Value
System.Double
that is the distance between the two vectors in the metric format
Exception
-
If any argument value is null, then an
ArgumentNullException
is thrown. -
If any argument value is empty string, then an
ArgumentException
is thrown. -
If any invalid argument value is passed, then an
OracleException
is thrown with the database error message.
Remarks
This method uses the Oracle VECTOR_DISTANCE
database function.
Both vector parameters must have the same number of dimensions and vector format. This is a VECTOR_DISTANCE
function requirement. They can be column expressions, variables, or inline strings.
The possible distance metric values are:
-
Cosine (Default)
-
Dot
-
Euclidean
-
Euclidean_Squared
-
Hamming
-
Jaccard
-
Manhattan
If you want to use the metric parameter as a variable, then you must enclose it in the EF.Constant()
method because the metric argument in the VECTOR_DISTANCE
database function is not a string, but a metric expression.
Sample Code
var query1 = (from t in ctx.AIData where t.Id == 10 select EF.Functions.VectorDistance(t.V, vector, "Cosine")).ToList(); var distanceType= "Cosine"; var query2 = (from t in ctx.AIData where t.Id == 10 select EF.Functions.VectorDistance(t.V, vector, EF.Constant(distanceType))).ToList();
VectorEmbedding
This method returns the input string's vector embedding using the specified model.
Declaration
// C# public static object VectorEmbedding(string modelName, object expression)
Parameters
modelName
is the importing embedding model to generate the vector embedding from.
expression
is the value from which to generate the embedding.
Return Value
System.Object
that is the provided expression's embedding in a vector format.
Exception
-
If any argument value is null, then an
ArgumentNullException
is thrown. -
If any argument value is empty string, then an
ArgumentException
is thrown. -
If any invalid argument value is passed, then an
OracleException
is thrown with the database error message.
Remarks
This method uses the Oracle VECTOR_EMBEDDING
database function.
The modelName
should be the same as what is in the database. The model needs to be loaded in the database prior to using this EF Core method.
modelName
can be an inline string or variable. If it is a variable, enclose it in the EF.Constant()
method to prevent parameterization. modelName
is an expression, not a string in the VECTOR_EMBEDDING
database function.
The expression argument can be a column, variable, or inline string.
Sample Code
string keyword = “cars”; var query1 = (from t in ctx.AIData select EF.Functions.VectorEmbedding("doc_model", keyword)).Take(1).ToList(); var modelName = "doc_model" var query2 = (from t in ctx.AIData select EF.Functions.VectorEmbedding(EF.Constant(modelName), keyword)).Take(1).ToList();
ToVector
This method converts a vector from one format to another format.
Declaration
// C# - 3 overloaded methods public static object ToVector(object expression) public static object ToVector(object expression, long dimensions) public static object ToVector(object expression, long dimensions, string format)
Parameters
expression
is the vector to be converted to a different vector format.
dimensions
is a numeric value for the number of dimensions for the new vector.
format
is the vector numeric format to convert the vector to.
Return Value
System.Object
that is the in the specified vector numeric format.
Exception
-
If any argument value is null, then an
ArgumentNullException
is thrown. -
If any argument value is empty string, then an
ArgumentException
is thrown. -
If any invalid argument value is passed, then an
OracleException
is thrown with the database error message.
Remarks
This method uses the Oracle TO_VECTOR
database function.
expression
could be a column, variable, or inline string in vector format.
The number of dimensions can alternatively be specified as zero (0)
. In this case, the dimension number is determined by expression.
The possible vector format values are:
-
Binary
-
Int8
-
Float32
-
Float64
-
*
This is the target vector numeric format to convert the vector to. If format value is not specified or is an asterisk (*), the format used is FLOAT32
.
To use dimensions or format as variables, enclose them in the EF.Constant()
method.
See Also: