How to add new column to existing table and set it value based on existing column using EF migrations

Question

How to add new column to existing table and set it value based on existing column using EF migrations

.net entity-framework-core

Question

For example have a table with values:

Id CarModel
1  Passat
2  Land Cruiser

And need to add non nullable column Manufacturer. Initial values for Manufacturer (for existing records) should be:

For CarModel=Passat - VW 
For CarModel=Land Cruiser - Toyota
etc.

My migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
  migrationBuilder.AddColumn<string>(
  name: "Manufacturer",
  table: "Cars",
  nullable: false);
}

How can I do this?

Hopeless

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 2/12/2019 12:55:58 PM

No really good way to do that. You need to edit the generated migration manually as follows:

Modify the AddColumn command to create the column initially with nullable set to true (i.e. optional).
Populate the column using the Sql method and raw SQL commands.
Add AlterColumn command to change the column nullable to the intended false (i.e. required).

Something like this:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>(
        name: "Manufacturer",
        table: "Cars",
        nullable: true);

    migrationBuilder.Sql(
@"UPDATE Cars SET Manufacturer = CASE CarModel
  WHEN 'Passat' THEN 'VW'
  WHEN 'Land Cruiser' THEN 'Toyota'
  ELSE 'Other' END");

    migrationBuilder.AlterColumn<string>(
        name: "Manufacturer",
        table: "Cars",
        nullable: false,
        oldClrType: typeof(string),
        oldNullable: true);
}

How to add new column to existing table and set it value based on existing column using EF migrations

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related