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

Question

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 lt FileStore gt    Property x   gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt Carrier gt        public CarrierMap                 HasKey p   gt  p Id            Property p   gt  p Id  HasDatabaseGeneratedOption DatabaseGeneratedOption Identity            Property p   gt  p Code               HasMaxLength 4               IsRequired                HasColumnAnnotation  Index   new IndexAnnotation new IndexAttribute   IsClustered   true  IsUnique   true               Property p   gt  p Name  HasMaxLength 255  IsRequired            Property p   gt  p Created  HasPrecision 7  IsRequired            Property p   gt  p Modified               HasColumnAnnotation  IX Modified   new IndexAnnotation new IndexAttribute                  HasPrecision 7               IsRequired            Property p   gt  p CreatedBy  HasMaxLength 50  IsRequired            Property p   gt  p ModifiedBy  HasMaxLength 50  IsRequired            Property p   gt  p Version  IsRowVersion              This creates an Up method in the initial DbMigration when you execute add-migration like this          CreateTable               scoFreightRate Carrier               c   gt  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 lt string  AnnotationValues gt                                                                                            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   gt  t Id               Index t   gt  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

User · Answer

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

User · Answer

If you do Code-First and already have a Database   public override void Up         AlterColumn  dbo MyTable   Id   c   gt   c Guid nullable  false  identity  true  defaultValueSql   newsequentialid

User · Answer

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

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

Examples related to c#

Examples related to entity-framework

Examples related to sql-server-2008

Examples related to entity-framework-6