How to script migrations and seeding to generate a final SQL script for execution in the deployment environment (test/prod/etc) without running Seed() method

ef6_deployment_scripts.md

Targeted deployments with Entity Framework Migrations

This is a note collecting the basic processes of deploying EF6 migrations to other servers (such as test & prod) without running the Seed method in Configuration.cs. It is often the case when deploying to test & prod environments that the DBAs will want to review SQL before it is executed in the target environment. In such a case the Seed method is not run, something that is not often clarified in online tutorials that imply the Seed method will always be available. This requires a completely different approach to seeding data.

Strategy

Step 1. Perform all seeding in one or more explicit migrations. Use the Sql() method and consider creating helper methods as needed.

Example:

    public override void Up()
    {
        AddRole("admin");
        AddRole("user");
        
        AddUser("jdoe", "admin");
        AddUser("jsmith", "user");
    }
    
    private void AddRole(string name)
    {
        var s = String.Format("INSERT INTO Roles (RoleName) VALUES ('{0}')", name);
        Sql(s);
    }
    
    private void AddUser(string username, string rolename)
    {
        var s = @"
DECLARE @id int;
SELECT @id = Id FROM Roles WHERE RoleName = '{2}';
INSERT INTO Users (UserName, RoleId) VALUES (username, @id);";
        s = String.Format(s, username, rolename);
        Sql(s);
    }

Step 2. Generate the script in the Package Manager Console as follows:

update-database -ConnectionString "<target db connection string>" -ConnectionProviderName <name> -script

Example for SQL Server:

update-database -ConnectionString "<target db connection string>" -ConnectionProviderName System.Data.SqlClient -script

Notice there are double-quotes around the connection string but not around the provider name. The ConnectionProviderName value can be found with the connection string in the Web.config file's connectionStrings child element for the particular EF context being used.

The generated script file can then be executed in the target environment.

Note: Be sure the user account has the necessary permissions to execute all migration changes in the target environment. In SQL Server the easiest solution is to add the user to the db_owner role:

exec sp_addrolemember 'db_owner', '<username>'

But this may give more permissions than the DBA is comfortable granting. Permissions ideally are strictly limited.