Entity Framework use model for different providers

Question

I have an entity 4.0 model that is using a SqlServerCE database as its provider. 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?

Answer 1

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.