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?

Yahoo Serious · Answer 1 · 2022-12-09T16:18:28.580

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

score 3 · Answer 2 · answered Apr 24 '21 at 06:52

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

This solution was working for me. I had to use defaultValueSql instead of defaultValue because the column type is "uniqueidentifier". Thank you. — Jacko07, Aug 20 '21 at 10:02

Julius Depulla · Answer 3 · 2019-02-16T14:36:36.823

1

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()));

edited Feb 16 '19 at 14:36

answered Mar 22 '16 at 16:53

Julius Depulla

1,493
1
12
27

I don't need to create a new Guid. I need to use that value. The new field is a Foreign Key and that is the value in the other table that want to set as the default value. – djblois Mar 22 '16 at 17:05

score 0 · Answer 4 · answered Mar 22 '16 at 15:48

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.

Pedro Aren't Guids non nullable by default and since their exists records already in the database - by adding a non-nullable record wouldn't it create this error? because all the records already in the table would fail this requirement? — djblois, Mar 22 '16 at 15:51

Adding Default Value for New Guid Column in Entity First Migration

4 Answers4