Using Entity Framework 6 Code First with Oracle 11g

Configuration

I have used ODP.NET provider for this, which can be easily found on Nuget. The main configuration change here is the EF provider and connection provider. Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices is the EF provider and Oracle.ManagedDataAccess.Client.OracleClientFactory is the Oracle connection provider; both providers need to registered. You can registered these either with configuration files, or by code itself. I have created class to registered them, as:

public class OracleDbConfiguration : DbConfiguration
{
	public BaseDbConfiguration()
	{
		this.SetProviderServices("Oracle.ManagedDataAccess.Client", EFOracleProviderServices.Instance);
		this.SetProviderFactory("Oracle.ManagedDataAccess.Client", new OracleClientFactory());
	}
}

Now you can use this class with the EF DbCotext class as:

[DbConfigurationType(typeof(OracleDbConfiguration))]
public class DataContext: DbContext
{
...
}

Oracle Table

Consider a simple Department table with 2 columns as

CREATE TABLE Departments
(
    ID integer PRIMARY KEY,
    Description varchar2(20)
);

EF Model

The model class for this table is:

public class Department
{
    public int Id { get; set; }
    public string Description { get; set; }

}

EF DbContext Implementation

The DataContext class defined above will now we modified as:

[DbConfigurationType(typeof(OracleDbConfiguration))]
public class DataContext: DbContext
{
	public BaseDataContext(string connectionString)
		:base(connectionString)
	{
		this.Configuration.AutoDetectChangesEnabled = false;
		this.Configuration.LazyLoadingEnabled = false;
		this.Configuration.ProxyCreationEnabled = false;
		this.Configuration.ValidateOnSaveEnabled = false;
	}

	protected override void OnModelCreating(DbModelBuilder modelBuilder)
	{
		modelBuilder.Types().Where(t => !t.IsAbstract).Configure(t => t.ToTable(t.ClrType.Name));
		modelBuilder.HasDefaultSchema("HR");

		modelBuilder.Configurations.Add(new DeptMap());
	   
		base.OnModelCreating(modelBuilder);
	}
}

NOTE: I have used UPPERCASE for schema, table and column names. While using EF with Oracle always use UPPERCASE, else the database object will not found during execution.

Connection String

Now define the connection string for Oracle 11g in the configuration file as:

<connectionStrings>
 <add name="Oracle.ConnectionString" connectionString="DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE)));USER ID=HR;PASSWORD=password"  providerName="Oracle.ManagedDataAccess.Client" />
</connectionStrings>

Usages

Now the above DataContext class can be used as:

using (var context = new DataContext("Oracle.ConnectionString")
{
	var dbSet = context.Set();
	return dbSet.ToList();
}

ID Column

EF use the ID property as IDENTITY column, Oracle support IDENTITY column directly from 12c, so in 11g, we will need to use as alternative way called Sequence. So following SQL statements also need to be used to make this code work:

CREATE SEQUENCE dept_seq;

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;