Preparing an Entity Framework Model for Multi Provider Support

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 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.

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.