Sample for Entity Framework 6 + Code First + Oracle 12c

Question

I have a problem with a Visual Studio solution using Entity Framework 6 + Code First + Oracle 12c. I'm not sure it is properly configured, or if I missed something simple.

I tried to look for a sample project as a start, but was not able to find - google search, stackoverflow etc.

Is there a minimalistic sample project somewhere, which tries to create the database when runs?

Update: Just to make sure, I'm not asking anyone to create a sample for me. Before I'll do it, I want to make sure there is really no existing sample (which is strange for me, but very well might be the case).

Rech

If there is such a thing, then Google is your best option. StackOverflow isn't a place to ask for tutorials or other off-site resources I'm afraid. – Rania

That is the reason of my question, I googled for a sample and was able to find only technical articles with bits and pieces how to set up, and discussions and older versions etc., but not a simple sample download. (If there is none, and I'll succeed to make it work, then I will upload it.) – Rech

Answer 1

I managed to create a working sample. Found some (not so) documented strange behaviors resulting in run time errors along the way.

Here is the full sample source: https://drive.google.com/file/d/0By3P-kPOnpiGRnc0OG5ZTDl6eGs/view?usp=sharing&resourcekey=0-ecT1EU81wJOQWokVDr_r9w

I created the sample with Visual Studio 2013. Used nuget to pull

• EntityFramework 6.1.3
• Official Oracle ODP.NET, Managed Entity Framework Driver 12.1.021

The important parts are

• Program.cs
• Context.cs
• TestEntity.cs
• App.config

I omit

• packages.config
• AssemblyInfo.cs
• csproj, sln files I also omit namespaces.

using System.Data.Entity;

public class Program
    {
        private static void Main(string[] args)
        {
            string connStr =
                "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=***server***)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=***SERVICE***)));Persist Security Info=True;User ID=***User***;Password=***password***";

            Database.SetInitializer(new DropCreateDatabaseIfModelChanges<Context>());
            //Database.SetInitializer(new DropCreateDatabaseAlways<Context>());

            Context context = new Context(connStr);

            TestEntity te = new TestEntity();
            te.Id = 1;
            te.Name = "Test1";

            context.TestEntities.Add(te);

            context.SaveChanges();
        }
    }

---

using System.Data.Entity;

public class Context : DbContext
{
    public Context(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {
    }

    public virtual DbSet<TestEntity> TestEntities { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("OTS_TEST_EF");

        modelBuilder.Entity<TestEntity>()
            .Property(e => e.Id)
            .HasPrecision(9, 2);

        base.OnModelCreating(modelBuilder);
    }
}

---

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("TestEntity")]
public class TestEntity
{
    [Column(TypeName = "number")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public decimal Id { get; set; }

    [StringLength(100)]
    public string Name { get; set; }
}

---

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework"
      type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
      requirePermission="false"/>
    <section name="oracle.manageddataaccess.client"
      type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
  </startup>
  <entityFramework>
    <!--<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>-->
    <providers>
      <!--<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>-->
      <provider invariantName="Oracle.ManagedDataAccess.Client"
        type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="Oracle.ManagedDataAccess.Client"/>
      <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"
        type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
    </DbProviderFactories>
  </system.data>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <publisherPolicy apply="no"/>
        <assemblyIdentity name="Oracle.ManagedDataAccess" publicKeyToken="89b483f429c47342" culture="neutral"/>
        <bindingRedirect oldVersion="4.121.0.0 - 4.65535.65535.65535" newVersion="4.121.2.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <!--<oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        <dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=INFOTEST))) "/>
      </dataSources>
    </version>
  </oracle.manageddataaccess.client>-->
  <!--<connectionStrings>
    <add name="OracleDbContext" providerName="Oracle.ManagedDataAccess.Client"
      connectionString="Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = INFOTEST)));Persist Security Info=True;User ID=user;Password=password"/>
  </connectionStrings>-->
</configuration>

---

The strange thing I found on the way: Adding any of the following TypeNames will result in "Sequence contains no matching element" error.

    /*[Column(TypeName = "numeric")]*/
    /*[Column(TypeName = "number(18,0)")]*/
    /*[Column(TypeName = "number(18,2)")]*/

Indicating precision with scale 0

modelBuilder.Entity<TestEntity>().Property(e => e.Id).HasPrecision(9, 0);

Will result in

Schema specified is not valid. Errors:

(7,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.Decimal[Nullable=False,DefaultValue=,Precision=9,Scale=0]' of member 'Id' in type 'EF6_Oracle12c_CF.TestEntity' is not compatible with 'OracleEFProvider.number

Omitting the

modelBuilder.HasDefaultSchema("OTS_TEST_EF");

line will result in

ORA-01918: user does not exists

It is also happened that I got

ORA-00955: name is already being used by existing object

or

Model compatibility cannot be checked because the database does not contain model metadata. Model compatibility can only be checked for databases created using Code First or Code First Migrations.

I managed to overcome those by enabling the

Database.SetInitializer(new DropCreateDatabaseAlways<Context>());

line, instead of the DropCreateDatabaseIfModelChanges mode.

Rech

Sigh, so what about when you are deploying and the server has a different schema name, everything breaks because instead of just defining things in the connection string, we need to also change the HasDefaultSchema line – Dextroglucose

This is a sample project to demonstrate how to make it work, not production code. – Rech

Answer 2

This has the a good sample if you are still interested . It is available on 11 g .

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/CodeFirst/index.html

Chariot

Answer 3

Here's a link to a sample from Oracle on using EF Code First, Migration and Visual Studio.

Oracle Learning Library on EF Code First and Code First Migration

I'm actually almost finishing up a project that uses VS, 12c and EF and the link was a good starting point. There was no particular issue about 12c that I saw.

Sharpfreeze

Answer 4

I have a test project on github that I used to try out examples of EF6 migrations on Oracle. The code that works for me (to programatically execute all pending migrations) is here. My use case is likely common - I need to be able to deploy my application to various environments and data centres and have it do "the right thing" to work with the environment's copy of my application database.

The important bit is

//Arrange

Configuration config = new Configuration();
DbMigrator migrator = new DbMigrator(config);

Console.WriteLine("Migrating...");
foreach (string s in migrator.GetPendingMigrations())
{
    //Act
    Console.WriteLine("Applying migration {0}", s);
    Action act = () => migrator.Update(s);

    //Assert
    act.ShouldNotThrow();
}

Jethro

Answer 5

I spent days fixing this issue... Finally I solved it:
The table did not exist. I checked many times and refreshed, but the problem was not in the table itself, it was with the sequence. Every table in Oracle creates a sequence object to increase the id. So, if you delete the table make sure to drop the sequence as well, otherwise when you migrate again, it will give you ORA-00955: name is already used by an existing object.

So, the real problem is in the sequence, not in the table. But you cannot create new sequence because it already exists. It is not deleted when you delete the table, it should be deleted manually.
I hope this will help someone.

Neilson