Entity Framework 6 GUID as a primary key: cannot insert a NULL value in the "Id" column, the "FileStore" table; column does not allow

Question

I have an object with the 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 kind of 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 the following error:

Cannot insert NULL value in column "Id", table "FileStore"; column does not allow zeros. INSERT fails. \ R \ nThe application has completed.

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

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

Answer 1

In addition to adding these attributes to the identifier column

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

in your migration, you must modify your CreateTable to add the defaultValueSQL property to your ie column:

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

This will not allow you to manually touch your database, which, as you noted in the comments, is what you want to avoid with Code First.

Answer 2

try the following:

 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 post at https://stackoverflow.com/a/312960/169 .

Answer 3

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

Answer 4

This has happened to me before.

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

Correction:

We only need to go to the database, select the identifier column and add newsequentialid() manually in the Default Value or Binding .

No need to update dbo .__ MigrationHistory table.

Hope this helps.

---

The solution of adding New Guid() usually not preferable, since in theory there is a chance that you might get a duplicate by accident.

---

And you should not worry about direct editing in the database. All Entity Framework automate our work with the database.

Translations

 .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) 

at

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

If some of our EF missed one thing and didn’t add it to the default value for us, just add it manually.

Answer 5

This works for me (without Azure), SQL 2008 R2 on the dev server, or localdb \ mssqllocaldb on the local workstation. Note: the object adds the 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 doing 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: Id columns don't get default values, don't worry

Now update the database, and you should eventually define the table definition in your database as follows:

 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 an overridden SqlServerMigrationSqlGenerator to ensure that it does not make the primary key a clustered index, as we encourage our developers to set the best clustered index in the 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); } } 

Answer 6

According to this , DatabaseGeneratedOption.Identity is not defined by a specific migration, if it added after the table was created, in which case I encounter. So I deleted the database and this particular migration and added a new migration, finally updated the database, and then everything works as expected. I am using EF 6.1, SQL2014 and VS2013.

Answer 7

Entity Framework - use Guid as your primary key

Using Guid as the main file of your tables when using the Entity Framework requires a bit more effort than using an integer. The installation process is simple, after you have read / 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

First code

Using Guid as the primary key in the first approach to the code is simple. When creating the object, add the DatabaseGenerated attribute to your primary key, as shown below:

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

The Entity structure will create a column, as you would expect, with a primary key data type and unique identifier.

 codefirst-defaultvalue 

Also note that it is very important that the default value for a column is 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 will lead to a completely random Guid for each new line. This will be cleaned up every time your database is deleted and recreated, so this works best when using the Database First approach.

Database first

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

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

enter image description here

Then open the EDMX diagram, select the appropriate property and open the properties window. Make sure StoreGeneratedPattern is set to identity.

 databasefirst-model 

You do not need to specify an identifier for your object in your code, which will be automatically filled after the object has been sent 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: The "Guid" field MUST be the primary key, or this does not work. Entity Framework will provide you with a rather cryptic error message!

Summary

Guid (globally unique identifiers) can easily be used as primary keys in the Entity Framework. This requires a little extra effort, depending on which approach you take. When using the first code approach, add the DatabaseGenerated attribute to the key field. When using the Database First approach, I 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 

Answer 8

If you are using Code-First and already have a database:

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

Answer 9

You can not. You will do a lot of things. Like a relationship. Which relies on a number that is discarded, which EF cannot do the way you configured it. There is a price for breaking each template.

Create a GUID at C # level so that the relationship continues to work.

Answer 10

And something like that?

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