Two different providers on the same config file

Question

Im using EntityFramework 6.1.0

I have 2 providers. MysqlClient and SQLServerCE, and i need to create 2 different DBContext. That forced me to create 2 configuration classes because mysql have some different things. But when i initialize the application, the Database.DefaultConnectionFactory is from the defaultConnectionFactory(config file), and i cant specify what provider to take on the daterminated Context.

How to do it?

My config file:

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
        <parameters>
          <parameter value="System.Data.SqlServerCe.4.0" />
        </parameters>
    </defaultConnectionFactory>
    <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
        <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
     </providers>
</entityFramework>

Mysql Context:

namespace Sistema.DataAccess
{
    [DbConfigurationType(typeof(Sistema.DataAccess.Migrations.Configuration))] 
    public class SistemaContext : Sistema.Common.Repository.DataContext
    {
        static SistemaContext()
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<SistemaContext, Sistema.DataAccess.Migrations.Configuration>());
        }
        public SistemaContext()
            : base(GetConnectionString())
        {

        }
        private static string GetConnectionString()
        { 
                return "Server=127.0.0.1;Database=?????;Uid=????;Pwd=????;Port=3306;";//MySQL
        }
    }
}

SQLCe Context:

namespace Sistema.DataAccess
{
    [DbConfigurationType(typeof(Sistema.DataAccess.Migrations.Configuration2))]
    public class SistemaContext2 : Sistema.Common.Repository.DataContext
    {
        static SistemaContext2()
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<SistemaContext2, Sistema.DataAccess.Migrations.Configuration2>());
        }
        public SistemaContext2()
            : base(GetConnectionString())
        {

        }
        private static string GetConnectionString()
        {
            return "Data Source=C:/teste2.sdf;Persist Security Info=False;";//SQLCE
        }
    }
}

Mysql Configuration

 public sealed class Configuration : DbMigrationsConfiguration<Sistema.DataAccess.SistemaContext>
    {
        public Configuration()
        {
            DbInterception.Add(new NLogCommandInterceptor());// guardar logs

            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;

            SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());//Mysql da erro se nao colocar isso.(Pelo que vi da para colocar no App.config tambem.)
            SetHistoryContextFactory("MySql.Data.MySqlClient", (conn, schema) => new MySQLHistoryContext(conn, schema));
        }
    }

SQLCE Configuration

public sealed class Configuration2 : DbMigrationsConfiguration<Sistema.DataAccess.SistemaContext2>
    {
        public Configuration2()
        {
            DbInterception.Add(new NLogCommandInterceptor());// guardar logs

            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }
    }

Answer 1

First of all you are using the wrong configuration classes. The DbConfigurationType needs a type inherited from DbConfiguration not DbMigrationsConfiguration<>.

The DbMigrationsConfiguration is really just used for the Migrators and DatabaseInitializers.

public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        this.SetDefaultConnectionFactory(new System.Data.Entity.Infrastructure.SqlCeConnectionFactory("System.Data.SqlServerCe.4.0"));

        this.SetProviderServices("System.Data.SqlServerCe.4.0", SqlCeProviderServices.Instance);

        this.AddInterceptor(new NLogCommandInterceptor());// guardar logs

        this.SetMigrationSqlGenerator("System.Data.SqlServerCe.4.0", () => new SqlCeMigrationSqlGenerator());
    }
}

[DbConfigurationType(typeof(MyDbConfiguration))]
public class TestContext : DbContext

Sadly it is not possible so set multiple DefaultConnectionFactories even with multiple DbConfigurations.

In your case you will have to store the connection strings in the app.config and pass the name to the DbContext constructor.

public class TestContext : DbContext
    {
        public TestContext()
            : base("name=MyConnectionString")
        {

        }

The connection will be initialized based on the provider name for MyConnectionString in the app.config

Or if you don’t want the connection string in your app.config just pass an already initialized DbConnection to the DbContext constructor

public class TestContext : DbContext
    {
        public TestContext()
            : base(new SqlCeConnection(GetConnectionString()),true)
        {

        }

Or if you don’t want to initialize a specific Connection use the DbProviderFactory.

public class TestContext : DbContext
    {
        public TestContext()
            : base(GetConnection(),true)
        {

        }

        public static DbConnection GetConnection() { 
            var factory = DbProviderFactories.GetFactory("System.Data.SqlServerCe.4.0");
            var connection = factory.CreateConnection();
            connection.ConnectionString = "Data Source=C:/teste2.sdf;Persist Security Info=False;";
            return connection;
        }