Change Entity Framework provider in configuration

Question

I was hoping to be able to switch data providers to/from SQL Server and SQL Server Compact Edition via just a configuration change. But it doesnt work and looking at the EDMX file I think I can see why:

<edmx:StorageModels>
<Schema ... Provider="System.Data.SqlClient" ...

Is there any way to specify the provider in app.config or at runtime?

score 3 · Accepted Answer · edited May 23 '17 at 12:11

The Storage-Model is tied to a specific provider, which will cause the Entity Framework to reject any DbConnection implementations that are not compatible with the specified provider.

If you look at an Entity Framework connection-string, you can see that the StorageSchema, ModelSchema and Mapping are specified in three different files (which are generated from your .edmx and than embedded in the assembly). You could take your .edmx apart and embed the .ssdl, .csdl and .msl yourself and than create another .ssdl for SQL Server CE. This is basically just copy & paste and replacing the provider and some column-types.

I wrote about here: Comparison Entity Framework

score 1 · Answer 2 · answered Jun 10 '11 at 14:54

For Unit Tests I change Schema this way (changing ssdl before main code execution).

In code:

    var s = Assembly.GetExecutingAssembly().GetManifestResourceStream("Model1.ssdl");
    var ssdlFilePath = "<some-dir>\file1.ssdl";
    using (var file = File.Create(ssdlFilePath))
    {
        StreamUtil.Copy(s, file);
    }
    var str = File.ReadAllText(ssdlFilePath);
    str = str.Replace("old provider token", "ProviderManifestToken=\"4.0\"");
    str = str.Replace("old provider type"", "Provider=\"System.Data.SqlServerCe.4.0\"");
    File.WriteAllText(ssdlFilePath, str);

In app.config:

  <connectionStrings>
    <add name="Database2Entities" connectionString="metadata=res://*/Model1.csdl|<some-dir>\file1.ssdl|res://*/Model1.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=|DataDirectory|\Database1.sdf&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

It works)

Change Entity Framework provider in configuration

2 Answers2