Dynamic MySQL Database Connection for Entity Framework 6

Dynamic MySQL database connection for Entity Framework 6

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 <entityFramework /> and <providers /> nodes, optionally (this is an absolute must in the second part of my answer, when dealing with dynamically defined databases) you may change the <defaultConnectionFactory /> 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 <parameter> 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.

At this stage, it's quite easy to connect to MySQL with Entity, you can just refer to the connectionString above by name. Note that if connecting by name, this will work even if the defaultConnectionFactory node still points at SQL Server (which it does by default).

public class ApplicationDbContext: DbContext
{
    public ApplicationDbContext() : base("mysqlCon")
    {
    }
}

The it is just a matter of connecting normally:

ApplicationDbContext db = ApplicationDbContext();

---

Connecting to a dynamically selected database name

At this point it's easy to connect to a database which we can pass as a parameter, but there's a few things we need to do.

Important Note

If you have not already, you MUST change the defaultConnectionFactory in Web.config if you wish to connect to MySQL
dynamically. Since we will be passing a connection string directly to
the context constructor, it will not know which provider to use and
will turn to its default connection factory unless specified in
web.config. See above on how to do that.

You could pass a connection string manually to the context like this:

public ApplicationDbContext() : base("Server:localhost;...")
{
}

But to make it a little bit easier, we can make a small change to the connection string we made above when setting up mySQL. Just add a placeholder as shown below:

<add name="mysqlCon" connectionString="Server=localhost;Database={0};Uid=username;Pwd=password" providerName="MySql.Data.MySqlClient" />

Now we can build a helper method and change the ApplicationDbContext class as shown below:

public class ApplicationDbContext: DbContext
{
    public ApplicationDbContext(string dbName) : base(GetConnectionString(dbName))
    {
    }

    public static string GetConnectionString(string dbName)
    {
        // Server=localhost;Database={0};Uid=username;Pwd=password
        var connString = 
            ConfigurationManager.ConnectionStrings["mysqlCon"].ConnectionString.ToString();

        return String.Format(connString, dbName);
    }
}

If you are using database migrations, the following step is important

If you are using migrations, you will find that the ApplicationDbContext will be passed to your Seed method by the framework and it will fail because it will not be passing in the parameter we put in for the database name.

Add the following class to the bottom of your context class (or anywhere really) to solve that problem.

public class MigrationsContextFactory : IDbContextFactory<ApplicationDbContext>
{
    public ApplicationDbContext Create()
    {
        return new ApplicationDbContext("developmentdb");
    }
}

Your code-first migrations and seed methods will now target the developmentdb schema in your MySQL database.

Hope this helps someone :)

How can I dynamically connect to different databases with Entity Framework 6?

//first DbContext
namespace MultiDataContextMigrations.Models
{
public class DataContext : DbContext
{
 public DataContext()
 : base("DefaultConnection")
 {

 }

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
 //TODO:Define mapping
 }

 public DbSet Users { get; set; }
 public DbSet Orders { get; set; }
}
}
//second DbContext
namespace MultiDataContextMigrations.Models
{
public class UserDataContext : DbContext
{
 public UserDataContext():base("DefaultConnection")
 {
 }

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
 //TODO:Define mapping
 }

 public DbSet Users { get; set; }
 public DbSet Roles { get; set; }
}
}

Check this Link
http://www.dotnet-tricks.com/Tutorial/entityframework/2VOa140214-Entity-Framework-6-Code-First-Migrations-with-Multiple-Data-Contexts.html

Using Entityframework Core, how can I dynamically change the MySql database, I connect to, without changing the connection string?

The solution is actually quite simple: Use connection interceptor (available from Entity Framework Core 3.0+).

The code below switches the database after the connection has been opened.
Now each DbContext class can use its own database and with only one connection pool in use.

First you create an interceptor class inherited from DbConnectionInterceptor. The constructor takes the database name, you want to switch to, as parameter:

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;
using System.Threading.Tasks;

public class MySqlConnectionInterceptor : DbConnectionInterceptor
{
    public MySqlConnectionInterceptor(string databaseName)
    {
        database = databaseName;
    }

    readonly string database;

    public override void ConnectionOpened(DbConnection connection, ConnectionEndEventData eventData)
    {
        if (database != null)
        {
            connection.ChangeDatabase(database); // The 'magic' code
        }
        base.ConnectionOpened(connection, eventData);
    }

    public override async Task ConnectionOpenedAsync(DbConnection connection, ConnectionEndEventData eventData, CancellationToken cancellationToken = default)
    {
        if (database != null)
        {
            await connection.ChangeDatabaseAsync(database); // The 'magic' code
        }
        await base.ConnectionOpenedAsync(connection, eventData, cancellationToken);
    }

}

Now all you have to is include one line in your DbContext class's OnConfiguring method:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{

    optionsBuilder.AddInterceptors(new MySqlConnectionInterceptor("yourDatabase"));
}

Now the connection will switch to 'yourDatabase' database every time, it's opened.

And it will only use one connection pool (total)! That way the number of 'sleeping' connections are kept at a minimum.

This works because Pomelo Entity Framework Core always resets a connection before reusing it from the pool (unless you specifically sets 'Connectionreset=false' - which is bad anyway). It sets the database back to the one in the connection string, which you of course can override again).

Of course you don't have to hard code the database name. If you for instance use a base DbContext class, that your other DbContexts inherits from, you can create a constructor that takes the database name as parameter, like this:

public class BaseDbContext : DbContext
{
    public BaseDbContext (string databaseName)
    {
        database = databaseName;
    }

    string database;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
    
        optionsBuilder.AddInterceptors(new MySqlConnectionInterceptor(database));
    }
}

The code has been tested in Asp.Net 5+6 and .Net Windows Forms.

---

Related Topics