c# - Entity Framework - ColumnBuilder

Question

I have been trying to research this for a few hours, there is not a lot of good documentation and examples on this and I was hoping someone could provide some better understanding.

I have a database table that I am using Entity Framework Migrations on. The previous versions of this database utilized "INT32" as the primary keys for the table. We decided as a team that rather than trying to manage this for a large database, we would rather expand the amount of ID's we can hold to be a BIGINT.

I created a new migration and I am attempting to update the table to now hold the BIGINT value.

Here is the code I am utilizing to change this migration:

DropForeignKey("parts", "FK_Parts_Bins_BinID");
AlterColumn("bins", "ID", c => c.Long(nullable: false, identity: true));
AlterColumn("parts", "BinID", c => c.Long());
AddForeignKey("parts", "BinID", "bins", "ID", false, "FK_Parts_Bins_BinID"); 

I have a table noted as 'Parts' which holds a foreign key reference to the primary key 'ID' in the 'Bins' table. Before these were just ints, now I want to utilize BigINTs.

I believe I have 2 problems:

1. I cannot re-establish the connection between Bins and Parts because when I am creating the new 'identity' column the bins ID field is getting marked as 'unsigned' where my parts BinID column is not. So I get the following exception:

   Referencing column 'BinID' and referenced column 'ID' in foreign key constraint 'FK_Parts_Bins_BinID' are incompatible.

I cannot figure out how to make my BinID column unsigned.

2. I believe after I fix the above exception I may have an issue with the auto-increment field resetting back to 0 or 1. This would mean the existing data ID's would not be accounted for. Is there a best practice for updating the starting position to be the largest ID in the existing table?

Microsoft Documentation:

https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.migrations.builders.columnbuilder.long?view=entity-framework-6.2.0

question from:https://stackoverflow.com/questions/65921785/entity-framework-columnbuilder

Answer 1

So I am not sure if this is the best solution, so I won't be marking this as best answer, hopefully someone with more knowledge of Entity Framework can reply with a better answer... but I did manage to get this to work - and solved both of my problems (1 & 2)

This is what I did:

DropForeignKey("parts", "FK_Parts_Bins_BinID");
Sql("ALTER TABLE bins MODIFY COLUMN ID BIGINT NOT NULL AUTO_INCREMENT");
Sql("ALTER TABLE parts MODIFY COLUMN BinID BIGINT");
//AlterColumn("bins", "ID", c => c.Long(nullable: false, identity: true));
//AlterColumn("parts", "BinID", c => c.Long());
AddForeignKey("parts", "BinID", "bins", "ID", false, "FK_Parts_Bins_BinID");

Since the entity framework requires the identity to be set to true or false, by default it appears 'identity' forces the value to be assigned unsigned but also forces the auto-increment to reset back to 0.

This solution does not reset the auto-increment, and I can control the value that gets assigned.