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

Question

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?

Answer 1

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");
}

Answer 2

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.

Answer 3

Even though Slava Utesinov's works, it only works on empty tables or in cases where no other tables are referring to the table you're converting. So this answer will help those ending up on this page with a more complex database setup.

Below is a utility function you can use from your migration class, which should be invoked from the Up/Down functions. The function also handles tables references the table you're trying to convert from Int to Guid. This helper function assumes the column you're converting is called 'Id', but should otherwise be rather generic.

public void Convert(bool toGuid, string parent, params string[] children)
    {
        if (toGuid)
        {
            AddColumn($"dbo.{parent}s", "Id2", c => c.Guid(nullable: false, identity: true, defaultValueSql: "newid()"));
        }
        else
        {
            AddColumn($"dbo.{parent}s", "Id2", c => c.Int(nullable: false, identity: true));
        }
        foreach (var child in children)
        {
            DropForeignKey($"dbo.{child}s", $"{parent}_Id", $"dbo.{parent}s");
            DropIndex($"dbo.{child}s", new[] { $"{parent}_Id" });
            RenameColumn($"dbo.{child}s", $"{parent}_Id", $"old_{parent}_Id");
            if (toGuid)
            {
                AddColumn($"dbo.{child}s", $"{parent}_Id", c => c.Guid());
            }
            else
            {
                AddColumn($"dbo.{child}s", $"{parent}_Id", c => c.Int());
            }
            Sql($"update c set {parent}_Id=p.Id2 from {child}s c inner join {parent}s p on p.Id=c.old_{parent}_Id");
            DropColumn($"dbo.{child}s", $"old_{parent}_Id");
        }
        DropPrimaryKey($"dbo.{parent}s");
        DropColumn($"dbo.{parent}s", "Id");
        RenameColumn($"dbo.{parent}s", "Id2", "Id");
        AddPrimaryKey($"dbo.{parent}s", "Id");
        foreach (var child in children)
        {
            CreateIndex($"dbo.{child}s", $"{parent}_Id");
            AddForeignKey($"dbo.{child}s", $"{parent}_Id", $"dbo.{parent}s", "Id");
        }
    }

So in your case your Up/Down functions would be:

    public override void Up()
    {
        Convert(true,"DownloadToken");
    }

    public override void Down()
    {
        Convert(false, "DownloadToken");
    }

Answer 4

It was caused because it is impossible to convert previous int type of Id column to Guid type(exactly that try to perform AlterColumn method). Also, error message suggest you, that new type of Id column can be one of type from set: int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, for them, it is possible to perform conversion from int type.

Solution - simply drop Id column and then recreate it with new Guid type, change migration that way:

public partial class ChangeDownloadTokenIdToGuid : DbMigration
{
    public override void Up()
    {
        DropPrimaryKey("dbo.DownloadTokens");

        DropColumn("dbo.DownloadTokens", "Id");
        AddColumn("dbo.DownloadTokens", "Id", c => c.Guid(nullable: false, identity: true));

        AddPrimaryKey("dbo.DownloadTokens", "Id");
    }

    public override void Down()
    {
        DropPrimaryKey("dbo.DownloadTokens");

        DropColumn("dbo.DownloadTokens", "Id");
        AddColumn("dbo.DownloadTokens", "Id", c => c.Int(nullable: false, identity: true));

        AddPrimaryKey("dbo.DownloadTokens", "Id");
    }
}

P.S. Why you use DatabaseGeneratedOption.Computed attribute, not DatabaseGeneratedOption.Identity?