Entity Framework use model for different providers

I have an entity 4.0 model that is using a SqlServerCE database as its prov开发者_运维问答ider. On the server I want to use the same project and just switch out the connection string to use the actual SqlServer database.

Here is my connection string

<add name="Entities"
        connectionString="metadata=res://*/Entities.csdl|res://*/Entities.ssdl|res://*/Entities.msl;
provider=System.Data.SqlClient;
provider connection string=&quot;
Data Source=xxxx;
Initial Catalog=xxxx;User ID=xxxx;Password=xxxx;&quot;" 
providerName="System.Data.EntityClient" />

When I attempt to query the Entity Model, I get the following error:

SqlCeCommand.CommandTimeout does not support non-zero values.

If I set the context timeout to 0, it then says

 Unable to cast object of type 'System.Data.SqlClient.SqlConnection'
 to type 'System.Data.SqlServerCe.SqlCeConnection'.

How do I set the provider from SqlServerCE to SqlClient?

You need to do a bit more work than just swapping out the connection string to support different providers. This article explains how to support more than one provider:

Preparing an Entity Framework model for multi provider support

The article covers supporting VistaDB and SQL Server but the same principles apply.

Yep I just came to the same problem - our app uses SQL Server but unit tests are executed using SQL Server CE. I've read the article provided by Kev and came up with an automation improvement to prevent manual copying of the file.

Assuming that "Metadata Artifact Processing" is set to "Embed in Output Assembly" you can to the following:

    public void SetupOnce()
    {
        var assembly = typeof(TContext).Assembly;
        var ssdlRes = assembly.GetManifestResourceNames().Single(e => e.EndsWith("ssdl")); //TODO handle multiple contexts

        using (var stream = assembly.GetManifestResourceStream(ssdlRes))
        using (var reader = new StreamReader(stream))
        {
            var result = reader.ReadToEnd().Replace("Provider=\"System.Data.SqlClient\"", "Provider=\"System.Data.SqlServerCe.4.0\"");
            File.WriteAllText(ssdlRes, result);
        }

        Context = new TContext();
    }

And the connection string is then set to:

<add name="DomainDbContext" 
     connectionString="metadata=res://*/Entities.Entities.csdl|./Entities.Entities.ssdl|res://*/Entities.Entities.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=StraDaLB_DM.sdf&quot;" 
     providerName="System.Data.EntityClient" />

Hope this helps someone someday ;)