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#. Which is why C# does also provides SqlGuid), so maybe this should be:

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

  FWIW: sequential Guids often may make a better clustered index, but note the disadvantages:

  • they may be guessed
  • they are only locally sequential