Using Entity Framework with Oracle

One thing has always troubled me, though. I was never sure what kind of traction it had with data sources other than SQL Server. Recently in a project circumstances conspired in such a fashion that it would be pretty darn handy to use EF to access data in Oracle. Turns out it that despite being a little rough around the edges it's very feasible.

The Provider

The first step is to download and install an Entity Framework provider for Oracle. There are several options out there but the Oracle Data Provider for Entity Framework on CodePlex seems to be the most complete that was free.

The Database

To demonstrate I'll set up a simple oracle express database to store hockey player data.

create table  "TEAMS" 
   (	"ID" number, 
	"NAME" varchar2(256), 
	"CITY" varchar2(256), 
	 constraint "TEAMS_PK" primary key ("ID") enable
   )
/

create trigger  "BI_TEAMS" 
  before insert on "TEAMS"               
  for each row  
begin   
    select "TEAMS_SEQ".nextval into :NEW.ID from dual; 
end; 

/
alter trigger  "BI_TEAMS" enable
/

create table  "PLAYERS" 
   (	"ID" number, 
	"FULLNAME" varchar2(256), 
	"JERSEYNUMBER" number, 
	"TEAM_ID" number, 
	 constraint "PLAYERS_PK" primary key ("ID") enable, 
	 constraint "PLAYERS_FK" foreign key ("TEAM_ID")
	  references  "TEAMS" ("ID") enable
   )
/

create trigger  "BI_PLAYERS" 
  before insert on "PLAYERS"               
  for each row  
begin   
    select "PLAYERS_SEQ".nextval into :NEW.ID from dual; 
end; 

/
alter trigger  "BI_PLAYERS" enable
/

The Plumbing

With the schema and prerequisite out of the way I can get right into adding an entity model to the project in the standard way.

I'll generate the model from an existing database.

Now to set up the data source. Because of the Oracle Provider that I installed above there's a new "Oracle Database" option in the Data Source list.

Now I'll set up the connection information.

Next I'll select the tables I created above to create the model from.

Ok, now I have a basic model, but I might as well clean up the naming by using pascal-case and reasonable pluralization.

If this were SQL Server I'd be done. Unfortunately there's a hidden problem. The ID fields that are generated from sequences won't be handled correctly. After saving the entities the ID's will be returned as 0. I'll fix this by manually hacking the SSDL (open your .edmx file in a text editor) with StoreGeneratedPattern="Identity" attributes on the ID fields (lines 6 and 16). Note that designer may rip that change out upon future modification.

While I suppose it's not absolutely necessary it might also be prudent to modify some type metadata such as changing "number"s to "int"s in your SSDL and "Decimal"s to "Int32"s in your CSDL where applicable. Frequently these don't auto-generate with the desired values especially with XE.

<EntityType Name="PLAYERS">
  <Key>
    <PropertyRef Name="ID" />
  </Key>
  <Property Name="ID" Type="int" Nullable="false" 
        StoreGeneratedPattern="Identity"/>
  <Property Name="FULLNAME" Type="varchar2" MaxLength="256" />
  <Property Name="JERSEYNUMBER" Type="int" />
  <Property Name="TEAM_ID" Type="int" />
</EntityType>
<EntityType Name="TEAMS">
  <Key>
    <PropertyRef Name="ID" />
  </Key>
  <Property Name="ID" Type="int" Nullable="false" 
        StoreGeneratedPattern="Identity"/>
  <Property Name="NAME" Type="varchar2" MaxLength="256" />
  <Property Name="CITY" Type="varchar2" MaxLength="256" />
</EntityType>

The Code

The plumbing work is over! Now I'll use the following C# to populate some data into my Oracle database.

using (HockeyEntities entites = new HockeyEntities())
{
    Team team = new Team();
    team.Name = "Penguins";
    team.City = "Pittsburgh";
    entites.AddToTeams(team);

    Player player = new Player();
    player.FullName = "Evgeni Malkin";
    player.JerseyNumber = 71;
    player.Team = team;

    player = new Player();
    player.FullName = "Sidney Crosby";
    player.JerseyNumber = 87;
    player.Team = team;

    team = new Team();
    team.Name = "Capitals";
    team.City = "Washington";
    entites.AddToTeams(team);

    player = new Player();
    player.FullName = "Alexander Ovechkin";
    player.JerseyNumber = 8;
    player.Team = team;

    entites.SaveChanges();
}

And I can query the data created above with:

using (HockeyEntities entites = new HockeyEntities())
{
    var players = from p in entites.Players
            where p.Team.City == "Pittsburgh"
            select p;

    foreach (Player p in players)
    {
        Console.WriteLine(p.FullName);
    }
}

Resulting in:

Evgeni Malkin
Sidney Crosby

Conclusion

Other that some minor tweaking to SSDL using EF to access Oracle isn't too bad. I assume that as time goes on issues like that will get worked out and the experience will be improve.