Entity Framework Entity-framework Code First Migrations Using Sql() during migrations
Example
For example: You are going to migrate an existing column from non-required to required. In this case you might need to fill some default values in your migration for rows where the altered fields are actually NULL. In case the default value is simple (e.g. "0") you might use a default or defaultSql property in your column definition. In case it's not so easy, you may use the Sql() function in Up() or Down() member functions of your migrations.
Here's an example. Assuming a class Author which contains an email-address as part of the data set. Now we decide to have the email-address as a required field. To migrate existing columns the business has the smart idea of creating dummy email-addresses like [email protected], where full name is the authors full name without spaces. Adding the [Required] attribute to the field Email would create the following migration:
public partial class AuthorsEmailRequired : DbMigration
{
public override void Up()
{
AlterColumn("dbo.Authors", "Email", c => c.String(nullable: false, maxLength: 512));
}
public override void Down()
{
AlterColumn("dbo.Authors", "Email", c => c.String(maxLength: 512));
}
}
This would fail in case some NULL fields are inside the database:
Cannot insert the value NULL into column 'Email', table 'App.Model.DatabaseContext.dbo.Authors'; column does not allow nulls. UPDATE fails.
Adding the following like before the AlterColumn command will help:
Sql(@"Update dbo.Authors
set Email = REPLACE(name, ' ', '') + N'@example.com'
where Email is null");
The update-database call succeeds and the table looks like this (example data shown):
Other Usage
You may use the Sql() function for all types of DML and DDL actibities in your database. It is executed as part of the migration transaction; If the SQL fails, the complete migration fails and a rollback is done.
