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.DropForeignKey(
name: "FK_ContentTableName_MimeType_MimeTypeId",
table: "ContentTableName");
migrationBuilder.DropPrimaryKey(
"PK_MimeType"
, "MimeType");
migrationBuilder.DropIndex(
"IX_ContentTableName_MimeTypeId"
, "ContentTableName");
migrationBuilder.RenameColumn(
"Id"
, "MimeType"
, "OldId");
migrationBuilder.AddColumn<Guid>(
"Id"
, "MimeType"
, "uniqueidentifier"
, defaultValue: "DF_MimeType_Id"
, defaultValueSql: "newid()");
migrationBuilder.RenameColumn(
"MimeTypeId"
, "ContentTableName"
, "OldMimeTypeId");
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.AddPrimaryKey(
"PK_MimeType"
, "MimeType",
"Id");
migrationBuilder.AddForeignKey(
name: "FK_ContentTableName_MimeType_MimeTypeId",
table: "ContentTableName",
column: "MimeTypeId",
principalTable: "MimeType",
principalColumn: "Id",
onDelete: ReferentialAction.Restrict);
migrationBuilder.CreateIndex(
name: "IX_ContentTableName_MimeTypeId",
table: "ContentTableName",
column: "MimeTypeId");
migrationBuilder.DropColumn(
"OldId"
, "MimeType");
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