Model-first with Entity Framework and Oracle 11g (Part 1)

In the very latest beta release of the Oracle .Net drivers (11.2.0.2.30), Oracle now ‘supports’ Entity Framework (database-first or model-first) and Linq-to-entities. Oh, and 32bit only for now.

(If you’re tempted to try this, and get a 'ORA-00904: "SUBPROGRAM_ID": invalid identifier' error it’s probably because you – as a developer - are using 10g Express Edition, which isn’t yet supported. You can wait for the EF beta 2 (soon, allegedly), or alternatively you can download 11g Express Edition which is also in beta. And 32 bit only (on Windows, anyway). Oh, and make sure you install the ODAC after you install XE, or you’ll get the wrong Oracle home as your default on your path, and you’ll get errors in Visual Studio setting up Oracle database connections)

Prior to EF I’ve been quite happy doing database-first development in the SQL Server world, mostly because the visual tooling (database diagrams and the like) has been pretty good, and because of the type fidelity with mainstream languages (esp. .net) there’s relatively little loss of intent when you derive a model from the schema.

In the Oracle world of course this is very different. I’ve been massively underwhelmed with the quality of the tooling for schema design, and Oracle’s unconventional[1] stance on 4-byte integer and boolean data types (i.e. lack thereof) means that any model derived from the schema is going to need a fair bit of tweaking the metadata. That and fixing up all the SHOUTING_IDENTIFIERS.

So for Oracle, model-first looks like a really good idea. Unfortunately, in the current beta at least, Oracle’s support for this is pretty lame:

• All integer types are written out as Number(38,0) (irritating)
• Sequences are not generated for identity fields (really painful)
• The PascalCasing of your entities is preserved in the database schema (totally unusable).

e.g.:

-- Creating table 'Customers'
CREATE TABLE "myschema"."Customers" (
   "Id" number(38,0) NOT NULL,
   "FirstName" nvarchar2(50) NOT NULL,
   "AnInteger" number(38,0) NOT NULL,
   "ALong" number(38,0) NOT NULL,
   "IsSomethingTrue" number(38,0) NOT NULL
);

Why unusable? Because, as any Oracle developer will tell you with a bitter look, Oracle is case sensitive. Generally when you query it it all works as if your query was upper-cased for you: select * from mytable works against MYTABLE. But if you use Mixed Case and create a MyTable you’d have to write select * from “MyTable” (note the quotes) and get the capitalization right. Yeah, you can see how much fun you could have with this, and it’s the same with columns. So stick to the SHOUTING_IDENTIFIERS convention, or suffer the horrible consequences. It’s ok, I didn’t know either.

So what any sane SSDL / DDL generation template would do would do a conversion, right? Entities named MyTable would spit out a table called MY_TABLE, and the MyTableId attribute would be a MY_TABLE_ID column. But no.

Worse than that, because Oracle’s implemented the entire generation process in a workflow, not in the T4 template (which is a stub) you can’t fix this on your own.

That’s about where I gave up on Oracle. Bastards.

Next time: 3rd party drivers to the rescue!

 

[1] I’m trying to be polite. Prehistoric would be more apt. DB2, MySql, Postgres (and SQL Server of course) have 4 byte integers because that’s what applications / CPUs actually use. Oracle act like it’s still 1990 and the ‘database IS the application’