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

asp.net c# ef-migrations entity-framework

Question

I want to modify the EF code-first method I'm employing.Id area toguid but I am unable to pass the below error.

This is my initial move:

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 understood that I required myId I modified my model file so that column would be a GUID:

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

while movingAdd-Migration ChangeDownloadTokenIdToGuid This file is created by it:

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

Using this document withUpdate-Database causes of this mistake

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 theories as to why this might be occurring?

1
18
3/15/2016 9:50:33 PM

Accepted Answer

It resulted from the inability to convert olderint kind ofId table toGuid (just what you're attempting to do)AlterColumn method). Additionally, error messages hint that a new kind ofId column could be of the following type from the set: with a scale of 0 or as an integer, bigint, smallint, tinyint, decimal, or numeric; for them, conversion fromint type.

Drop everything and SolutionId then re-create it using a newGuid type, modify migration in this manner:

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?

26
3/17/2016 1:52:40 PM

Popular Answer

Slava Utesinov's method is effective, however it only functions with empty tables or when no other tables are referencing the one you're converting. Therefore, individuals who arrive on this page with a more complex database setup will benefit from this response.

You can utilize the migration class's utility function, which is listed below, in the Up/Down functions. The table you're trying to convert from Int to Guid is one that the function also supports. This helper method should be very flexible, however it does presume that the column you're converting is called "Id."

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

Your Up/Down functions would be as follows in your case:

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

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow