In the Microsoft Entity Framework (EF), the GUI for the Entity Model is called the Entity Designer.  When you create an Entity Model, the model itself is stored in an XML file named model.EDMX.

The EDMX file is: 

a) an XML representation of the Entities and Relationships in your Database (the Storage Model, or SSDL), your application (the Conceptual Model, or CSDL), and the mapping from one to the other (the  Mapping Specification, or MSL); and

b) a repository for the metadata that defines the Entity Designer Diagram layout.

And this works out just fine for SQL Server.  Microsoft provides a nice little driver that is EF compatible, they provide a nice little GUI to create your Model with, and SQL Server tables — if named “normally”, i.e. Customer, Account, etc. — map beautifully to your object model.

This is NOT the case for Oracle.  Oracle still holds its nose whenever anyone says “Microsoft”, so they haven’t, and won’t, provide a driver for EF.  Heaven forbid they should make it easy to use Visual Studio and the .Net framework to work with their data.  Nope — Java, Eclipse, and the Oracle Application Framework are their preferred toolset.

So what is a .Net developer to do?  How can one create an EF model from an Oracle database?

ANSWER:  DevArt. 

DevArt has gone to the trouble of creating ADO.Net drivers for a variety of databases, including SQL Server and, da-da-da-da, Oracle.

I have been using DevArt’s dotConnect for Oracle drivers for EF purposes for about a year now.  In the early days, they were quite quirky.  I posted to their forums regularly.  Every single problem, suggestion, requirement or wish list item that I posted has been implemented, usually in their next release.  (They post quiet releases every 4 to 6 weeks.  Yay for Agile Development.)  Their latest version (5.35.79 as of this writing) works flawlessly for my purposes.

Anyway, included with their dotConnect drivers, is the DevArt Entity Developer.  This tool ROCKS!  Over the past year, it has matured to the point that I have completely abandoned using the MS Entity Designer for anything.  Instead, I create  and maintain my Entity Model’s using the DevArt Entity Developer. 

Anyway, in order to not step on anyone’s toes, DevArt saves the Entity Model as an EDML file, not an EDMX file.  (Yes, this is the “exact same” Entity Model XML that MS saves.)  They also store a bunch of metadata and project selections in a set of generated files behind the EDML file.  And, as of December, you can select to have the Entity Developer automatically generate the precompiled views file too.  (MS doesn’t do that and provides a 30-step process to accomplish it — A problem that has spawned many web posts.)

One of my favorite features of the DevArt Entity Developer over the MS one, has to do with an Oraclism that just bugs me. 

ORACLISM Oracle DBA’s, (by tradition, by culture, or by requirement, I’m not sure which), often name database objects in all caps with underscore delimiters.  For example, a table named CustomerAccountType in SQL Server, will probably be named CUST_ACCT_TYP in Oracle.  (There’s an unreasonably low limit to how long the name can be in Oracle, so the DBAs often have to truncate or compress names to make them fit.)

So, if you have a table defined thus:

CREATE TABLE  CUST_ACCT_TYP (
CUST_ACCT_TYP_ID NUMBER(10) NOT NULL,
TYPE_DESCRIPTION VARCHAR(20) NOT NULL)

Now, you drag and drop that table into the MS Entity Designer*, you will get an object defined thus:

Class:     CUST_ACCT_TYP
Property:  CUST_ACCT_TYP_ID, Double
Property:  TYPE_DESCRIPTION, String

Not a very “.Net-ish” way of defining a class, is it?  Also notice the NUMBER(10), an integral number type, is mapped to a Double.

DevArt’s Entity Developer, however, does a grand job of converting all those names, so you end up with an object defined thus:

Class:     CustAcctTyp
Property:  CustAcctTypId, Int64
Property:  TypeDescription, String

Notice how it even got the data type right for the Id. 

Now, if only I could UPDATE my model (instead of recreating the affected parts of it) when I change the Tables and Views in Oracle… 🙂

* By the way, you still need the DevArt drivers to do  this.