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

So having given up on the Oracle provider for now, and because people had said good things about them, I tried devArt’s dotConnect:

-- Table "Customers"
CREATE TABLE "Customers"  ( 
    "Id" NUMBER(10) NOT NULL,
    "FirstName" VARCHAR2(50) NOT NULL,
    "AnInteger" NUMBER(10) NOT NULL,
    "ALong" NUMBER(18) NOT NULL,
    "IsSomethingTrue" NUMBER(1) NOT NULL,
    PRIMARY KEY ("Id")
)
/
CREATE SEQUENCE "Customers_SEQ"
/
CREATE OR REPLACE TRIGGER "Customers_INS_TRG"
    BEFORE INSERT ON "Customers" FOR EACH ROW
BEGIN
    SELECT "Customers_SEQ".NEXTVAL INTO :NEW."Id" FROM DUAL;
END;
/

Better. We got our sequence triggers, and some slightly saner data type choices. We've still got the casing problem (and no check constraints for our boolean flag), but we can fix that now because the devArt DDL generation template is a real T4 template, not just a stub:

You’ll find yours in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen. There’s not loads in there, all the real work is in the include file in .\Entity Framework Tools\Templates\Includes\Devart SSDLToOracle.ttinclude

A quick case-conversion modification to the the OracleGenerator class:

	public override string GetValidIdentifier(string identifier) {

		identifier = base.GetValidIdentifier(identifier);
		return ToOracleCase(identifier);
	}

	public static string ToOracleCase(string value){
		StringBuilder output = new StringBuilder();
		bool waslower = false;
		foreach (var c in value.ToCharArray())
		{
			bool isLower = char.IsLower(c);
			if(waslower && !isLower)
				output.Append('_');
			
			output.Append(Char.ToUpper(c));
			waslower = char.IsLower(c);
		}
		return output.ToString();
	}

...and we are ready to go (ok, it's a crude implementation, but look, we are off and away):

-- Table "CUSTOMERS"
CREATE TABLE "CUSTOMERS"  ( 
    "ID" NUMBER(10) NOT NULL,
    "FIRST_NAME" VARCHAR2(50) NOT NULL,
    "AN_INTEGER" NUMBER(10) NOT NULL,
    "ALONG" NUMBER(18) NOT NULL,
    "IS_SOMETHING_TRUE" NUMBER(1) NOT NULL,
    PRIMARY KEY ("ID")
)
/
CREATE SEQUENCE "CUSTOMERS__SEQ"
/
CREATE OR REPLACE TRIGGER "CUSTOMERS__INS_TRG"
    BEFORE INSERT ON "CUSTOMERS" FOR EACH ROW
BEGIN
    SELECT "CUSTOMERS__SEQ".NEXTVAL INTO :NEW."ID" FROM DUAL;
END;
/

Much better.

Update: The next day I realised I’d made a cardinal error. All any of this does is change the generated DDL, not the store schema in the EDMX model. I had to do that separately, which rather defeated the point of what I was trying to do. In the end I gave up, went database-first and wrote an XSLT that performs a PascalCasing conversion on the conceptual model object / attribute names. And re-mapped any badly mapped types. What a PITA.