Entity Framework auto generate GUID

Question

I am new to EF so here goes.I have a class which contains the following

public class EmailTemplate
{
    public Guid Id { get; set; }

    [MaxLength(2000)]
    public string Html { get; set; }
}

Here is my mapping class

class EmailMapper : EntityTypeConfiguration<EmailTemplate>
    {
        public EmailMapper()
        {
            ToTable("EmailTemplate");

            HasKey(c => c.Id);
            Property(c => c.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(c => c.Id).IsRequired();
        }
    }

I am trying to call DbContext.SaveChanges(), but I get the following error :

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Id', table 'AutoSendConnection.dbo.EmailTemplates'; column does not allow nulls. INSERT fails.

What am i doing wrong? Why won't EF auto create a unique GUID?

Answer 1

Just decorate the Id field on your EmailTemplate class as below and SQL Server will automatically generate the value on insert.

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

You can also remove your Mapper class as it's no longer needed.

Answer 2

If using .Net core then this should work for you ...

Use fluent API

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Node>().Property(x => x.ID).HasDefaultValueSql("NEWID()");
}

or

modelBuilder.Entity<Student>().Property(p => p.StudentID)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

Here is a more comprehensive Cheat Sheet for entity framework

Answer 3

Addressing other answers here

None of these other options seem to work and I've questioned this time and time again with the EF team over on github ...

https://github.com/aspnet/EntityFramework6/issues/762

... for some reason the EF dev team seem to think that this is "working by design" and repeatedly close tickets questioning this "bug".

The EF team explanation

For some reason they seem to think that "generating Guids in SQL is considered not best practice and that to ensure the keys are available immediately we should be generating the keys in the app code".

The issue here of course is that highly populated tables run the risk of you taking further business actions consuming an invalid key.

In my case this could break some extremely complex multi server DTC transactions so I don't believe the advice from MS to be correct, that said EF Core doesn't currently support distributed transactions at all so in a focused context they may have a point.

My answer (which actually works)

In short, I solved this by "manually hacking" the generated migration after generating it ...

EF code first migrations, DB generated guid keys

To quote the other question the answer is as follows:

Generate the migration script as you normally would putting both attributes on the key property like this ...

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

... declaratively speaking the entity is now correct.

The migration it will generate will look something like:

CreateTable(
    "dbo.Foos",
    c => new
        {
            Id = c.Guid(nullable: false),
            ...
        })
    .PrimaryKey(t => t.Id)
    ...;

... I have not been able to pin down why, but in some situations this will work and in others it won't (run the migration, perform an insert to test).

If it fails, roll the migration back then modify it to read something like ...

CreateTable(
    "dbo.Foos",
    c => new
        {
            Id = c.Guid(nullable: false, defaultValueSql: "newid()"),
            ...
        })
    .PrimaryKey(t => t.Id)
    ...;

... the extra code here tells SQL to generate the key as we would expect.

As a rule of thumb I would apply this change all the time for consistency reasons and it means that at a glance your migrations will show you exactly what keys are db generated and of course which ones don't.

Answer 4

After a long investigation, I found out that in EF Core 3.1 you need to use

builder.Property(e => e.Id).ValueGeneratedOnAdd();

Answer 5

Set the default sql value of the field to 'newsequentialid()' in the mapping configuration.

Answer 6

You can also set Default Value of ID as NewID() in Sql Server itself and pass the GUID as null

I used to do it in SSMS.

Answer 7

I prefer to leave the database to generate the id automatically for example the following schema:

CREATE TABLE [dbo].[MyTable](
    [MyId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Booking_BookingId]  DEFAULT (newsequentialid())
    )

Then in the code first mapping I specify the following to tell Entity Framework that the database will take care of generating the value on insert.

Property(a => a.MyId).IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

Answer 8

I encountered this issue in .NET 6. Some background is that we use int for primary keys but a new requirement ment that parts of the system needed to be in sync with other systems and we could no longer depend on our int keys. We decided to use a hybrid approach with auto-increment primary key integer id column and a GUID column as described here:

https://dba.stackexchange.com/a/96990/80960

Basically the model looked like this:

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

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

Migration looked like this:

        migrationBuilder.AddColumn<Guid>(
            name: "Guid",
            table: "Products",
            type: "uniqueidentifier",
            nullable: false,
            defaultValue: new Guid("00000000-0000-0000-0000-000000000000"));

ApplicationDbContextModelSnapshot.cs:

                b.Property<Guid>("Guid")
                    .ValueGeneratedOnAdd()
                    .HasColumnType("uniqueidentifier");

I thought this meant that every old value would get 00000000-0000-0000-0000-000000000000 and new values would receive ValueGeneratedOnAdd. However when I tested to add a new value I still got value 00000000-0000-0000-0000-000000000000. See image below:

I then tried to use defaultValue: Guid.NewGuid()); instead of defaultValue: new Guid("00000000-0000-0000-0000-000000000000")); in migration.

This meant that every old and every new value I added got the same Guid but not 00000000-0000-0000-0000-000000000000.

I then switched over to manually add defaultValueSql: "NEWID()" instead of defaultValue: new Guid("00000000-0000-0000-0000-000000000000") in migration.

Now everything started working as expected both with old values and new values:

Now I wanted this functionality to be default for every new Guid added. I therefore modified ApplicationDbContext.cs method protected override void OnModelCreating(ModelBuilder modelBuilder) like this:

foreach (var property in modelBuilder.Model.GetEntityTypes()
    .SelectMany(t => t.GetProperties())
    .Where(p => p.ClrType == typeof(Guid)))
{
    property.SetDefaultValueSql("NEWID()");
}

Model ended up looking like this since [DatabaseGenerated(DatabaseGeneratedOption.Identity)] did not work in this case:

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

public Guid Guid { get; set; }

Answer 9

Entity Framework Core Update:

There is no need to use [DatabaseGenerated(DatabaseGeneratedOption.Identity)].
There is no need to use fluent API

EF Core automatically take care of it and generates Id for primary key

Example:

public class DummyEntity
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

}

Seeding

    _context.DummyEntities.Add(new DummyEntity
    {
        FirstName = "Abc",
        LastName = "Def",
        Postion = "User",
    });

    _context.SaveChanges();