Ef Core migrate enum to different enum type


I have a code-first EF project with a table which uses the wrong enum in one column and need to migrate it using EF Migrations. I have to both rename the column, and then copy the old values to the new ones.

I know I can do it by simply writing an SQL script manually, but I was wondering if there is a way to let EF Migration do it automatically, "code-first" style?

The enums are stored as strings in the database, and they have slightly different members (but they have 1:1 mapping) i.e. the class is

public class Data
    public int Id { get; set; }
    public OldStatusType Status { get; set; }

public class DataMap : BaseConfiguration<Data>
    public override void Configure(EntityTypeBuilder<Data> builder)
        BaseConfigure(builder, "Datas", Schema.MySchema.ToString());

        builder.HasKey(x => x.Id);
        builder.Property(x => x.Id).UseSqlServerIdentityColumn();

        // map enum to string
            .Property(x => x.Status)

And now I need to change the class to

public class Data
    public int Id { get; set; }
    public NewStateType State { get; set; } // Status -> State

so that old Status values are mapped into new State values.

What is the correct approach for doing this in code-first migrations?


To clarify, I tried doing this:

public partial class ChangeDataEnumType : Migration
    protected override void Up(MigrationBuilder migrationBuilder)
        // rename Status to State
            name: "Status",
            schema: "MySchema",
            table: "Data",
            newName: "State");
        // migrate 'Old' enum value to 'New'
        migrationBuilder.Sql("UPDATE [MySchema].[Data] SET [State] = 'New' WHERE [State] = 'Old'");

but it fails with:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text']
      EXEC sp_rename N'[MySchema].[Data].[Status]', N'State', N'COLUMN';

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (8ms) [Parameters=[], CommandType='Text']
      UPDATE [MySchema].[Data] SET [State] = 'New' WHERE [State] = 'Old'

System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Status'.

But the last query doesn't mention "Status" at all, it uses the new column name, so I am completely confused.

If I try these two statements one by one in SSMS (and then rollback), they work without issues.

asked on Stack Overflow Apr 21, 2021 by Lou • edited Apr 21, 2021 by Lou

0 Answers

Nobody has answered this question yet.

User contributions licensed under CC BY-SA 3.0