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