A few weeks ago I've published a post regarding DataDirect's new Oracle provider that supports EF.

I was able to download DataDirect’s provider only this week, so it will take me a few more days to examine it and post some notes.

As for now, I would like to share with you some of the issues in EF we’ve came across migrating from Sql Server to Oracle, using the provider from DevArt.

BTW, we haven’t fully migrated to Oracle just yet, our project supports working with both databases using a factory for the creation of the EDM and the ObjectContext’s connection.

If you use EF at its simplest form, creating entity types and mapping them to tables, you won’t have many problems, the actual problems begins when you use the more extensive options EF has to offer, including user defined queries, using stored procedures etc..

Type matching

Different DBs have different types, the following table will help you decide which types to give your columns in your new Oracle tables.

LOB problems

If you use Oracle’s LOB types (BLOB, CLOB…) you will have a problem doing Union/Minus/Intersect queries, so if you have LOB columns in your entity and you currently use Linq to Entities queries with Union/Except/Intersect calls, you’ll need to restructure your queries.

Writing your own queries

If you decide to write your own sql select statement (using DefiningQuery), you will notice a bug that exists in Devart’s provider – If you write a sql statement like this:

Select column1, column2 from …

The generated sql statement that will be executed in the DB will look like this:

Select "extent1"."column1", "extent1"."column2" from (...) "extent1"

Since in oracle there is a difference between selecting “column1” and column1, your query will raise an error. To solve this problem, you just do the following fix to your sql statement:

Select column1 as “column1”, column2 as “column2” from …

I assume this bug will be fixed in next versions of the provider and I didn’t had the chance to see how DataDirect’s provider works, but I have the feeling they don’t have this problem (I hope I won’t be surprised).

Returning Keys and ResultBinding from Stored Procedures

When we write our own Stored Procedures for CUD operations, we sometimes have to deal returning auto-generated keys and computed values.

In Sql Server this is easy, we use the Select statement and select the values we want to return from the SP, and in the EF’s mapping we use ResultBinding to specify how to copy these values to their target properties.

In Oracle things get a bit tricky – you cannot write Select statements inside a SP without directing them to variables, so your SP needs to have an Out parameter of REF CURSOR type and use this parameter to hold the new values you want to return.

You’ll need to add this parameter to your Function element:

<Parameter Name="VCUR" Type="REF CURSOR" Mode="In" />

(There’s a bug with the Mode enumeration, so you can’t use Out)

And add a ResultBinding to your ModificationFunctionMapping:

<ResultBinding Name="DEPTNO" ColumnName="NEW_DEPTNO" />

This sounds easy enough, but with EF there is a small problem – if you add a parameter to your SP, you also have to map it to a property in your conceptual model, so we find ourselves forced to create a “dummy” property to hold the return value of the SP.

The EDM will look something like this (taken from example given by Devart’s support team with some changes):

<!-- SSDL content -->
...
        <EntityContainer Name="ModelStoreContainer">
          <EntitySet Name="DEPT" EntityType="Model.Store.DEPT">
            <DefiningQuery>select …, to_blob(null) as “Column1” from dept</DefiningQuery>
          </EntitySet>
        </EntityContainer>
        <EntityType Name="DEPT">
          <Key>
            <PropertyRef Name="DEPTNO" />
          </Key>
          <Property Name="DEPTNO" Type="int" Nullable="false" />
          <Property Name="DNAME" Type="VARCHAR2" MaxLength="14" />
          <Property Name="LOC" Type="VARCHAR2" MaxLength="13" />
          <Property Name="COLUMN1" Type="BLOB" MaxLength="1" />
        </EntityType>
...
<!-- CSDL content -->
...
        <EntityType Name="DEPT">
          <Key>
            <PropertyRef Name="DEPTNO" />
          </Key>
          <Property Name="DEPTNO" Type="Int32" Nullable="false" />
          <Property Name="DNAME" Type="String" MaxLength="14" Unicode="true" FixedLength="false" />
          <Property Name="LOC" Type="String" MaxLength="13" Unicode="true" FixedLength="false" />
          <Property Name="COLUMN1" Type="Binary" MaxLength="1" />
        </EntityType>
...
<!-- C-S mapping content -->
...
<EntityTypeMapping TypeName="IsTypeOf(Model.DEPT)">
              <MappingFragment StoreEntitySet="DEPT">
                <ScalarProperty Name="DEPTNO" ColumnName="DEPTNO" />
                <ScalarProperty Name="DNAME" ColumnName="DNAME" />
                <ScalarProperty Name="LOC" ColumnName="LOC" />
                <ScalarProperty Name="COLUMN1" ColumnName="COLUMN1"/>
              </MappingFragment>
</EntityTypeMapping>
...

Of course, once you do the above you’ll have a Blob column in your Select statement and you’ll have the LOB problem I’ve discussed previously.

The SP problem was one of our pitfalls migrating to Oracle, and as I understand, Devart will probably fix this in future versions, I just hope DataDirect’s provider doesn’t suffer from the same problem.

These where some problems we’ve faced migrating to Oracle. There are of course other issues such as rewriting SP, renaming SP parameters due to differences of naming conventions and other problems you face in these cases regardless of EF.

I will check DataDirect’s provider and will publish some notes in the future.