Chris Umbel

Using Entity Framework with Oracle

My post is relevant to VS 2008. Although I haven't tested it this article on Model-First entities with Oracle seems to cover the connectivity with 2010. Thanks to Selim Selcuk for tracking this down!

I've long thought that the Entity Framework hasn't gotten a fair shake. Sure, there's some room for improvement but for an out-of-the-box ORM that ships with the framework it's not too bad.

Oracle LogoOne 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  
    select "TEAMS_SEQ".nextval into :NEW.ID from dual; 

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  
    select "PLAYERS_SEQ".nextval into :NEW.ID from dual; 

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. Add entity model to project dialog

I'll generate the model from an existing database. Choose model contents dialog

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. Data source setup dialog

Now I'll set up the connection information. Connection properties dialog

Next I'll select the tables I created above to create the model from. Choose database objects dialog

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

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.



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";

    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";

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


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)

Resulting in:

Evgeni Malkin
Sidney Crosby


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.

Tue Dec 22 2009 21:30:00 GMT+0000 (UTC)

Follow Chris
RSS Feed