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?

1
2
2/11/2019 11:47:28 AM

Accepted Answer

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

  1. Modify the AddColumn command to create the column initially with nullable set to true (i.e. optional).
  2. Populate the column using the Sql method and raw SQL commands.
  3. 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);
}
2
2/12/2019 12:55:58 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow