Code First migration from int to Guid primary key issue

Question

I'm trying to change my code first ID column from 'int' to 'Guid', and when trying to run the migration, I get the message:

Identity column 'CustomFieldId' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

I'm defining the column like this:

public partial class CustomField : BaseEntity
{

    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid CustomFieldId { get; set; }

Mapping it in CustomFieldMapping.cs like this:

public CustomFieldMapping()
{
    //Primary key
    HasKey(t => t.CustomFieldId);

    //Constraints 
    Property(t => t.CustomFieldId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

And the migration that's generated is trying to do this:

public override void Up()
{
    DropForeignKey("dbo.CustomField", "CustomFormId", "dbo.CustomForm");
    DropForeignKey("dbo.CustomData", "CustomFieldId", "dbo.CustomField");
    DropForeignKey("dbo.CustomForm", "ParentFormId", "dbo.CustomForm");
    DropIndex("dbo.CustomField", new[] { "CustomFormId" });
    DropIndex("dbo.CustomForm", new[] { "ParentFormId" });
    DropIndex("dbo.CustomData", new[] { "CustomFieldId" });
    DropPrimaryKey("dbo.CustomField");
    DropPrimaryKey("dbo.CustomForm");
    AlterColumn("dbo.CustomField", "CustomFieldId", c => c.Guid(nullable: false));
    AlterColumn("dbo.CustomField", "SortOrder", c => c.Int(nullable: false));
    AlterColumn("dbo.CustomForm", "CustomFormId", c => c.Guid(nullable: false));
    AlterColumn("dbo.CustomForm", "ParentFormId", c => c.Guid());
    AddPrimaryKey("dbo.CustomField", "CustomFieldId");
    AddPrimaryKey("dbo.CustomForm", "CustomFormId");
    CreateIndex("dbo.CustomField", "CustomForm_CustomFormId");
    CreateIndex("dbo.CustomForm", "ParentFormId");
    CreateIndex("dbo.CustomData", "CustomField_CustomFieldId");
    AddForeignKey("dbo.CustomField", "CustomForm_CustomFormId", "dbo.CustomForm", "CustomFormId");
    AddForeignKey("dbo.CustomData", "CustomField_CustomFieldId", "dbo.CustomField", "CustomFieldId");
    AddForeignKey("dbo.CustomForm", "ParentFormId", "dbo.CustomForm", "CustomFormId");

I would like it to be a sequentially incremented Guid. What am I doing wrong?

Answer 1

To solve this problem I used a Sql() method in the Up() and Down() methods of the migration class. The SQL command string in the Up() method removes the primary key constraint on the ID column, drops the ID column of type int and then adds a new ID column with of type Guid. The Down() method does the same thing but drops the Guid column and adds a new int column.

I found a few solutions on Stack Overflow that resolve the "change column type" by running a SQL command in a query window. To address your comment:

We're just trying to keep a clean/clear migration path to trace when we did what which is not always easy with SQL.

I used SQL commands within the Up() and Down() migration methods. For me this solution works well in my projects.

The solution at the bottom of this answer was constructed from several Stack Overflow questions/answers. Skip to that for just the code. Here are the long-winded details.

Using SQL commands in a migration class

I couldn't find a solution that used Entity Framework migration methods like AlterColumn() and DropColumn() ONLY.

Rather than using a mix of migration methods and commands in the Sql() method, I used all SQL commands within a string in the Sql() migration method. Using all SQL commands made it easier to test in a query window in Visual Studio or SQL Server Management Studio.

The answer by 'Uchitha' gave the starting steps for adding the Sql() "method within the desired migration class."

1. Generate migration class using Add-Migration
2. Alter the class using code similar to above
3. Run the migration using Update-Database

The Sql() method sample in the answer looks like:

Sql("UPDATE dbo.YourTable SET Column1 = 'VALUE1' "); 

Changing the column type - generic steps

I used the answer by 'JustAnotherUserYouMayKnow' to get started on the steps to change the column type. I didn't follow this explicitly but it provided just the basic framework of the need to drop a column and recreating it.

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

Sequential GUIDs

The answer from 'Icarus' provided the ALTER TABLE statement with the use of newsequentialid() to generate sequential GUIDs as per your statement:

I would like it to be a sequentially incremented Guid.

ALTER TABLE your_table
    ADD your_column UNIQUEIDENTIFIER DEFAULT newsequentialid() NOT null

Take note of privacy concerns by 'Johan' in the comment section of the answer by 'Icarus':

If privacy is a concern, do not use newsequentialid(). It is possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID

Alter primary key

The column you want to change is an ID column and you've set it as the primary key. Therefore, before dropping the existing ID column you'll need to remove the primary key using another ALTER TABLE SQL command.

See the selected answer from 'darnir' for "How can I alter a primary key constraint using SQL syntax?"

ALTER TABLE <Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Table_Name>
ADD CONSTRAINT <constraint_name> PRIMARY KEY (<Column1>,<Column2>)

See the note by 'Oleg' to determine if this will be a factor:

PRIMARY KEY CONSTRAINT cannot be altered, you may only drop it and create again. For big datasets it can cause a long run time and thus - table inavailability.

I had problems when the command with DROP CONSTRAINT above was executed. The results pane listed a constraint that was auto-generated even though I'd used a specific constraint name in the ALTER TABLE ... ADD COLUMN command. See this question "Why does SQL keep creating a DF constraint?" and this question if you experience something similar.

To fix the problem with dropping the constraint I used the answer by 'ScubaSteve' from this question: "How to drop SQL default constraint without knowing its name?" With the addition of the note by 'Seven' here are the SQL commands:

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[tableSchema].[tableName]') AND [name] = 'columnName';
IF @ObjectName IS NOT NULL EXEC('ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT ' + @ObjectName)

The comment by 'Seven' in 'ScubaSteve's answer. I added the 'if' condition as at times the EXEC would fail when no constraint was found.

To make this script idempotent add IF @ObjectName IS NOT NULL before EXEC command

The final solution

Make sure to replace MyTableName, MyColumnName, and dbo in the code below to your table name, column name (e.g. set column name to Id) and table schema respectively.

public override void Up()
{
    Sql(@"
        DECLARE @ObjectName NVARCHAR(100)
        SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
        WHERE [object_id] = OBJECT_ID('[dbo].[MyTableName]') AND [name] = 'MyColumnName';
        IF @ObjectName IS NOT NULL EXEC('ALTER TABLE [dbo].[MyTableName] DROP CONSTRAINT ' + @ObjectName)

        ALTER TABLE dbo.MyTableName DROP CONSTRAINT PK_MyTableName, COLUMN MyColumnName

        ALTER TABLE dbo.MyTableName
        ADD Id UNIQUEIDENTIFIER DEFAULT (newsequentialid()) NOT NULL
        CONSTRAINT PK_MyTableName
        PRIMARY KEY CLUSTERED ([MyColumnName])
    ");
}

public override void Down()
{
    Sql(@"
        DECLARE @ObjectName NVARCHAR(100)
        SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
        WHERE [object_id] = OBJECT_ID('[dbo].[MyTableName]') AND [name] = 'MyColumnName';
        IF @ObjectName IS NOT NULL EXEC('ALTER TABLE [dbo].[MyTableName] DROP CONSTRAINT ' + @ObjectName)

        ALTER TABLE dbo.MyTableName DROP CONSTRAINT PK_MyTableName, COLUMN Id

        ALTER TABLE MyTableName
        ADD MyColumnName int IDENTITY(1, 1) NOT NULL
        CONSTRAINT PK_MyTableName
        PRIMARY KEY CLUSTERED ([MyColumnName] ASC)        
    ");
}