Entity Framework 6 GUID as primary key: Cannot insert the value NULL into column 'Id', table 'FileStore'; column does not allow nulls

I have an entity with primary key "Id" which is Guid:

public class FileStore
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Path { get; set; }
}

And some configuration:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<FileStore>().Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    base.OnModelCreating(modelBuilder);
}

When I try to insert a record I get a following error:

> Cannot insert the value NULL into column 'Id', table 'FileStore'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated.

I don't want to generate Guid manually. I just want to insert a record and get Id generated by SQL Server. If I set .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity), Id column is not Identity column in SQL Server.

How can I configure Entity Framework to autogenerate Guid in SQL Server?

In addition to adding these attributes to your Id column:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }

in your migration you should change your CreateTable to add the defaultValueSQL property to your column i.e.:

Id = c.Guid(nullable: false, identity: true, defaultValueSql: "newsequentialid()"),

This will prevent you from having to manually touch your database which, as you pointed out in the comments, is something you want to avoid with Code First.

try this :

public class FileStore
 {
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public Guid Id { get; set; }
   public string Name { get; set; }
   public string Path { get; set; }
 }

You can check this SO post.

You can set the default value of your Id in your db to newsequentialid() or newid(). Then the identity configuration of EF should work.

This works for me (no Azure), SQL 2008 R2 on dev server or localdb\mssqllocaldb on local workstation. Note: entity adds Create, CreateBy, Modified, ModifiedBy and Version columns.

public class Carrier : Entity
{
    public Guid Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
}

then create a mapping configuration class

public class CarrierMap : EntityTypeConfiguration<Carrier>
{
    public CarrierMap()
    {
        HasKey(p => p.Id);

        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        Property(p => p.Code)
            .HasMaxLength(4)
            .IsRequired()
            .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute { IsClustered = true, IsUnique = true }));

        Property(p => p.Name).HasMaxLength(255).IsRequired();
        Property(p => p.Created).HasPrecision(7).IsRequired();
        Property(p => p.Modified)
            .HasColumnAnnotation("IX_Modified", new IndexAnnotation(new IndexAttribute()))
            .HasPrecision(7)
            .IsRequired();
        Property(p => p.CreatedBy).HasMaxLength(50).IsRequired();
        Property(p => p.ModifiedBy).HasMaxLength(50).IsRequired();
        Property(p => p.Version).IsRowVersion();
    }
}

This creates an Up method in the initial DbMigration when you execute add-migration like this

        CreateTable(
            "scoFreightRate.Carrier",
            c => new
                {
                    Id = c.Guid(nullable: false, identity: true),
                    Code = c.String(nullable: false, maxLength: 4),
                    Name = c.String(nullable: false, maxLength: 255),
                    Created = c.DateTimeOffset(nullable: false, precision: 7),
                    CreatedBy = c.String(nullable: false, maxLength: 50),
                    Modified = c.DateTimeOffset(nullable: false, precision: 7,
                        annotations: new Dictionary<string, AnnotationValues>
                        {
                            { 
                                "IX_Modified",
                                new AnnotationValues(oldValue: null, newValue: "IndexAnnotation: { }")
                            },
                        }),
                    ModifiedBy = c.String(nullable: false, maxLength: 50),
                    Version = c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"),
                })
            .PrimaryKey(t => t.Id)
            .Index(t => t.Code, unique: true, clustered: true);

Note: that the Id columns does not get a default value, don't worry

Now execute Update-Database, and you should end up with a table definition in your database like this:

CREATE TABLE [scoFreightRate].[Carrier] (
	[Id]         UNIQUEIDENTIFIER   DEFAULT (newsequentialid()) NOT NULL,
	[Code]       NVARCHAR (4)       NOT NULL,
	[Name]       NVARCHAR (255)     NOT NULL,
	[Created]    DATETIMEOFFSET (7) NOT NULL,
	[CreatedBy]  NVARCHAR (50)      NOT NULL,
	[Modified]   DATETIMEOFFSET (7) NOT NULL,
	[ModifiedBy] NVARCHAR (50)      NOT NULL,
	[Version]    ROWVERSION         NOT NULL,
	CONSTRAINT [PK_scoFreightRate.Carrier] PRIMARY KEY NONCLUSTERED ([Id] ASC)
);


GO
CREATE UNIQUE CLUSTERED INDEX [IX_Code]
	ON [scoFreightRate].[Carrier]([Code] ASC);

Note: we have a overridden the SqlServerMigrationSqlGenerator to ensure it does NOT make the Primary Key a Clustered index as we encourage our developers to set a better clustered index on tables

public class OurMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(AddPrimaryKeyOperation addPrimaryKeyOperation)
    {
        if (addPrimaryKeyOperation == null) throw new ArgumentNullException("addPrimaryKeyOperation");
        if (!addPrimaryKeyOperation.Table.Contains("__MigrationHistory"))
            addPrimaryKeyOperation.IsClustered = false;
        base.Generate(addPrimaryKeyOperation);
    }

    protected override void Generate(CreateTableOperation createTableOperation)
    {
        if (createTableOperation == null) throw new ArgumentNullException("createTableOperation");
        if (!createTableOperation.Name.Contains("__MigrationHistory"))
            createTableOperation.PrimaryKey.IsClustered = false;
        base.Generate(createTableOperation);
    }

    protected override void Generate(MoveTableOperation moveTableOperation)
    {
        if (moveTableOperation == null) throw new ArgumentNullException("moveTableOperation");
        if (!moveTableOperation.CreateTableOperation.Name.Contains("__MigrationHistory")) moveTableOperation.CreateTableOperation.PrimaryKey.IsClustered = false;
        base.Generate(moveTableOperation);
    }
}

It happened to me before.

When the table has been created and I added in .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) later, the code migration somehow could not assign default value for the Guid column.

The fix:

All we need is to go to the database, select the Id column and add newsequentialid() manually into Default Value or Binding.

No need to update dbo.__MigrationHistory table.

Hope it helps.

---

The solution of adding New Guid() is generally not preferred, because in theory there is possibility that you might get a duplicate accidentally.

---

And you shouldn't worry about directly editing in the database. All Entity Framework do is automate part of our database work.

Translating

.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)

into

[Id] [uniqueidentifier] NOT NULL DEFAULT newsequentialid(),

If somehow our EF missed one thing and did not add in the default value for us, just go ahead and add it manually.

Entity Framework – Use a Guid as the primary key

Using a Guid as your tables primary key, when using Entity Framework, requires a little more effort than when using a integer. The setup process is straightforward, after you’ve read/been shown how to do it.

The process is slightly different for the Code First and Database First approaches. This post discusses both techniques.

enter image description here

Code First

Using a Guid as the primary key when taking the code first approach is simple. When creating your entity, add the DatabaseGenerated attribute to your primary key property, as shown below;

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }

Entity framework will create the column as you would expect, with a primary key and uniqueidentifier data type.

codefirst-defaultvalue

Also notice, very important, that the default value on the column has been set to (newsequentialid()). This generates a new sequential (continuous) Guid for each row. If you were so inclined, you could change this to newid()), which would result in a completely random Guid for each new row. This will be cleared each time your database gets dropped and re-created, so this works better when taking the Database First approach.

Database First

The database first approach follows a similar line to the code first approach, but you’ll have to manually edit your model to make it work.

Ensure that you edit the primary key column and add the (newsequentialid()) or (newid()) function as the default value before doing anything.

enter image description here

Next, open you EDMX diagram, select the appropriate property and open the properties window. Ensure that StoreGeneratedPattern is set to identity.

databasefirst-model

No need to give your entity an ID in your code, that will be populated for you automatically after the entity has been commited to the database;

using (ApplicationDbContext context = new ApplicationDbContext())
{
    var person = new Person
                     {
                         FirstName = "Random",
                         LastName = "Person";
                     };

    context.People.Add(person);
    context.SaveChanges();
    Console.WriteLine(person.Id);
}

Important Note: Your Guid field MUST be a primary key, or this does not work. Entity Framework will give you a rather cryptic error message!

Summary

Guid (Globally Unique Identifiers) can easily be used as primary keys in Entity Framework. A little extra effort is required to do this, depending on which approach you are taking. When using the code first approach, add the DatabaseGenerated attribute to your key field. When taking the Database First approach, explicitly set the StoredGeneratedPattern to Identity on your model.

[1]: https://i.stack.imgur.com/IxGdd.png
[2]: https://i.stack.imgur.com/Qssea.png

According to this, DatabaseGeneratedOption.Identity is not detected by a specific migration if it's added after the table has been created, which is the case I run into. So I dropped the database and that specific migration and added a new migration, finally update the database, then everything works as expected. I am using EF 6.1, SQL2014 and VS2013.

If you do Code-First and already have a Database:

public override void Up()
{
    AlterColumn("dbo.MyTable","Id", c =>  c.Guid(nullable: false, identity: true, defaultValueSql: "newsequentialid()"));
}

You can not. You will / do break a lot of things. Like relationships. WHich rely on the number being pulled back which EF can not do in the way you set it up. THe price for breaking every pattern there is.

Generate the GUID in the C# layer, so that relationships can continue working.

And what something like this?

public class Carrier : Entity
{
    public Carrier()
    {
         this.Id = Guid.NewGuid();
    }  
    public Guid Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
}

If you want to automatically generate a compatible migration without use the DataAnnotations, you must add the following in the OnModelCreating method override in the your DbContext class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    //This is implicit when property is called Id or <ClassName>Id
    modelBuilder.Entity<FileStore>(x => x
      .HasKey(f => f.Id)
      .IsClustered());

    modelBuilder.Entity<FileStore>(x => x
        .Property(f => f.Id)
        .IsRequired()                     //Set column as not nullable
        .ValueGeneratedOnAdd()            //Optional (but recommended)
        .HasDefaultValueSql("newid()"));  //Or: "newsequentialid()"
}

In case you want to use an abstract class or interface to share the [(Guid) Id] property across multiple classes...

public interface IEntity
{
  public Guid Id { get; set; }
}

public class FileStore : IEntity
{
  public Guid Id { get; set; }
  public string Name { get; set; }
  public string Path { get; set; }
}

public class FolderStore : IEntity
{
  public Guid Id { get; set; }
  public string Name { get; set; }
}

You can define the same directives in this generic way:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);

  foreach (var t in modelBuilder.Model.GetEntityTypes())
  {
    if (typeof(IEntity).IsAssignableFrom(t.ClrType))
    {
      //This is implicit when property is called Id or <ClassName>Id
      modelBuilder.Entity(t.ClrType, x => x
        .HasKey(nameof(IEntity.Id))
        .IsClustered());

      modelBuilder.Entity(t.ClrType, x => x
        .Property(nameof(IEntity.Id))
        .IsRequired()
        .ValueGeneratedOnAdd()
        .HasDefaultValueSql("newid()"));  //Or: "newsequentialid()"
    }
  }
}