Introduction

Thanks to the new functionalities added by Entity Framework 4 it's possilbe to use Visual Studio 2010 designer to create a database structure, that will enable the usage of classes (Entities), and an ObjectContext as a LINQ-enabled interface to the database backend.

The following approach will enable the usage of different database providers, but a single code base to query all in the same way.

The example covers how to use SqlServer and Oracle without the need to write separated queries for each one.

A simple test Project

I've built a simple test project that runs the deletion/creation of a simple data structure and tries to save and retrieve data on each of the provider specified in the connection strings.

Unluckly for non-italian readers, the project has entities and properties written in italian, I don't have time to translate it right now, but that won't be a big trouble for you I think =)

You can clone it.

The project is built with Visual Studio 2010, and uses Entity Framework 4.1, it uses a Model-First approach, and as I said, it supports SqlServer and Oracle database backends.

The provider used by SqlServer is the standard one, for Oracle I used dotConnect for Oracle v6.30 (warning: older versions doesn't support identity fields for oracle, they doesn't add an autoincrement trigger like the newer versions to handle the identities similarly to SqlServer, for instance).

Model-First Approach

To create a data structure with the Model-First approach of Entity Framework and get the entities classes for free you just need to do the following:

• Add to the project a new ADO.NET Data Model (Model.edmx file in the sample project)

• Using the designer to create the tables/entities and their relations.

At this point it will be possible to create an ObjectContext and query the database through LINQ

var context = new EFTestEntities(); // EFTestEntities is the property of the model: Name Container

In the project there will be a partial class for each entity of the model (in the test project they are: Autore and Libro). These classes can be easily extended to add more methods or derived properties to your model. You just need to create a partial class with the same name of the entity you want to extend, just put them under the same namespace of the model.

It is possible to create the database in run-time using the method CreateDatabase() on the ObjectContext we've created (in the same way is possible to use DeleteDatabase() to drop it).

Adding multiple Database provider support

To enable the support of multiple database providers, an SSDL file needs to be created for each provider that you will need to support:

• In the model property select the DDL generation template accordingly to the provider (Ex: **SSDLToSQL10.tt for SqlServer and Devart SSDLToOracle.tt for Oracle)

• Right-Click on the model designer space and select Generate database from model

• Specify a valid connection to a database for the provider and follow the wizard

• At the end of the wizard, open the Model.edmx file with the XML Editor (Right-Click -> Open With..) and just copy the Schema tag and its content under the comment inside an xml file calles, for example: Model..ssdl (In the sample project you'll find Model.SqlServer.ssdl and Model.Oracle.ssdl), and set the Copy property to Always Copy

• Specify in the metadata of the connection string the SSDL file we created (Ex: metadata=res:///Model.csdl|Model.Oracle.ssdl|res:///Model.msl for Oracle connection string)

You should now be able to create an ObjectContext for the provider/connection string of your choice:

string connString = ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
var context = new EFTestEntities(connString);

The steps above needs to be done again each time the Model.edmx file will be modified, to align the SSDL files with the new model structure. And you will also need to recreate the database or re-align it manually, or with a script (until it will be added the migration support to Entity Framework..)

Browse on GitHub