Need to create tables at runtime to support plugin solution

[gordon-matt]

I'm creating a project with a plugin architecture.. where each of the plugins may have entities that are added to the DbContext. That part is no issue - I can easily load in the new entity types at runtime, but the problem I am facing is that I have no way to automatically create the tables. EF migrations is not an option in this scenario, so I need another solution.

EF can already generate the proper SQL to create the database, so I'm hoping it won't take too much effort to have something like this:

context.CreateTableFor<TEntityType>();

Even better: bring back the EntityTypeConfiguration<TEntityType> class from EF6 and you can do something like this:

context.CreateTableFor<TEntityType>(EntityTypeConfiguration<TEntityType> configuration);

Those of us needing to create tables at runtime due to plugins, etc. could call such a method from the plugins on install for each table that needs to be created.

[BenjiZombie]

I'm using IEntityTypeConfiguration<> (I'm on 2.0.0-preview2), to scan libs for entity configurations, but I'm not creating tables outside of the migrations scripts

[JohnPicchi]

Take a look at this blog post by Rowen Miller, I think this should help.

[ajcvickers]

@gordon-matt It seems like using Migrations at runtime, which if I remember rightly is what Rowan does in his post, could be a reasonable approach here. Beyond that, if existing tables never need to be modified, then you could look into using context.GetService<IRelationalDatabaseCreator>().CreateTables(). You might need to create a temporay DbContext containing just the new entity types so EF doesn't try to create tables that alreay exist. You could also get a database script using the code shown in issue #2943.

[gordon-matt]

Thanks for the info. The blog post by Rowan Miller shows how to discover the classes that make up the model at runtime. That is something I've known how to do for a long time. He does also show how to invoke migrations from code:

var config = new DbMigrationsConfiguration<MyContext> { AutomaticMigrationsEnabled = true };
var migrator = new DbMigrator(config);
migrator.Update();

That's only for EF6, so I found the following for EF Core:

context.Database.Migrate()

However, that did nothing at all. I assume that is just supposed to apply the latest migrations that were created via the "Add-Migration" command. That's not helpful in my case, since there won't be any such migrations. Unless you know some way to create migrations inside class libraries (the plugins) and have them applied that way?

I also looked at IRelationalDatabaseCreator and it's interesting, but only has CreateTables() which states that it assumes none of the tables have already been created. I propose a new method like, CreateTable<TEntity>() so we can create the ones we need.

For now, I think I will have to look into the code for generating the database script, as shown in #2943. I will have to split that up and go through each "CREATE TABLE" statement, running only the ones where the table doesn't already exist. That's not very elegant, but I guess it will have to do until a better solution comes along.

[gordon-matt]

Alright, for those interested in a workaround, here is my solution. Be warned, it's a bit of a hack, but it works:

// 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.

This would be so much cleaner if IRelationalDatabaseCreator had a CreateTable<TEntity>() method that I could call from each plugin upon installation.

[nillkitty]

Here's a much less hacky way of doing this; it's how I'm doing this in v5.0.11 (this method is a member of the inherited DbContext). You can tweak the foreach loop if you want to filter out specific migration commands and let others through, but this should replay all of the necessary commands, including creation of indexes and keys; as long as you suppress any "already exists" exceptions, this is good enough for most use cases.

        /// <summary>
        /// Updates the database to ensure that all tables have been created
        /// for which there are valid entities
        /// </summary>
        public void EnsureTablesCreated()
        {

            var infrastructure = this.GetInfrastructure();
            var migSqlGen = infrastructure.GetService<IMigrationsSqlGenerator>();
            var modelDiffer = infrastructure.GetService<IMigrationsModelDiffer>();
            var conn = infrastructure.GetService<IRelationalConnection>();

            var diffs = modelDiffer.GetDifferences(null, Model.GetRelationalModel());
            var sql = migSqlGen.Generate(diffs, Model, MigrationsSqlGenerationOptions.Default);
            foreach(var s in sql)
            {
                try
                {
                    s.ExecuteNonQuery(conn);
                } catch (Exception ex) 
                    when (ex.Message.Contains("There is already an object named"))
                {
                    // Ignore
                }
            }

        }

[janseris]

Here's a much less hacky way of doing this; it's how I'm doing this in v5.0.11 (this method is a member of the inherited DbContext). You can tweak the foreach loop if you want to filter out specific migration commands and let others through, but this should replay all of the necessary commands, including creation of indexes and keys; as long as you suppress any "already exists" exceptions, this is good enough for most use cases.

        /// <summary>
        /// Updates the database to ensure that all tables have been created
        /// for which there are valid entities
        /// </summary>
        public void EnsureTablesCreated()
        {

            var infrastructure = this.GetInfrastructure();
            var migSqlGen = infrastructure.GetService<IMigrationsSqlGenerator>();
            var modelDiffer = infrastructure.GetService<IMigrationsModelDiffer>();
            var conn = infrastructure.GetService<IRelationalConnection>();

            var diffs = modelDiffer.GetDifferences(null, Model.GetRelationalModel());
            var sql = migSqlGen.Generate(diffs, Model, MigrationsSqlGenerationOptions.Default);
            foreach(var s in sql)
            {
                try
                {
                    s.ExecuteNonQuery(conn);
                } catch (Exception ex) 
                    when (ex.Message.Contains("There is already an object named"))
                {
                    // Ignore
                }
            }

        }

Is there currently a way to load schema from the database in form of IRelationalModel and compare it with the current DbContext schema ("design time")?
That would:

1. Inform user of having the same table but in different version/state
2. Allow printing migration commands without the need of executing them first and checking if they fail

In your code, you are comparing with null IRelationalModel which yields all commands required to create the schema defined by DbContext.

My use case is plugin architecture as well.