How to create a database from sql script using Entity Framework Core?

Question

I am starting a project with EF Core, were the database already exists. I have the database structure as an sql script, that I would like to run every time I initialize the context, in order to have a fresh database every time I would run the application. Here follows a snippet from the traditional EF, showing what I would like to achieve with EF Core.

    internal sealed class AnnotationContextDatabaseInitializer : DropCreateDatabaseAlways<AnnotationContext>
{
    public override void InitializeDatabase(AnnotationContext context)
    {
        base.InitializeDatabase(context);
        context.Database.ExecuteSqlCommand(TransactionalBehavior.EnsureTransaction, File.ReadAllText("dropAndCreateEntireDatabase.sql"));
    }
}

score 0 · Accepted Answer · answered Feb 07 '20 at 09:09

After getting more experienced with EF, I can see that it does not make sense to use the context for things like creating a DB. This would be a chicken egg problem, were the context is not valid has it misses the underlying tables.

This does be done with a traditional SqlCommand, running a script that contains the DB.

´´´

    public void ExecuteNonQuery(string query, string connectionString)
    {
        string[] splitter = new string[] {"\r\nGO"};
        string[] commandTexts = query.Split(splitter, StringSplitOptions.RemoveEmptyEntries);

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.RetryOpen();

            foreach (var commandText in commandTexts)
            {
                try
                {
                    using (SqlCommand cmd = connection.CreateCommand())
                    {
                        cmd.CommandText = commandText;
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    connection.Close();
                    throw;
                }
            }

            connection.Close();
        }
    }

´´´

This can be run lets say in a test initialize.

How to create a database from sql script using Entity Framework Core?

1 Answers1