EF Core - Add new tables to database During Runtime

Question

I have an asp.net core project which needs to be able to support plugins at runtime, and as a consequence, I need to generate database tables based on what has been plugged in. The plugins are each divided in separate projects and they have have their own DbContext class. The plugins to be used are not known during compile-time, only at runtime.

Now in EF Core I thought that there would be a method like "UpdateDatabase" where you can just add tables to the existing database, but I was wrong. Is there a way to accomplish this? I was able to generate a separate database for each of the plugins, but that wasn't quite what I had in mind..I needed all tables in one database.

Here's the code for the "HRContext" plugin:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.EntityFrameworkCore;
using Plugins.HR.Models.Entities;

namespace Plugins.HR.Contexts
{
    public class HrContext : DbContext
    {
        public HrContext()
        {
        }
        public HrContext(DbContextOptions<HrContext> contextOptions) : base(contextOptions)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("HR");
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<Address> Address { get; set; }
        public DbSet<Attendance> Attendance { get; set; }
        public DbSet<Department> Departments { get; set; }
        public DbSet<Employee> Employees { get; set; }
        public DbSet<JobTitle> JobTitles { get; set; }
    }
}

Here's another piece of code for the "CoreContext" plugin:

using System;
using System.Collections.Generic;
using System.Text;
using Core.Data.Models;
using Microsoft.EntityFrameworkCore;
namespace Core.Data.Contexts
{
    public class CoreContext : DbContext
    {
        public CoreContext()
        {

        }
        public CoreContext(DbContextOptions<CoreContext> contextOptions) : base(contextOptions)
        {

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDefaultSchema("Core");
            base.OnModelCreating(modelBuilder);

        }
        public DbSet<Test> Tests { get; set; }
    }
}

My ConfigureServices method in Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<CoreContext>(options => options.UseSqlServer("Data source = localhost; initial catalog = Company.Core; integrated security = true;"))
    .AddDbContext<HrContext>(options => options.UseSqlServer("Data source = localhost; initial catalog = Company.HR; integrated security = true;"));

    // Add framework services.
    services.AddMvc();
}

If I try to change the connection string to be the same, sooner or later I will get an error that says that the table for one plugin does not exist. I tried "EnsureCreated" but that didn't work too.

Answer 1

I had the same issue. See my solution on GitHub a few days ago, here: EF Core Issue #9238

What you need is something like the following:

// Using an interface, so that we can swap out the implementation to support PG or MySQL, etc if we wish...
public interface IEntityFrameworkHelper
{
    void EnsureTables<TContext>(TContext context)
        where TContext : DbContext;
}

// Default implementation (SQL Server)
public class SqlEntityFrameworkHelper : IEntityFrameworkHelper
{
    public void EnsureTables<TContext>(TContext context)
        where TContext : DbContext
    {
        string script = context.Database.GenerateCreateScript(); // See issue #2943 for this extension method
        if (!string.IsNullOrEmpty(script))
        {
            try
            {
                var connection = context.Database.GetDbConnection();

                bool isConnectionClosed = connection.State == ConnectionState.Closed;

                if (isConnectionClosed)
                {
                    connection.Open();
                }

                var existingTableNames = new List<string>();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT table_name from INFORMATION_SCHEMA.TABLES WHERE table_type = 'base table'";

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            existingTableNames.Add(reader.GetString(0).ToLowerInvariant());
                        }
                    }
                }

                var split = script.Split(new[] { "CREATE TABLE " }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string sql in split)
                {
                    var tableName = sql.Substring(0, sql.IndexOf("(", StringComparison.OrdinalIgnoreCase));
                    tableName = tableName.Split('.').Last();
                    tableName = tableName.Trim().TrimStart('[').TrimEnd(']').ToLowerInvariant();

                    if (existingTableNames.Contains(tableName))
                    {
                        continue;
                    }

                    try
                    {
                        using (var createCommand = connection.CreateCommand())
                        {
                            createCommand.CommandText = "CREATE TABLE " + sql.Substring(0, sql.LastIndexOf(";"));
                            createCommand.ExecuteNonQuery();
                        }
                    }
                    catch (Exception)
                    {
                        // Ignore
                    }
                }

                if (isConnectionClosed)
                {
                    connection.Close();
                }
            }
            catch (Exception)
            {
                // Ignore
            }
        }
    }
}

Then at the end of Startup.Configure(), I resolve an IEntityFrameworkHelper instance and use that with an instance of DbContext to call EnsureTables().

One issue is I need to still account for the parts of the script which are not CREATE TABLE statements. For example, the CREATE INDEX statements.

I requested they give us a clean solution, for example: add a CreateTable<TEntity>() method to IRelationalDatabaseCreator. Not holding my breath for that though...

EDIT

I forgot to post the code for GenerateCreateScript(). See below:

using System.Text;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Storage;

public static class DatabaseFacadeExtensions
{
    public static string GenerateCreateScript(this DatabaseFacade database)
    {
        var model = database.GetService<IModel>();
        var migrationsModelDiffer = database.GetService<IMigrationsModelDiffer>();
        var migrationsSqlGenerator = database.GetService<IMigrationsSqlGenerator>();
        var sqlGenerationHelper = database.GetService<ISqlGenerationHelper>();

        var operations = migrationsModelDiffer.GetDifferences(null, model);
        var commands = migrationsSqlGenerator.Generate(operations, model);

        var stringBuilder = new StringBuilder();
        foreach (var command in commands)
        {
            stringBuilder
                .Append(command.CommandText)
                .AppendLine(sqlGenerationHelper.BatchTerminator);
        }

        return stringBuilder.ToString();
    }
}

It's based on the code found here: EF Core Issue #2943