Changing primary key data type in Production DB

asp.net-mvc c# ef-code-first ef-migrations entity-framework-6

Question

I am using EF code-first. I need to change the datatype of primary key of one table in our production database.

public class Course 
{
   [Key]
   public Guid Id {get; set;}    //This needs to be changed to int

   public string Name {get;set;}

   public virtual ICollection<Group> Groups {get;set;}
}

public class Group
{
   [Key]
   public Guid Id {get; set;}

   public string Name {get;set;}

   public virtual ICollection<Course> Courses{get;set;}
}

As the above is a Many-Many relationship, EF has created a join table GroupCourses automatically with PK & FK as both Group_Id and Course_Id. Which is fine until now.

Now I need to change the primary key in Course entity to int from Guid.

I changed the datatype from Guid to int and successfully created a new migration. But when I try to run the command update-database, I am getting below error.

Operand type clash: uniqueidentifier is incompatible with int in query

I am not sure how to fix the above error.

Also, I think it's impossible to change the datatype of a primary key field without losing data (of course, we can copy from backup manually). I hope I am wrong.

Can someone please tell me is there any better way of achieving this without losing data ?

NOTE : I have gone through other SO posts related and other internet forums but no luck.

1
1
11/9/2016 5:41:08 PM

Accepted Answer

You get the type clash error because the generated migration tries to do directly ALTER COLUMN on the affected columns to change the type from Guid to integer, and it is not possible because the Guid values can not be implicitly converted to integer values.

You have to manually modify your migration. Remove the AlterColumn lines. In order to migrate your existing course ids without data loss you can temporarily rename the existing Guid columns (adding a suffix _Old), create new columns with the right new name and type, fill the contents in the relationship table with the right values using an UPDATE with a SELECT, and finally delete the old columns:

public override void Up()
{
    DropForeignKey("dbo.GroupCourses", "Course_Id", "dbo.Courses");
    DropIndex("dbo.GroupCourses", new[] { "Course_Id" });
    DropPrimaryKey("dbo.Courses");
    DropPrimaryKey("dbo.GroupCourses");

    //Removed: AlterColumn("dbo.Courses", "Id", c => c.Int(nullable: false, identity: true));
    RenameColumn("dbo.Courses", "Id", "Id_Old"); //Added
    AddColumn("dbo.Courses", "Id", c => c.Int(nullable: false, identity: true)); //Added

    //Removed: AlterColumn("dbo.GroupCourses", "Course_Id", c => c.Int(nullable: false));
    RenameColumn("dbo.GroupCourses", "Course_Id", "Course_Id_Old"); //Added
    AddColumn("dbo.GroupCourses", "Course_Id", c => c.Int(nullable: false)); //Added
    Sql(@"UPDATE gc SET gc.Course_Id = c.Id " 
            + "FROM dbo.GroupCourses as gc " 
            + "INNER JOIN dbo.Courses as c ON gc.Course_Id_Old = c.id_Old"); //Added

    DropColumn("dbo.GroupCourses", "Course_Id_Old"); //Added
    DropColumn("dbo.Courses", "Id_Old");    //Added

    AddPrimaryKey("dbo.Courses", "Id");
    AddPrimaryKey("dbo.GroupCourses", new[] { "Group_Id", "Course_Id" });
    CreateIndex("dbo.GroupCourses", "Course_Id");
    AddForeignKey("dbo.GroupCourses", "Course_Id", "dbo.Courses", "Id", cascadeDelete: true);
}

I marked in the code the lines I changed with the comments //Added and //Removed. The rest of the code is the original one in the migration as generated with Add-Migration after changing the property type to int in the model.

You only have to fill with an UPDATE the values in the column in the relationship table. The values in Courses.Id column will be auto-generated, as it is an identity column.

You should also figure out the Down() method and implement the reverse process, or leave it as it is if you really are never going to downgrade your database to a previous migration.

You can also do this by splitting the code between the migration and the seeder. It would be more correct conceptually, as you are supposed to use the migrations for schema modifications and the seeder for data modifications. But I think it is easier this way, and you need only one migration, while with the seeder approach you would need two migrations: one with all the code included above except the UPDATE and the sentences that drop the _Old columns, and another one just with the dropping of the _Old columns. The UPDATE part would be implemented in the seeder.

A warning: the SQL code included in the UPDATE sentence works with Sql Server but might not work with other database engines. It is another downside of using the migration instead of the seeder.

3
11/9/2016 8:17:15 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