Add new Required Field to one of table with EF Code First Migration

Question

I am using EF Code First Migration. I already have lots of data on production Db and I would like to intorduce a non nullable field. How it could be possible?

Currently it throws an error:

The column cannot contain null values. [ Column name = Test,Table name = 'MyTable'] 

Answer 1

The strategy I generally use for this is to first introduce the new column as optional, populate it, and then make it required. You can actually make separate migration steps by making separate migrations for each of these steps or manually change the automatically generated migration code to make the change all happen in one migration. I will be describing how to use a single migration.

If you add a new [Required] field, the autogenerated migration may look like this which will fail if dbo.MyTable has data in it:

public override void Up()
{            
    AddColumn("dbo.MyTable", "MyColumn", c => c.String(nullable: false));
}

public override void Down()
{
    DropColumn("dbo.MyTable", "MyColumn");
}

You can edit the migration to initially add the column as optional, prepopulate it, and then mark it required. You can use Sql() to perform the prepopulation. Depending on your data, you may desire to calculate the new column’s value based on other columns or even another table and you can do that by writing the appropriate SQL.

public override void Up()
{
    AddColumn("dbo.MyTable", "MyColumn", c => c.String());
    Sql("UPDATE dbo.MyTable SET MyColumn = COALESCE(SomeOtherColumn, 'My Fallback Value')");
    AlterColumn("dbo.MyTable", "MyColumn", c => c.String(nullable: false));
}

public override void Down()
{
    DropColumn("dbo.MyTable", "MyColumn");
}

If you merely want to set the same value on all rows, you can skip the Sql() step and add the column as required with a defaultValue before removing the defaultValue. This allows you to set all rows to the same value during migration while still requiring you to manually specify the value when adding new rows. This approach supposedly doesn’t work on older versions of EF. I’m not sure which version of EF is required, but it should at least work on modern ones (≥6.2):

public override void Up()
{
    AddColumn("dbo.MyTable", "MyColumn", c => c.String(defaultValue: "Some Value", nullable: false));
    AlterColumn("dbo.MyTable", "MyColumn", c => c.String(nullable: false));
}

public override void Down()
{
    DropColumn("dbo.MyTable", "MyColumn");
}