How can I change int id column to jump using EF?

Question

I am using EF code — the first approach and want to change the Id field to guid , but it seems it cannot go below the 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 needed my Id column to be a GUID, so I changed the 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 you start 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 could be happening?

Answer 1

This was because it was not possible to convert the previous int type of type Id to the Guid column (exactly what the AlterColumn method is trying to execute). In addition, the error message tells you that the new type of the Id column can be one of the types from the set: int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, they can be converted from int .

Solution - just release the Id column and then recreate it with a new Guid type, change the migration as follows:

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

PS Why are you using the DatabaseGeneratedOption.Computed attribute rather than DatabaseGeneratedOption.Identity ?

Answer 2

Despite the fact that Slava Utesinov works, it works only on empty tables or in cases where other tables do not refer to the table you are converting. So this answer will help those who end up on this page with a more complex database setup.

The following is a utility function that you can use in your migration class, which should be called from the Up / Down functions. The function also processes tables that reference the table that you are trying to convert from Int to Guid. This helper function assumes that the column you are converting is called "Id", but otherwise should be fairly general.

 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}sc inner join {parent}sp 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 will be:

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