Click here to Skip to main content
14,971,029 members
Articles / Programming Languages / C#
Technical Blog
Posted 3 Jan 2012

Tagged as


4 bookmarked

Preparing an Entity Framework Model for Multi Provider Support

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
3 Jan 2012CPOL5 min read
It is possible to use a single ADO.NET Entity Framework model between two database providers, but not without some extra work.

EDMX Northwind VS2010

I was recently tasked with creating a number of samples testing the compliance of both VistaDB and Microsoft SQL Server with Linq to Entity queries. Each sample tested if the provider was able to execute the query without error and then compare both queries to ensure that both providers returned the same results. To ensure valid test results, I needed all queries to be executed against a single entity model for both database providers. Entity Framework was designed in a generic manner for this very purpose so my task should be trivial, correct? In this article, I will explain the complications I ran into during my task.

Generating the Original Model

My original Entity Data Model was generated from a VistaDB Northwind example which I had an identical copy of in my local SQL Express server. The process of generating a new data model will add a new appconfig file to your project if there is not already one present. If there is already an appconfig file present, the data model will simply add a new connection string to it. Entity Framework connection strings always includes paths to the three files that make up the data model, the database connection string and provider information. The EF connection string that was auto generated for Northwind.vdb4 looks like this:

<add name="NorthwindEntities" connectionString="metadata=res://*/NorthwindModel.csdl|
provider connection string='Data Source=&quot;C:\Northwind.vdb4&quot;'" 
providerName="System.Data.EntityClient" />

Things to note about the connection string:

  • The path of the CSDL file */NorthwindModel.csdl
  • The path of the SSDL file */NorthwindModel.ssdl
  • The path of the MSL file */NorthwindModel.msl
  • The database connection string Data Source=C:\Northwind.vdb4
  • Provider Name System.Data.VistaDB

Changing the Connection to SQL Server

After creating a new entity data model from the VistaDB database and making sure the connection works, the next step is to find a programmatic way to change the data models connection to SQL Server.

My first assumption would be to create a new instance of my data model and check if the API allows me to change the connection string, which it does.

NorthwindEntities context = new NorthwindEntities();
context.Connection.ConnectionString = MySqlConnection;

The first thing that comes to mind is “what a simple use of the API to handle multiple providers”. The problem is that even though the connection property has been changed on the model, it still tried to use the VistaDB provider. There is no provider field to set on the context so how is anyone supposed to support two providers? A quick search turned up a great article from Muhammad Mosa on this very issue. In this article, he explains that there is no simple way to support multiple providers from a single entity data model. The problem is tied to provider specific information that is hard coded to one of the meta data files.

Creating a Second SSDL File

The SSDL file that is auto generated with the entity model does contain provider specific information which limits its independence. This is a snippet from the SSDL generated from a VistaDB data model.

<Schema Namespace="NorthwindModel.Store" 
    Alias="Self" Provider="System.Data.VistaDB" ProviderManifestToken="4.0

Even if the connection string property Is changed, the model will continue to use this reference to the VistaDB provider. In order to allow the model to use SQL Server as well, another copy of the SSDL file will have to be made with altered provider information.

Steps to create a second SSDL file:

  • Add a new XML file to the project, I would suggest using naming similar to your entity model and save the file as a .ssdl (Northwind.ssdl);
  • The only line in the file at this point should be <?xml version="1.0" encoding="utf-8" ?>
  • Right click the entity model, open with the XML text editor.
  • Copy the contents from <!-- SSDL content --> to <!-- CSDL content -->
  • Paste this under the first line of the new SSDL file.
  • Edit the provider information at the top, in my case, this was Provider="System.Data.SqlClient" and ProviderManifestToken="2008".

Please note that as Muhammad Mosa mentions, this alteration could be more intense if the second provider was something like MySql, where table schemas and data types don’t match as well. At this point, with a second SSDL file that references the SqlClient provider, I should be able to use the data model generically.

Altering the Connection String to Use a New SSDL

As mentioned before, entity model connection strings contain paths to their data model files. An alteration will need to be made to the SqlServer connection string to point to the new SSDL file. In my case, the SSDL file was being copied out to a libs folder in my project directory and this is how the connection string looked.

provider connection string='Data Source=.\SQLEXPRESS;
Initial Catalog=Northwind;
Integrated Security=True;MultipleActiveResultSets=True'

Note the relative path used to point this connection string to the new SSDL file. ..\..\..\libs\Northwind.SqlServer.ssdl.

Programmatic Provider Change

After all of the previous alterations, I can now change which database provider the entity model uses programmatically with the following code:

NorthwindEntities VDBEntities = new NorthwindEntities();
NorthwindEntities SQLEntities = new NorthwindEntities(SqlConnection);

VDBEntities is an instance of the northwind entity model which will uses the auto generation connection string from the appconfig file to connect to a VistaDB database. SQLEntities is an instance of the exact same model but instead uses the connection string shown above to connect to SQL Server.


It is possible to use a single ADO.NET Entity Framework model between two database providers, but not without some extra work. The changes needed cannot be done automatically within Visual Studio. But the amount of work is not too difficult for an afternoon project.

The above steps are identical with any other ADO.NET provider, but the data types and schema targets will have to be adjusted to the other database vendor. Muhammad Mosa’s blog post covers SQL Server and MySql in one model if you are interested in those providers.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
United States United States
I hold a PhD in computer science, and have been a practicing developer since the early 90's.

I used to be the owner for VistaDB, but sold the product to another company in August 2010.

I have recently moved to Redmond and now work for Microsoft. Any posts or articles are purely my own opinions, and not the opinions of my employer.

Comments and Discussions

Questionhaving the EF and the new .ssdl file in a seperate .dll Pin
Member 855250012-Oct-12 0:05
MemberMember 855250012-Oct-12 0:05 
QuestionMy approach Pin
aSarafian3-Jan-12 20:35
MemberaSarafian3-Jan-12 20:35 
I had the same problem when choosing EF as the main DAL layer but with multiple database vendors.
Even with SQL2005 and SQL2008 there is a problem, because the version is specified within those efl files.

What I did was to create a tool that split the entire file into three separate ones. Use one as the major version and produce other for the other databases. These files are included as resource in the assembly that contains the model.

Also I created out own custom connection generator and with the help of some attributes,I can find the proper resources to build the connection for the specified database.

Sometimes you just ask yourself, what the hell are the architects of microsoft thinking?

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.