Ef Migration for Changing Data Type of Columns

EF5 Code First - Changing A Column Type With Migrations

The smartest way is probably to not alter types. If you need to do this, I'd suggest you to do the following steps:

1. Add a new column with your new type
2. Use Sql() to take over the data from the original column using an update statement
3. Remove the old column
4. Rename the new column

This can all be done in the same migration, the correct SQL script will be created. You can skip step 2 if you want your data to be discarded. If you want to take it over, add the appropriate statement (can also contain a switch statement).

Unfortunately Code First Migrations do not provide easier ways to accomplish this.

Here is the example code:

AddColumn("dbo.People", "LocationTmp", c => c.Int(nullable: false));
Sql(@"
    UPDATE dbp.People
    SET LocationTmp =
        CASE Location
            WHEN 'London' THEN 1
            WHEN 'Edinburgh' THEN 2
            WHEN 'Cardiff' THEN 3
            ELSE 0
        END
    ");
DropColumn("dbo.People", "Location");
RenameColumn("dbo.People", "LocationTmp", "Location");

EF migration for changing data type of columns

You have a default constraint on your column. You need to first drop the constraint, then alter your column.

public override void Up()
{
    Sql("ALTER TABLE dbo.Received DROP CONSTRAINT DF_Receiv_FromN__25869641");
    AlterColumn("dbo.Received", "FromNo", c => c.String());
    AlterColumn("dbo.Received", "ToNo", c => c.String());
    AlterColumn("dbo.Received", "TicketNo", c => c.String());
}

You will probably have to drop the default constraints on your other columns as well.

I've just seen Andrey's comment (I know - very late) and he is correct. So a more robust approach would be to use something like:

 DECLARE @con nvarchar(128)
 SELECT @con = name
 FROM sys.default_constraints
 WHERE parent_object_id = object_id('dbo.Received')
 AND col_name(parent_object_id, parent_column_id) = 'FromNo';
 IF @con IS NOT NULL
     EXECUTE('ALTER TABLE [dbo].[Received] DROP CONSTRAINT ' + @con)

I know this probably doesn't help the OP but hopefully it helps anyone else that comes across this issue.

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

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.

How to change the column type in table using Entity Framework core

You should use EF Core migrations to update your db schema. The documentation is pretty good, so make sure to go through it.

However, this is a summary of how the process would be:

1. Make the change in your model (which by convention will be automatically detected. Alternatively, use the Fluent API in your DB Context OnCreate method or in your EntityConfigurations).
2. Add a migration running the following CLI command : dotnet ef migrations add SomeDescriptiveNameAboutWhatThisMigrationWillDo.
3. A migration file with an Up and Down method will be automatically generated. The Up will be run when you apply the migration, and the Down if you ever decide to revert it . You could add changes to the automatically scaffolded migration file. Based on the code in the migration file, EF Core will then generate a SQL script and apply the changes to the DB.
4. Once you have added (and maybe edited) the migration file, you need to apply it to the DB. You do that by running dotnet ef migrations update.
5. EF Core tracks all applied migrations in a table in your DB called by default __EFMigrationsHistory

In your particular case of changing a column type, EF Core might try to drop the column and recreate it, which will result in data loss. If you wanna keep your data, I would recommend altering the migration script to actually split the process in two: first add a new column with the new type and a slightly different name, then write some custom SQL to migrate data from the old column to the new one, then delete the old column and finally rename the new column to the correct name. To be honest, I am not sure if there is some custom migration operation that will out of the box change the data type without data loss, there might be.

To double check if the migration will generate data loss or check if it will do what you expect it to do, you can generate the SQL script that will be used by running dotnet ef migrations script <from migration> <to migration>. After reviewing it, you can either copy/paste and run the script in your DB, or just run the command detailed in step 4 above.

Change data type in Model while preserving data (Entity Framework Core)

When the problem is complex, I prefer divide that.

For this migration, I will make 2 update :

1. Create and populate Translations table
2. Populate PageDataSections table

1) Create and populate Translations table

See the temporary column Translations.PageDataSectionId

public partial class Update1 : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<int>(
            name: "PageDataSectionOrigin",
            table: "Translations",
            nullable: true);

        migrationBuilder.Sql(@"
            INSERT INTO Translations (TranslateRU, PageDataSectionOrigin)
            SELECT DataText, PageDataSectionId FROM PageDataSections
        ");
    }
}

2) Populate PageDataSections table

See the temporary column is deleted.

public partial class Update2 : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "DataText",
            table: "PageDataSections");

        migrationBuilder.AddColumn<int>(
            name: "DataTextTranslationId",
            table: "PageDataSections",
            nullable: true);

        migrationBuilder.Sql(@"
            UPDATE PageDataSections SET DataTextTranslationId = (select TranslationId FROM Translations WHERE PageDataSectionOrigin=PageDataSectionId)
        ");

        migrationBuilder.DropColumn(
            name: "PageDataSectionOrigin",
            table: "Translations");
    }
}

You can combine this two update in one.

I don't know why, but EF have named the column PageDataSection.DataText to DataTextTranslationId. But you can replace easily this name.

How to alter datatypes of primary and foreign keys using EF migration?

I realized that the way I am trying to solve this problem is ways to complicated.

Adding an additional migration step solves my problem in an astonishing easy way. So no need for an additional SQL script. Entity framework is fully able to migrate the primary and foreign keys (at least version 6.1.3 which I am using).

This is what the code looks like after calling Add-Migration with the appropriate parameters.

public override void Up()
{
    DropForeignKey(...)
    // ...
    DropIndex(...) 
    // ... 
    DropPrimaryKey(...)
    // ...
    AlterColumn(...)
    // ...
    AddPrimaryKey(...)
    // ...
    CreateIndex(...)
    // ...
    AddForeignKey) 
    // ...
}

---

Related Topics