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

Solution 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

Solution 2

I wanted to copy a value from an existing column and inset to the newly introduced column in my Postgresql database, while running the update-database. So I edited the Up function in the Migration class.

protected override void Up(MigrationBuilder migrationBuilder) {
    migrationBuilder.AddColumn < string > (
    name: "FileName", table: "FavoriteFiles", nullable: false, defaultValue: "");
    migrationBuilder.Sql("UPDATE \"FavoriteFiles\" SET \"FileName\" = SPLIT_PART(\"FilePath\", '/', 7) WHERE \"FilePath\" IS NOT NULL;");
}

Then I ran Update-Database. Hope it helps.

Comments

[Andy Raddatz]

Since he has the value in a variable theDefaultValue in his migration, he can use it like this: Sql(@"UPDATE dbo.MyTable SET MyNewColumn = " + theDefaultValue + ";")

[JotaBe]

@AndyRaddatz Please, before changing an answer (I mean do more than correcting typos or changing the wording), re-read the question: --This value has to be taken from the database (a constant field in my "Configuration" table).--