Adding Default Value for New Guid Column in Entity First Migration

Question

I am using Entity code first-migrations for my project. I already have the system up and running. However, I need to Add a new Guid column which is a foreign key. While trying to update-Database, I receive the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.Categories_dbo.aspnet_Roles_RoleId". The conflict occurred in database "HelpDesk", table "dbo.aspnet_Roles", column 'RoleId'.

So I did some research and found Entity Framework 6 Code first Default value. However, I cannot figure out how to get it to set a default value for the Guid. Here is the code I tried:

Here is the migration:

public override void Up()
{

     AddColumn("dbo.Categories", "RoleId", c => c.Guid(nullable: false, defaultValue: "4468ACB7-AD6F-471E-95CF-615115EA3A76"));
     CreateIndex("dbo.Categories", "RoleId");
     AddForeignKey("dbo.Categories", "RoleId", "dbo.aspnet_Roles", "RoleId");
}

public override void Down()
{
     DropForeignKey("dbo.Categories", "RoleId", "dbo.aspnet_Roles");
     DropIndex("dbo.Categories", new[] { "RoleId" });
     DropColumn("dbo.Categories", "RoleId");
}

I am able to get rid of all the build errors if I switch to this code (but still gives me that Alter Table error if I run Update-database:

AddColumn("dbo.Categories", "RoleId", c => c.Guid(nullable: false, identity: false, defaultValue: null));

How do I convert this to add a specific Guid as the default value?

Answer 1

I suppose you already figured it out, but I think (untested):

• This should work for a constant (fixed) C#-Guid-value:

  AddColumn("dbo.Categories", "RoleId", c => c.Guid(nullable: false, defaultValue: new Guid("4468ACB7-AD6F-471E-95CF-615115EA3A76")));
  

• This should work for a constant (fixed) C#-string-value using defaultValueSql:

  AddColumn("dbo.Categories", "RoleId", c => c.Guid(nullable: false, defaultValueSql: "4468ACB7-AD6F-471E-95CF-615115EA3A76")));
  

• And for others (like me) looking for a varying, unique value (different per table row), determined by SqlServer, you may want to use defaultValueSql: "NewId()" (inspired by this answer):

   AddColumn("dbo.Categories", "RoleId", c => c.Guid(nullable: false, defaultValueSql: "NewId()")));
  

• Edit: untested, but probably also possible is a varying, unique, sequential value (different per table row), determined by SqlServer, by using defaultValueSql: "newsequentialid()" (inspired by this answer):

   AddColumn("dbo.Categories", "RoleId", c => c.Guid(nullable: false, defaultValueSql: "newsequentialid()")));
  

  But note that SqlServer increases/sorts the sequence different from C#.

Answer 2

Using the Entity Framework Migration Builder:

The defaultValue set the PK to a single new Guid on my first run. Using defaultValueSql: "NewId()" instead worked perfectly for my purposes.

protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropUniqueConstraint("PK_Payments", "Payments");

            migrationBuilder.DropColumn(
                name: "PaymentId",
                table: "Payments");

            migrationBuilder.AddColumn<Guid>(
                name: "PaymentId",
                table: "Payments",
                type: "uniqueidentifier",
                defaultValueSql: "NewId()",
                nullable: false);
}

Answer 3

From the error, I supposed you copied this Guid value "4468ACB7-AD6F-471E-95CF-615115EA3A76" from the database and wanted to use it for a test purpose and EF noticed the value already exist in a table and is complaining. It is also preventing you from using null, because you told it to prevent nulls,nullable: false. So it is expecting you to provide it with a value. You can create a new Guid and use it. See below

  AddColumn("dbo.Categories", "RoleId", c => c.Guid(nullable: false, defaultValue: Guid.NewGuid().ToString()));

Answer 4

Could it be that your database initializer is using the default initializer (CreateDatabaseIfNotExists)? Alterations to those may sometimes lead to similar issues.

This may not be your final answer, but it could be related to the problem. I've attached a link to a decent article on database Initializers, it may help you isolate your problem.

http://www.codeguru.com/csharp/article.php/c19999/Understanding-Database-Initializers-in-Entity-Framework-Code-First.htm

From what I can tell, your code doesen't seem to be the immediate cause of the issue, it looks more like the migration script being generated that is causing problems.