EF core and ASP.NET Core with a schema based multitenancy

Question

I am building and App with ASP.NET core and EF core. I want to implement a schema based multitenancy using a single database. Each schema in my database will consist of the same set of tables with the same names, so all the schema share the same structure. But the schema names are not known in advance since I want to create them dynamically in my APP.

I read this blog article where the author presents a method that uses IDbCommandInterceptor but modifying the SQL in this way could bring a lot of issues such as security issues or making requests to fail.

How can I dynamically specify in the DBContext, which schema to use (based on the received request) so that the data are saved and retrieved from this specified schema?

Answer 1

Summary:

This is possible to be done via .HasDefaultSchema() and some configuration injection in your context.

You can see example on GitHub.com.

---

What is done there:

1) We add schema to app.settings:

  "Database": {
    "Schema": "test"
  },

2) Additional model and provider for schema:

DatabaseSettings.cs

public class DatabaseSettings
{
    public string Schema { get; set; }
}

SchemaProvider.cs

public class SchemaProvider : ISchemaProvider
{
    private DatabaseSettings Settings { get; }

    public SchemaProvider(IOptions<DatabaseSettings> settings)
    {
        this.Settings = settings.Value;
    }

    public string GetSchema()
    {
        return this.Settings.Schema;
    }
}

3) In Startup.cs we register new configuration model and provider for schema name:

public void ConfigureServices(IServiceCollection services)
{
    services.Configure<DatabaseSettings>(this.Configuration.GetSection("Database"));
    services.Add(new ServiceDescriptor(typeof(ISchemaProvider), typeof(SchemaProvider), ServiceLifetime.Scoped));

    var connection = this.Configuration.GetConnectionString("TestDatabase");
    services.AddDbContext<TestContext>(options => options.UseSqlServer(connection));

    services.AddControllers();
}

4) In TestContext (EF Core Context) we add injection of schema provider and apply selected schema:

public partial class TestContext : DbContext
{
    private ISchemaProvider SchemaProvider { get; }

    ...

    public TestContext(DbContextOptions<TestContext> options, ISchemaProvider schemaProvider)
        : base(options)
    {
        this.SchemaProvider = schemaProvider;
    }

    ...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(this.SchemaProvider?.GetSchema() ?? "dbo"); // null value supported for migrations from EF Core CLI without application context

        ...
    }
}

---

What is not done?

• This is purely PoC, so this could not be used as production-ready code and requires extensive testing/profiling.

• This is not tested against app.settings change on-the-fly and custom configuration loading, there could be different issues.

• Migrations are manually fixed to use custom schema. Probably that is possible to support automatic migrations to runtime selected schema, see this link, but I never tried it.