CODEWITHSUNDEEP
entity-framework
Joshua Ohana
Joshua Ohana

Reputation: 6121

EF changing data type from string to int, how to drop and add column during migrations

I am changing a column from string to int on one table. The migration got built fine with ef migrations add but when trying to run update I was got an error that

Conversion failed when converting the nvarchar value 'MyField' to data type int.

I don't need the data so I figured I could modify the up/down to just drop and re-add the columns instead of an alter. This is the old up/down

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<int>(
            name: "MyField",
            table: "MyTable",
            nullable: false,
            oldClrType: typeof(string));
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<string>(
            name: "MyField",
            table: "MyTable",
            nullable: false,
            oldClrType: typeof(int));
    }

I changed that the below

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn("MyField", "MyTable");
        migrationBuilder.AddColumn<int>(
            name: "MyField",
            table: "MyTable",
            nullable: false);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn("MyField", "MyTable");
        migrationBuilder.AddColumn<string>(
            name: "MyField",
            table: "MyTable",
            nullable: false);
    }

Now when running ef database update I am getting a new error that would make sense...except I cannot find an ALTER TABLE anywhere!

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'PlanType' cannot be added to non-empty table 'Subscriptions' because it does not satisfy these conditions.

What could I be missing that's not allowing me to do this?

** Resolved

Per @strickt01 answer I just had to allow nullable from Down and supply a default value in Up

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn("MyField", "MyTable");
        migrationBuilder.AddColumn<int>(
            name: "MyField",
            table: "MyTable",
            defaultValue: 1,
            nullable: false);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn("MyField", "MyTable");
        migrationBuilder.AddColumn<string>(
            name: "MyField",
            table: "MyTable");
    }

Upvotes: 2

Views: 4668

Answers (1)

strickt01
strickt01

Reputation: 4048

Dropping the column and then re-creating it does not clear out the data in the table. Your Subscriptions table will still have rows. Thus when you try to add the column which you declare as non-nullable with no default value you get the error (SQL cannot populate the current rows with non-null values without a default). The ALTER TABLE statement is referenced because that is the SQL for adding a column: 

ALTER TABLE Subscription ADD PlanType int not null

See docs

You will either need to declare a default or create it as non-nullable, populate it as part of the migration and then change it to non-nullable.

Upvotes: 3

Related Questions