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

Question

I'm using Pomelo Entity framework core with MySqlConnector in my asp.net 5 app to connect to my MySql database - using custom DbContext classes. This normally works fine.

However, I have a need to connect to another database than the one in the connection string (for instance 'INFORMATION_SCHEMA').

I can of course change the connection string, replacing the database name, but:

That creates an extra connection pool - one per connection string!

I'm trying to avoid that - having only one connection pool per website.

I was messing around with 'SetDefaultSchema' and other attempts that all fail miserably.

How can I change the database name, the DbContext uses so I only have one connection pool and still each DbContext has its own database to connect to?

All ORMs, including EF Core use the old good ADO.NET behind the scenes (what else?). And `DbConnection` needs - you know, connection string. — Ivan Stoev, Nov 02 '21 at 19:49
Yes, but at least in MySql you CAN change the database of an opened connection. — Poul Bak, Nov 02 '21 at 20:01
How? Can you do that with MySQL ADO.NET connection object? Or you mean prepending SCHEMA to table names in SELECT and other DML SQL commands? If the later, then it seems they (Pomelo maintainers) specifically don't want to allow that - see their GitHub thread https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/pull/923 and related links — Ivan Stoev, Nov 03 '21 at 08:02
@IvanStoev: Your're right, 'Schema' is not compatible with MySql, which is why my first attempts failed (and the reason Pomelo abandoned it). The solution is below. — Poul Bak, Nov 03 '21 at 23:10

Poul Bak · Accepted Answer · 2021-11-30T18:01:36.363

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.

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

1 Answers1

Linked