Effective Oracle and EF – SQL Server to Oracle Migrations (I)

Early on in our EF development cycles, I gave our .NET QA team an unusual challenge:- Spin up our test suites in readiness for our upcoming ADO.NET Entity Framework support for Oracle, except without an pre-QA ADO.NET EF provider ready for Oracle. Our excellent QA got to work and built our the majority of our test suites on SQL Server, and when the time came we jumped to Oracle as we ramped up to our General Availability (GA) release in August. So why take such a roundabout route? The real motivation here was to prove a compelling value of EF. EF is ideally positioned to become a database agnostic API that gives .NET programmers portability across databases. This is something enjoyed by ODBC and JDBC developers across native and Java, and .NET developers can finally rejoice at this newly found productivity.

To that end, the next series blog postings recounts the challenge originally given to our .NET QA team. The story itself is not the main takeaway – you’ll find tips and advice on how best to more between SQL Server and Oracle for your EF based applications. This is something we have received numerous requests for – whether you are building out your Silverlight app, your WC/F service, ASP.NET etc. application and you want to seamlessly encompass new Oracle data sources in addition to SQL Server or whether you are looking to shift your SQL Server based EF wholesale to Oracle, there’s compelling content for you here.

With the Entity Framework (or any ORM for that matter) a well grounded notion of ‘data abstraction’ should be pervasive - application code can be developed separately without concerns about the underlying databases. Once the EF application is written, you don’t need to be concerned about the various features, SQL syntaxes of different databases, etc. That’s the desired goal but in reality, there are minor changes to the application, schema, or the EDMX files (SSDL, CSDL, and MSL). You can quickly asses the portability of your apps across data sources by grabbing a copy of the Query Samples from here. You find plenty of good Linq to Entities, QueryBuilder, EntitySQL, ObjectServices and Design API's sample to run with. Over the course of the next few postings, we'll look at these challenges…

• DataType mapping between SQLServer and Oracle • Identity Columns vs Sequences mismatch • Boolean datatype or Int16 • Stored Procedures with Ref Cursors

2. DataType matching between SQLServer and Oracle

Next up, you’ll need to better understand how best to line up the often disparate data types between SQL Server and Oracle. For Progress DataDirect Connect for ADO.NET we operate on the data type matching below:

* For integer value one can put a table constraint on columns to have the values within the limited range. **Available only in SQLServer2008

Oracle does have numerous other datatypes that don’t necessarily translate from SQL Server, but that’s a discussion for another day.

Identity Columns vs Sequences Mismatch & Limitations

Now one of the more vexing topics for EF Oracle developers. Microsoft SQLServer has long standing and widely used notion of auto-increment columns which rely on specifying a column to be of type "identity". This specification does not translate easily to Oracle parlance. Oracle savvy developers may have a reflex reaction towards Sequences as a worthy equivalent but this can lead to a trouble. SQLServer’s identity property is bound to a specific table column and this is easily verifiable using established metadata calls. Oracle’s Sequences are more like objects that do not maintain the same relationship and are expressly not bound to any tables or columns. Consider the following DDL – note the EmployeeID column is designated as an identity column. Let's see this case for SQLServer first SQLServer: Suppose there is a table called "Employees" whose partial definition is as shown below - Note the property IDENTITY on EmployeeID column [cc lang="sql" line_numbers="false"] CREATE TABLE [dbo].[Employees]([EmployeeID] [int] IDENTITY(1,1) NOT NULL, [LastName] [nvarchar](20) COLLATESQL_Latin1_General_CP1_CI_AS NOT NULL, [FirstName] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL, ... [/cc] Fetching the column meta data information on EmployeeID (e.g. using GetSchemaTable()), here is what one will get [cc lang="sql" line_numbers="false"] ColumnName ColumnOrdinal ColumnSize NumericPrecision NumericScale IsUnique IsKey BaseServerName BaseCatalogName BaseColumnName BaseSchemaName BaseTableName DataType AllowDBNull ProviderType IsAliased IsExpression IsIdentity IsAutoIncrement IsRowVersion IsHidden IsLong IsReadOnly [/cc] The "IsIdentity" declaration indicates this column’s identity qualities. If you have a similar table construct in an Oracle database, the column metadata information is not as expressive, as you see from the results below [cc lang="sql" line_numbers="false"] ColumnName ColumnOrdinal ColumnSize NumericPrecision NumericScale DataType IsLong AllowDBNull IsUnique IsKey BaseTableName BaseColumnName BaseSchemaName [/cc] The EF uses the behavior of auto-increment columns (such as IDENTITY) extensively, and Oracle requires explicit declaration of sequences. An example below First, lets create a sequence [cc lang="sql" line_numbers="false"] CREATE SEQUENCE supplier_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20; [/cc] Now, insert values into a table which uses the above sequence [cc lang="sql" line_numbers="false"] INSERT INTO suppliers (supplier_id, supplier_name) VALUES (supplier_seq.nextval, 'Kraft Foods'); [/cc] The story doesn’t end here and we are actively looking at ways to make this more seamless for our end users, so you can achieve portability between SQL Server and Oracle in this instance. More on this possibly in future postings. You can of course add you voice by commenting below or drop a note to our support team if you need this functionality.

In our next posting we’ll look into the details of how best to deal with Oracle Number types, and Int16s.