Setting provider and connection string in EntityFramework for MySql

Question

I am using Entity Framework and in my solution there are 9 projects and it will enlarge. My problem is stating connection string in .config file. When I did like this, I had to state connection string for 4-5 projects and when I wanted to change my connection, changing ConnectionStrings is becoming an obligation for 4 or 5 projects. I want to set connection string in DbContext constructor. DbContext can provide me this ability but I can't define providerName.So dbconnection of context automatically use SqlClient but I want to use MySql provider. My connection string is :

"Server=localhost;Database=xxx;Uid=auth_windows;Persist Security Info=True;User=root;Password=yyyyyy;"

Also I can't specify provider name in connection string like "Provider = MySql.Data.MySqlClient". It throws exception "Provider keyword is not supported".

I am changing my question for more comprehensible.

In short I want to to this.

public class XxContext : DbContext
{

       public XxContext()
       {

            this.Database.Connection.Provider = "MySql.Data.MySqlClient";
            this.Database.Connection.ConnectionString = "Server=localhost bla bla bla";

       }
}

But I don't know how to state you should use MySql.Data.MySqlClient(without config file). Is it possible ? If it is, how can I do ?

Answer 1

Yours is a connection string used with SqlConnection objects. Since these objects support only SQL Server you cannot use the Provider keyword. Moreover, since you're using EF, you need to specify a different connection string in order to use the model of your database, a context and the Provider keyword. A typical EF connection string would be:

<connectionStrings>
    <add name="AdventureWorksEntities" 
         connectionString="metadata=.\AdventureWorks.csdl|.\AdventureWorks.ssdl|.\AdventureWorks.msl;
         provider=System.Data.SqlClient;provider connection string='Data Source=localhost;
         Initial Catalog=AdventureWorks;Integrated Security=True;Connection Timeout=60;
         multipleactiveresultsets=true'" providerName="System.Data.EntityClient" />
</connectionStrings>

In order to use the MySQL provider, consider these steps provided in this answer:

Entity Framework 6 offers some handy subtle changes which aid in both getting MySQL working and also creating dynamic database connections. Getting MySQL working with Entity Framework 6

First, at the date of my answering this question, the only .Net connector drivers compatible with EF6 is the MySQL .Net Connectior 6.8.1 (Beta development version) which can be found at the official MySQL website here.

After installing, reference the following files from your Visual Studio solution:

Mysql.Data.dll
Mysql.Data.Entity.EF6.dll

You will also need to copy these files somewhere where they will be accessible to the project during build time, such as the bin directory.

Next, you need to add some items to your Web.config (or App.config if on desktop based) file.

A connection string:

<connectionStrings>
    <add name="mysqlCon"
         connectionString="Server=localhost;Database=dbName;Uid=username;Pwd=password"

         providerName="MySql.Data.MySqlClient" /> </connectionStrings>

Also add the provider, inside the and nodes, optionally (this is an absolute must in the second part of my answer, when dealing with dynamically defined databases) you may change the node:

<entityFramework>
    <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" />
    <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
    </providers> </entityFramework>

If you change the defaultConnectionFactory from the default sql server connection, don't forget to remove the nodes which are nested in the defaultConnectionFactory node. The MysqlConnectionFactory does not take any parameters for its constructor and will fail if the parameters are still there.

Answer 2

You can use the DbProviderFacory which is registered in the config file (See MSDN and SO article).

Your config file should have something like this

<DbProviderFactories>
  <remove invariant="MySql.Data.MySqlClient" />
  <add name="MySQL Data Provider"
       invariant="MySql.Data.MySqlClient"
       description=".Net Framework Data Provider for MySQL"
       type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>

And your DbContext and IDbContextFactory implementation something like this...

DbContext

public class MyDbContext
    : DbContext
{
    public MyDbContext(DbConnection connection, bool contextOwnsConnection)
        : base(connection, contextOwnsConnection)
    {

    }
}

IDbContextFactory

public class MyDbContextFactory
    : IDbContextFactory<MyDbContext>
{
    private readonly string _connectionStringName;

    public MyDbContextFactory(string connectionStringName)
    {
        Contract.Requires<NullReferenceException>(
            !string.IsNullOrEmpty(connectionStringName),
            "connectionStringName");
        _connectionStringName = connectionStringName;
    }

    public MyDbContext Create()
    {
        var connectionStringSettings = ConfigurationManager
            .ConnectionStrings[_connectionStringName];

        var connection = DbProviderFactories
            .GetFactory(connectionStringSettings.ProviderName)
            .CreateConnection();

        if (connection == null)
        {
            var message = string.Format(
                "Provider '{0}' could not be used",
                connectionStringSettings.ProviderName);
            throw new NullReferenceException(message);
        }

        connection.ConnectionString = connectionStringSettings
            .ConnectionString;
        return new MyDbContext(connection, true);
    }
}

A connection is given to the context constructor with is able to include the DbProvider programatically. The context is set to own the connection so that it can open on first use and close on dispose.