Introduction
One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.
This feature allows you build applications independent from the underplaying database provider. In this post I’m going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.
Entity Framework providers
Entity Framework is database independent. Each database provider could have its own Entity Framework provider. Currently there are several database provider who support the current version of Entity Framework that is released with .Net 3.5 SP1.
A list of Entity Framework providers can be found here.
Entity Framework multiple database support sample
In this post, I am going to demonstrate a sample application (based on northwind database) that is going to support both SQL Server database engine and MySQL database engine.
MySQL Entity Framework provider (connector/net)
MySQL Entity Framework provider I am going to use is connector/net ADO.NET provider for MySQL. It is a free provider and open source built by MySQL .Net data provider team. The version I am using is a modified version (by me) of connector/net 6.0.3. At the time of writing this post current release is 6.0.4.
How to prepare your Entity Data Model for multi-database support?
In order to keep your conceptual model (CSDL) static and ready to be used with different databases you should make sure that your database schema is the same across all database engine you plan to support.
For example, Northwind database schema in SQL Server is the same as Northwind database in MySQL. There are differences in data types, for example uniqueidentifier doesn’t exists in current version of MySQL, however there is a replacement which could be varbinary(16). Also data types might differ in names but there representation in .Net is the same, for example there is bit in SQL server and boolean -tinyint- in MySQL can be mapped to Boolean -bool- in .Net. Schema includes views and stored procedures/user defined functions (names, parameters and return results)
As conclusion, you have to maintain your database schema across any engine you wish to use to keep your CSDL untouchable. However change in your storage schema in EDM (SSDL) is mandatory as we are going to see later.
Steps support multiple database providers using Entity Framework
Modifying the SSDL
First step would be preparing your database on the provider you wish to work with. In my case it is MySQL. Script of northwind database on MySQL is attached with this post.
Next step is to modify your SSDL! If you are using VS.Net 2008 SP1 Entity Framework designer then your probably got a EDMX file for the generated EDM. the EDMX combines all CSDL,SSDL and MSL in it. You need just to have another SSDL file for your new database under MySQL.
To proceed with this, just create another file with and call it for example Northwind.MySQL.ssdl. Set its Copy to Output Directory option to Copy Always. Then add the following at the top of the new document (XML root):
<?xml version="1.0" encoding="utf-8"?>
Then follow the following steps to extract (copy) SSDL from the generated EDMX
- Right click on your EDMX file “Northwind.edmx” and select “Open With…” from the context menu.
- From “Open With” dialog select XML Editor (make sure you already saved your EDMX). An XML view of your EDMX will appear.
- Find an XML comment <!--SSDL content --> where you will find underneath it a StorageModels element.
- Copy the content of Schema element under StorageModels element (without StorageModels element)
- Past what you just copied into Northwind.MySQL.ssdl
After you got your copy of SSDL file you’ll need to modify it. Lets walkthrough where the modification should take place.
The Schema element contain several attributes. 2 of them should be changes to a new values. Provider and ProviderManifestToken attributes.
- Provider attribute specify the underlying .Net data provider name that should be used for this storage schema (SSDL). So change its value to “MySql.Data.MySqlClient”.
- ProviderManifestToken used to load the provider manifest for offline scenarios.There is no specific schema for the token; it is up to the provider to choose the minimum information needed to identify a manifest without having to open a connection to storage. You’ll notice in most cases it shows a version of the underlying database engine. So in MySQL case it should be “MySQL5”.
The final output of Schema element should look like this:
<Schema Namespace="NorthwindModel.Store" Alias="Self"
Provider="MySql.Data.MySqlClient"
ProviderManifestToken="MySQL5"
xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"
xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">...
</Schema>
The next changes are related to the storage schema (schema name and data types).
On each EntitySet element there is an attribute called schema. In case of SQL Server this attribute is set to the related table schema. In MySQL you just need to set this attribute to the name your database.
<EntitySet Name="Categories" .... Schema="northwind" />
Note: in the above code snippet part of code is omitted for clarification.
Now under each EntityType element there are several Property elements. Each Property element maps to a database table column with the same name. And of course should be of the same data type. That is why there is a Type attribute for each Property element.
Sometimes (depending on provider mapped data types) you’ll need to change this Type attribute for several Property elements. For example MySQL provider doesn’t recognize the data type ntext which supported by SQL Server. However another data type could be used such as MEDIUMTEXT or LONGTEXT.
You should correct such things in your Schema EntityTypes Properties. Happy that Nothwind isn’t complicated at all and most of the data types used with it are common between both engines. I just had to change few types.
Now we are done with SSDL modification, and time to move to Connection String.
Modifying Connection String
I left this to the end. Changing connection string would the most easy task. I highly recommend to read about Connection Strings in Entity Framework.
The connection string generated to you would be something like this:
<connectionStrings>
<add name="Northwind"
connectionString="metadata=res://*/NorthwindModel.csdl|res://*/NorthwindModel.ssdl|res://*/NorthwindModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQLExpress;Initial Catalog=Northwind;Integrated Security=True;""
providerName="System.Data.EntityClient" />
</connectionStrings>
A change to connection string is mandatory to specify different ssdl file, provider and provider connection string. All these information is provided above as a value to
connectionString attribute of
add element.
A modified Connection string would be like this:
<add name="NorthwindEntitiesMySQL"
connectionString="metadata=Northwind.MySql.ssdl|res://*/NorthwindModel.csdl|res://*/NorthwindModel.msl;provider=MySql.Data.MySqlClient;provider connection string="server=localhost;user id=root;password=Pa$$w0rd;persist security info=True;database=northwind""
providerName="System.Data.EntityClient" />
Note that metadata value is changed. It now points to Nothwind.MySQL.ssdl file that is not part of assembly resource, but a file on the same folder of the executing assembly. Also provider value change to MySql.Data.MySqlClient and provider connection string is changed to “server=localhost;user id=root;password=Pa$$w0rd;persist security info=True;database=northwind”.
Note that you also can choose to make your Northwind.MySQL.ssdl as part of assembly resources by changing the Build action of the file to be Embedded Resource and Copy to Output Directory option to Do not copy. In this case your metadata value will be “metadata=res://*/Northwind.MySql.ssdl|res://*/NorthwindModel.csdl|res://*/NorthwindModel.msl”.
Now have a look at the attached sample project. Notice that there is no change in code that access the entity framework, just changing the connection string to use another one.
Conclusion
Being database independent is a great feature that comes out of the box with Entity Framework. Being able to switch to another database engine without touching your code is one big feature I admire myself. Just changes in SSDL and connection string and you are done to go live on another database with your application.
We already applied this practically with latest release of KiGG. Now KiGG supports repositories and models that are implemented with current version of Entity Framework. KiGG -with appreciation to Entity Framework- supports both SQL Server and MySQL. No code change –check it yourself-
Feel free to download KiGG or the attached sample to explore this cool feature.