c# - EF6 and multiple configurations (SQL Server and SQL Server Compact)

Question

Update: Problem solved, see end of this question.

The problem:

We are trying to use Entity Framework 6 and code-based configuration in a scenario were we have use both a SQL Server and SQL Server CE in the same AppDomain.

This quite simple scenario seems not to be supported "by design". From the EF team:

Note: We do not support having multiple configuration classes used in the same AppDomain. If you use this attribute to set different configuration classes for two contexts an exception will be thrown.

More information here: Code-based Configuration (Codeplex)

The question:

How do we move forward from here? Any help would be greatly appreciated! Is there a more flexible way to connect a configuration to a context instead of an AppDomain?

(Our context classes are located in different assemblies. We have tried the DbConfigurationType attribute but the problem is EF itself)

Configuration files:

Configuration for normal SQL server

public class EfConfiguration : DbConfiguration
{
    public EfConfiguration()
    {
        SetProviderServices(
            SqlProviderServices.ProviderInvariantName, 
            SqlProviderServices.Instance);

        SetDefaultConnectionFactory(new SqlConnectionFactory());
    }
}

Configuration for SQL Server Compact Edition

public class EfCeConfiguration : DbConfiguration
{
    public EfCeConfiguration()
    {
        SetProviderServices(
            SqlCeProviderServices.ProviderInvariantName,
            SqlCeProviderServices.Instance);

        SetDefaultConnectionFactory(
            new SqlCeConnectionFactory(SqlCeProviderServices.ProviderInvariantName));
    }
}

UPDATE:

The error which we get is:

System.TypeInitializationException : The type initializer for 'MyProject.Repositories.Base.DataContext' threw an exception. ----> System.InvalidOperationException : An instance of 'EfCeConfiguration' was set but this type was not discovered in the same assembly as the 'DataContext' context. Either put the DbConfiguration type in the same assembly as the DbContext type, use DbConfigurationTypeAttribute on the DbContext type to specify the DbConfiguration type, or set the DbConfiguration type in the config file. See http://go.microsoft.com/fwlink/?LinkId=260883 for more information.

UPDATE 2, the solution As described above, we can only have one configuration. This is a problem since Sql and SqlCe uses different providers. If we use "SetDefaultConnectionFactory" to fit one type of database, the other will fail.

Instead, supply the connection into the context as described in the post marked as answer below. Once you always initialize the context with a connection as opposed to a connectionstring you are good to go. You can remove the SetDefaultConnectionFactory call from the configuration. We're using only the code below for configuring the SqlCe Context and no configuration for the Sql Context.

  public class CommonEfConfiguration : DbConfiguration
    {
        public CommonEfConfiguration()
        {
            // EF does not know if the ce provider by default,
            // therefore it is required to be informed about it.
            // The connection factories are not necessary since the connection
            // is always created in the UnitOfWork classes
            SetProviderServices(SqlCeProviderServices.ProviderInvariantName, SqlCeProviderServices.Instance);
        }
    }

Answer 1

EDIT: based On Error details: Did you already try tell EF where the config class is found?

[DbConfigurationType("MyNamespace.MyDbConfiguration, MyAssemblyFullyQualifiedName")]
public class MyContextContext : DbContext
{
}

If that cant be made work, then see alternative

Use the Context with constructor DbConnection

public class MYDbContext : DbContext {
     // MIgration parameterless constructor is managed in  MyMigrationsContextFactory 

    public MyDbContext(string connectionName) : base(connectionName) { } // no this

    public MYDbContext(DbConnection dbConnection, bool contextOwnsConnection)  // THIS ONE
        : base(dbConnection, contextOwnsConnection) {  }

you then need a "DBConnection" connection for each provider. For SQL server

      public DbConnection GetSqlConn4DbName(string dataSource, string dbName) {
        var sqlConnStringBuilder = new SqlConnectionStringBuilder();
        sqlConnStringBuilder.DataSource = String.IsNullOrEmpty(dataSource) ? DefaultDataSource : dataSource;
        sqlConnStringBuilder.IntegratedSecurity = true;
        sqlConnStringBuilder.MultipleActiveResultSets = true;

        var sqlConnFact = new SqlConnectionFactory(sqlConnStringBuilder.ConnectionString);
        var sqlConn = sqlConnFact.CreateConnection(dbName);
        return sqlConn;
    }

repeat for SqlCe factory, it can also generate a DBConnection SqlCe connection factor create connection