Entity framework migration - add new column with a value for existing entries

Question

I have an application with Entity Framework Code First.

In a table, I have to add a column. So I added it in the model and created a Migration.

But during the migration, I would like to update the existing entries and add a value for this new column. This value has to be taken from the database (a constant field in my "Configuration" table).

But the default value should only be applied for existing entries, not for the next.

How can I do it from my Migration class ?

My current migration class :

public override void Up()
{
    var theDefaultValue = MyConstants.MyConstantParameterFromDatabase;
    AddColumn("MySchema.MyTable", "MyNewColumn", c => c.Decimal(nullable: false, precision: 18, scale: 2));
}

Edit : still looking for a solution to update all existing entries (with 0 value) but only after this migration...

Answer 1

You simply have to modify the Up() method, and include in it a SQL statement to set the column value in the existing rows. This will only be executed when the database is updated, and the update involves this particular Migration. Thus only the already existing rows will be updated when the Update-Database is invoked.

Add code like this after the AddColumn command, so that the column is already available in the table when the SQL statement runs:

Sql("UPDATE dbo.MyTable SET Column = (SELECT val FROM config)");

NOTE: (SELECT val FROM config) is pseudocode that you must replace with a query that returns the desired value