Dynamically Changing Schema in Entity Framework Core

Dynamically changing schema in Entity Framework Core

Did you already use EntityTypeConfiguration in EF6?

I think the solution would be use mapping for entities on OnModelCreating method in DbContext class, something like this:

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal;
using Microsoft.Extensions.Options;

namespace AdventureWorksAPI.Models
{
    public class AdventureWorksDbContext : Microsoft.EntityFrameworkCore.DbContext
    {
        public AdventureWorksDbContext(IOptions<AppSettings> appSettings)
        {
            ConnectionString = appSettings.Value.ConnectionString;
        }

        public String ConnectionString { get; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(ConnectionString);

            // this block forces map method invoke for each instance
            var builder = new ModelBuilder(new CoreConventionSetBuilder().CreateConventionSet());

            OnModelCreating(builder);

            optionsBuilder.UseModel(builder.Model);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.MapProduct();

            base.OnModelCreating(modelBuilder);
        }
    }
}

The code on OnConfiguring method forces the execution of MapProduct on each instance creation for DbContext class.

Definition of MapProduct method:

using System;
using Microsoft.EntityFrameworkCore;

namespace AdventureWorksAPI.Models
{
    public static class ProductMap
    {
        public static ModelBuilder MapProduct(this ModelBuilder modelBuilder, String schema)
        {
            var entity = modelBuilder.Entity<Product>();

            entity.ToTable("Product", schema);

            entity.HasKey(p => new { p.ProductID });

            entity.Property(p => p.ProductID).UseSqlServerIdentityColumn();

            return modelBuilder;
        }
    }
}

As you can see above, there is a line to set schema and name for table, you can send schema name for one constructor in DbContext or something like that.

Please don't use magic strings, you can create a class with all available schemas, for example:

using System;

public class Schemas
{
    public const String HumanResources = "HumanResources";
    public const String Production = "Production";
    public const String Sales = "Sales";
}

For create your DbContext with specific schema you can write this:

var humanResourcesDbContext = new AdventureWorksDbContext(Schemas.HumanResources);

var productionDbContext = new AdventureWorksDbContext(Schemas.Production);

Obviously you should to set schema name according schema's name parameter's value:

entity.ToTable("Product", schemaName);

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.

How to define schema table level

Use ToTable method:

public class RegionConfiguration : IEntityTypeConfiguration<Region>
{
    public void Configure(EntityTypeBuilder<Region> builder)
    {
        builder.HasKey(x => x.RegionId); //Primary Key
        builder.ToTable("TableName", "dbo");
        // or  builder.ToTable(nameof(<entity>), "dbo");
    }
}

Changing schema name on runtime - Entity Framework

I've managed to resolve this issue by using a brilliant library, located in CodePlex (courtesy of Brandon Haynes), named "Entity Framework Runtime Model Adapter", available here:
http://efmodeladapter.codeplex.com/

I've tweaked it a bit, to fit our needs and without the need of replacing the designer code at all.

So, I'm good.

Thanks anyways, and especially to Brandon, amazing job!

---

Related Topics