Changing primary key data type in EF Core

Introduction

Simply changing data type on a primary key is not easy; you must go through certain steps.  Once you do follow these steps, it becomes easy.  This article shows what you need to do, so you won't have to figure it out the hard way. 

Background

For reasons beyond the scope of this article, we decided to change the primary key on some tables from int to guid. You can do the same with any columns, but for primary keys it is somewhat different.

Using the code

In TFS, get latest of your EF core project.

In package manager console, do the following to bring the code and data to current.

update-database -c XXEntities

In TFS, check out the file \path\YourDataProject\Migrations\XXEntitiesModelSnapshot.cs

In the  package manager console, create a new package:  

add-migration -c XXEntities NameOfMyUpdate

It will create a new migration, and it will be empty, and the designer will reflect exactly how the fields are in the database right now.  The designer is a .cs file you'll see in project manager beneath your NameOfMyUpdate migration file. 

You'll see an "Up" method, and a "Down" method.   You will be putting code in there to change your keys' data types.

Locate your desired primary key and foreign keys in your designer, and in XXEntitiesModelSnapshot.  Change the data type to your desired type:

b.Property<Guid>("ColumnNameId");

Of course, you also must change it everywhere throughout your app.   Every time you reference that column, change its data type.  

After changing it everywhere, RE CHECK the DESIGNER.  Doing find in all files seems to NOT FIND the keys, and everything seems to go smoothly but still is WRONG because they didn’t get changed here.

In your newly created migration class, in the method “Up”, create all the EF Core migration calls to do the following:

               Drop the indexes and constraints

               Rename Id to Old Id

               Make new Id of the right datatype

               Run an embedded sql statement to update the foreign keys

               Drop old id

               Create new indexes and constraints

I have been somewhat lucky, in my organization, that I do not need to modify the “Down” function to do all this functionality in reverse.  If you have the desire to make your data change BACK to the original data type, you will need to do the opposite in the Down function. 

Build the whole solution. In package manager console, do the following again to invoke your new changes.

update-database -c XXEntities

The following shows example calls for the steps above.  Note I also have the SQL, as comments, to show how it could be done using straight SQL. 

//migrationBuilder.Sql("ALTER TABLE [dbo].[ContentTableName] DROP CONSTRAINT [FK_ContentTableName_MimeType_MimeTypeId]");
migrationBuilder.DropForeignKey(
    name: "FK_ContentTableName_MimeType_MimeTypeId",
    table: "ContentTableName");
 
//migrationBuilder.Sql("ALTER TABLE [dbo].[MimeType] DROP CONSTRAINT [PK_MimeType]");
migrationBuilder.DropPrimaryKey(
    "PK_MimeType"
    , "MimeType");
 
//migrationBuilder.Sql("DROP INDEX [IX_ContentTableName_MimeTypeId] ON [dbo].[ContentTableName]");
migrationBuilder.DropIndex(
    "IX_ContentTableName_MimeTypeId"
    , "ContentTableName");
 
//migrationBuilder.Sql("exec sp_rename 'dbo.MimeType.Id','OldId','COLUMN'");
migrationBuilder.RenameColumn(
    "Id"
    , "MimeType"
    , "OldId");
 
//migrationBuilder.Sql("alter table [dbo].[MimeType] add Id uniqueidentifier not null constraint df_MimeType_Id default(newid())");
migrationBuilder.AddColumn<Guid>(
    "Id"
    , "MimeType"
    , "uniqueidentifier"
    , defaultValue: "DF_MimeType_Id"
    , defaultValueSql: "newid()");
 
//migrationBuilder.Sql("exec sp_rename 'dbo.ContentTableName.MimeTypeId','OldMimeTypeId','COLUMN'");
migrationBuilder.RenameColumn(
    "MimeTypeId"
    , "ContentTableName"
    , "OldMimeTypeId");
 
//migrationBuilder.Sql("alter table dbo.ContentTableName add MimeTypeId uniqueidentifier null");
migrationBuilder.AddColumn<Guid>(
    name: "MimeTypeId",
    table: "ContentTableName",
    nullable: true
    );
 
migrationBuilder.Sql("update [dbo].ContentTableName set MimeTypeId=t.Id FROM[dbo].ContentTableName i inner join dbo.MimeType t on i.OldMimeTypeId = t.OldId");
 
////migrationBuilder.Sql("ALTER TABLE [dbo].[MimeType] ADD  CONSTRAINT [PK_MimeType] PRIMARY KEY CLUSTERED ([Id] ASC) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)");
migrationBuilder.AddPrimaryKey(
    "PK_MimeType"
    , "MimeType",
    "Id");
 
////migrationBuilder.Sql("ALTER TABLE [dbo].[ContentTableName]  WITH CHECK ADD  CONSTRAINT [FK_ContentTableName_MimeType_MimeTypeId] FOREIGN KEY([MimeTypeId]) REFERENCES[dbo].[MimeType]([Id])");
////migrationBuilder.Sql("ALTER TABLE [dbo].[ContentTableName] CHECK CONSTRAINT [FK_ContentTableName_MimeType_MimeTypeId]");
migrationBuilder.AddForeignKey(
            name: "FK_ContentTableName_MimeType_MimeTypeId",
            table: "ContentTableName",
            column: "MimeTypeId",
            principalTable: "MimeType",
            principalColumn: "Id",
            onDelete: ReferentialAction.Restrict);
 
////migrationBuilder.Sql("CREATE NONCLUSTERED INDEX [IX_ContentTableName_MimeTypeId] ON [dbo].[ContentTableName] ([MimeTypeId] ASC ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)");
migrationBuilder.CreateIndex(
    name: "IX_ContentTableName_MimeTypeId",
    table: "ContentTableName",
    column: "MimeTypeId");
 
////migrationBuilder.Sql("ALTER TABLE [dbo].[MimeType] DROP COLUMN [OldId]");
migrationBuilder.DropColumn(
    "OldId"
    , "MimeType");
 
////migrationBuilder.Sql("ALTER TABLE [dbo].ContentTableName DROP COLUMN OldMimeTypeId");
migrationBuilder.DropColumn(
    "OldMimeTypeId"
    , "ContentTableName");

Points of Interest

Want to be SURE you found and changed all?   Do another add-update -C XXEntities NameOfUpdate2. If you did your changes correctly, everywhere, the Up function and Down function will be empty.  If you forgot to change your data type somewhere, these two functions will have a lot of generated code to try to fix your database back to some state.     This extra check pays off in the long run; things get more complicated if you do more EF Core migrations before realizing your error and fixing it later.    

Bad news [or EF Core data type change problems] are not like fine wine; they do not get better with age. 

History

20170621  Initial revision