Entity Framework Oracle and Sql Server - how to build a database independent application

Question

We are trying to build a data access layer for using both Oracle and SQL Server (not at the same time).

We use EF Model-first for creating the model and the create the SQL scripts for building the database. Our first thought was to create 2 EDMX files, one for each type, and use the appropriate one depending on the client's need. We are using the Oracle and SQL Server database generation workflow and DDL generation template to create the scripts for each database.

Our main problem is when the database schema changes we do not want to drop and recreate the DB but only create the migration scripts to update the DB base on our model (clients have many data that will be lost).

We use EF power pack for extracting the migration scripts for SQL Server but there is nothing like it for Oracle.

We want help to find a good data layer (1 EDMX for both Oracle and SQL Server if it's possible and not complicated) and a good way to generate database changes from our model to update existing client DBs in case of a new application release

We found this as a starting point http://msdn.microsoft.com/en-us/data/ff830362 but there is not mention for Oracle support.

We have tried code-first and EF Migrations but Oracle failed us again on the DB creation and migration.

Any recommendation on how we can accomplish this?

Thank You

Answer 1

There is no way to have single EDMX for both SQL Server and Oracle. EDMX consists of three parts CSDL (entity definition), SSDL (database definition), MSL (mapping between those definitions). SSDL must always target concrete database so you need at least separate SSDL for Oracle and SQL Server and if you are lucky you will not need separate MSL as well (mapping must be exactly same which will probably not happen if you are using any tool to generate the database).

So you always need at least part of EDMX file for second DB and manually maintain it.

If you need DB migration supporting Oracle you must look for tool from Oracle (or third party). For example RedGate offers tools supporting schema migration for both SQL Server and Oracle.

Visual Studion Premium and Ultimate edition also offers tools for comparing database schemas. It by default supports only SQL Server but Toad Extensions should add support for Oracle as well.

Once you have any of these tools you just need to compare schema deployed on customer server with your new schema and the tool should create migration script for you.