Click here to Skip to main content
Click here to Skip to main content

Tagged as

Preparing an Entity Framework model for multi provider support

, 18 May 2010
Rate this:
Please Sign up or sign in to vote.
Preparing an Entity Framework model for multi provider support
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|
res://*/NorthwindModel.ssdl 
|res://*/NorthwindModel.msl;
provider=System.Data.VistaDB;
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.

metadata=..\..\..\libs\Northwind.SqlServer.ssdl|
res://*/NorthwindModel.csdl|
res://*/NorthwindModel.msl;
provider=System.Data.SqlClient;
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 use 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.

Summary

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.

License

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

Share

About the Author

JasonShort
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.
Follow on   Twitter

Comments and Discussions

 
Questionusing a seperate dll for entity model and the new .ssdl-file PinmemberMember 855250012-Oct-12 5:31 
QuestionIssue with your tutorial..! Please help! :) Pinmembergiangiacomo.ermacora28-Mar-12 0:39 
GeneralMy vote of 5 PinmemberAlberto Bar-Noy2-May-11 22:49 
GeneralMy vote of 5 Pinmemberheebinho15-Feb-11 1:19 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 18 May 2010
Article Copyright 2010 by JasonShort
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid