How can I change an int ID column to Guid with EF migration?

user_1942551 0
Viewed 18454

I'm using EF code-first approach and want to change the Id field to guid but can't seem to get past below error.

This is my first migration:

public partial class CreateDownloadToken : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.DownloadTokens",
            c => new
            {
                Id = c.Int(nullable: false, identity: true),
                FileId = c.Int(),
                UserId = c.String(nullable: false, maxLength: 128),
                ValidUntil = c.DateTime(nullable: false),
            })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Files", t => t.FileId)
            .ForeignKey("dbo.Users", t => t.UserId, cascadeDelete: true)
            .Index(t => t.FileId)
            .Index(t => t.UserId);

    }

    public override void Down()
    {
        DropForeignKey("dbo.DownloadTokens", "UserId", "dbo.Users");
        DropForeignKey("dbo.DownloadTokens", "FileId", "dbo.Files");
        DropIndex("dbo.DownloadTokens", new[] { "UserId" });
        DropIndex("dbo.DownloadTokens", new[] { "FileId" });
        DropTable("dbo.DownloadTokens");
    }
}

Later I realized that I need my Id column to be GUID so I changed my model file: 

public class DownloadToken
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public Guid Id { get; set; }

    public int? FileId { get; set; }

    [ForeignKey("FileId")]
    public virtual File File { get; set; }

    [Required]
    public string UserId { get; set; }

    [ForeignKey("UserId")]
    public virtual User User { get; set; }

    [Required]
    public DateTime ValidUntil { get; set; }
}

When running Add-Migration ChangeDownloadTokenIdToGuid it generates this file:

public partial class ChangeDownloadTokenIdToGuid : DbMigration
{
    public override void Up()
    {
        DropPrimaryKey("dbo.DownloadTokens");
        AlterColumn("dbo.DownloadTokens", "Id", c => c.Guid(nullable: false));
        AddPrimaryKey("dbo.DownloadTokens", "Id");
    }

    public override void Down()
    {
        DropPrimaryKey("dbo.DownloadTokens");
        AlterColumn("dbo.DownloadTokens", "Id", c => c.Int(nullable: false, identity: true));
        AddPrimaryKey("dbo.DownloadTokens", "Id");
    }
}

Running this file with Update-Database causes this error:

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

Any ideas why this might be happening?

entity-framework-migrations entity-framework asp.net c#
4 Answers

I know this is EF but for anyone stumbling on this with EF Core in 2021 like me, this is what I did to adapt Andreas Willadsen's code:

protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.AddColumn<Guid>("Id2", "{Parent}s", nullable: false);
migrationBuilder.DropForeignKey("FK_{Child}s_{Parent}s_{Parent}Id", "{Child}s");
migrationBuilder.DropIndex("IX_{Child}s_{Parent}Id", "{Child}s");

//if FK reference exists in child you can wipe the child table or..... instead of drop + add column below, need to call renamecolumn, then addcolumn, then use Andreas' post with migrationBuilder.SQL() and update to convert old keys to guid, then finally drop column.
migrationBuilder.DropColumn("{Parent}Id", "{Child}s");
migrationBuilder.AddColumn<Guid>("{Parent}Id", "{Child}s", nullable: false);
//

migrationBuilder.DropPrimaryKey("PK_{Parent}s", "{Parent}s");
migrationBuilder.DropColumn("Id", "{Parent}s");
migrationBuilder.RenameColumn("Id2", "{Parent}s", "Id");
migrationBuilder.AddPrimaryKey("PK_{Parent}s", "{Parent}s", column: "Id");

migrationBuilder.CreateIndex("IX_{Child}s_{Parent}Id", "{Child}s","{Parent}Id");
migrationBuilder.AddForeignKey(
    name: "FK_{Child}s_{Parent}s_{Parent}Id",
    table: "{Child}s",
    column: "{Parent}Id",
    principalTable: "{Parent}s",
    principalColumn: "Id",
    onDelete: ReferentialAction.Cascade);
}

Replace {Parent} with your parent table name, {Child} with your child table name, and call the same thing in your Down() except swap <Guid> with <int>.
I want to know why ef core hasn't automated this yet in add-migration.

user_63775690

This is what I used for a full table in a one to many relationship. newid only works for sql server and Azure. The down does not work with this method.

public void ConvertIntIdToGuidId (string tableName, MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<Guid>("Id2", $"{tableName}", nullable: true, defaultValue: new Guid("00000000-0000-0000-0000-000000000000"));
    migrationBuilder.DropPrimaryKey($"PK_{tableName}", $"{tableName}");
    migrationBuilder.DropColumn("Id", $"{tableName}");
    migrationBuilder.RenameColumn("Id2", $"{tableName}", "Id");

    migrationBuilder.Sql(
@$"UPDATE {tableName} SET Id = NEWID () ;");

    migrationBuilder.AlterColumn<Guid>(name: "Id",table: $"{tableName}", nullable: false, oldNullable: true);
    migrationBuilder.AddPrimaryKey($"PK_{tableName}", $"{tableName}", column: "Id");
}
user_184121470
Related