Manage Entity Framework code first migrations in a setup project

Question

I am developing a setup project with Wix and I am installing a database using a migration .dll file.

I was planning on using Migrate.exe to execute the migrations from the .dll, but there is a requirement that if newer versions of the installer are run (with newer migrations) I must update the database. I couldn't find a way to list all the migrations with Migrate.exe and run only those that have not been ran against the installed database.

Do you know a way to fulfill this requirement with Migrate.exe, or any other tool or framework, that would allow me to track the migrations and run only those that are not executed for a database?

I should be able to implement the suggested functionality in the installer project in order for it to work.

Thanks.

Best regards, Evgeni Dyulgerov

Answer 1

You do not need to run migration scripts during installation - your installer will need too high privileges to do so and you may have problems with install package policies applied by system administrators from Active Directory.

Db upgrade is application logic too and I believe it belongs to the app - not the installer.

Of course you can do it in wix with custom actions. But you will have to load all the assemblies that are required for the migration to take place - run SQL commands at least.

I recommend you run the migrations in your app startup.

Here is a snipped that does the job. First lets scatch some context :

1. You have N consequtive migrations you created during the project development cycles.

2. You can use EF tooling from Package Manager Console in Visual Studio or directly ( the .exe that you can find in the nuget package ) to create your partial (update) migrations. You will compile them in the migrations assembly afterwards so that they are carried with your app to the client with the installer. An example :

   Add-Migration -Name SomeDbChangeDescription -StartUpProjectName TheCoreProjectWithConfig -ProjectName TheProjectWithMigrations -ConfigurationTypeName "Fully.Qualified.Type.Name.Of.MigrationConfiguration" -ConnectionString "Data Source=.\SQLINSTANCE;Database=my-db;Trusted_Connection=False;User ID=xxx;Password=xxx" -ConnectionProviderName "System.Data.SqlClient"

Remember that with this script you generate a migration script from one db but you can later apply it on multiple databases - when you have db-per-tenant. You do not need to generate migration script for every database if they stem from the same model because there is no DB specific information hard coded in the migrations script during the generation.

3. You compile your migrations assembly and make sure it is in the installer and it is loaded on app start.

Now you can use (modifiy as you like) the following code. dbMigrator.Update() will apply all the migrations scripts that need to be applied - if you have 10 migrations in the assembly with 2 not applied migrations it will compare the model of the db and all the migrations and will apply only the last 2. If you call the dbMigrator.Update() method with a database name that does not exist it will create new DB applying the initial script and all other partial updates.

public class DbInitializer : IDbInitializer
{
    private readonly IConnectionStringProvider _connectionStringProvider;

    public DbInitializer(IConnectionStringProvider connectionStringProvider)
    {
        _connectionStringProvider = connectionStringProvider;
    }

    public void CreateOrUpdateDb(string dbName)
    {
        try
        {
            string connectionString = _connectionStringProvider.GetConnectionString(dbName);
            DbMigrationsConfiguration cfg = CreateMigrationsConfig(connectionString);
            cfg.CommandTimeout = 900;
            cfg.AutomaticMigrationsEnabled = false;
            cfg.AutomaticMigrationDataLossAllowed = false;
            DbMigrator dbMigrator = new DbMigrator(cfg);

            var pendingMigrations = dbMigrator.GetPendingMigrations().ToArray();
            if(pendingMigrations.Length > 0)
            {
                foreach(var pendingMigration in pendingMigrations)
                {
                    InitializerEventSource.Log.UpgradingDb(dbName, pendingMigration);
                }

                dbMigrator.Update();
                DbInitializerEventSource.Log.UpgradedDb(dbName);
            }
        }
        catch(MigrationsException exception)
        {
            // exception handling
        }
        catch(Exception exception)
        {
            // exception handling
        }
    }

    private DbMigrationsConfiguration<InitDbContext> CreateMigrationsConfig(string connectionString)
    {
        DbMigrationsConfiguration<InitDbContext> cfg = new DbMigrationsConfiguration<InitDbContext>
            {
                AutomaticMigrationsEnabled = false,
                AutomaticMigrationDataLossAllowed = false,
                MigrationsAssembly = Assembly.Load("TheAssemblyContainingTheMigrations"),
                MigrationsNamespace = "TheNamespaceWhereTheMigrationsAre",
                ContextKey = "HardCodedContexKey",
                TargetDatabase = new DbConnectionInfo(connectionString, _connectionStringProvider.ProviderInvariantName)
            };
        return cfg;
        }     
}

UPDATE You can apply another approach -

1. Use the above mentioned AddMigration script during development and add -Script directive to obtain the SQL migration script instead of C# migration script.
2. Use WIX Sql Extension to execute it during installation. The official Sql Extension for Wix Documentation.