0

I have a Model with a Guid primary key. I want the Database to generate a key on insert so I added the following annotations:

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

        [Required]
        public int employee_number { get; set; }

        //more props...
    }

Now I expected that inserts with RawSQL wouldn't expect a primary key, however the folllowing statement doesn't work when executred through ExecuteSqlRaw:

INSERT INTO employees (employee_number/**, more props*/) 
VALUES (123 /**,more props*/)

An error is caused by the DB about a non-nullable primary key. Explicitly inserting some random Guid works, but i figured the DB would take care of this.

Is the Identity annotation wrong?

mj1313
  • 7,930
  • 2
  • 12
  • 32
Beltway
  • 508
  • 4
  • 17

1 Answers1

0

You could miss one step.

When I add migration, ef core will generate a migration file. Then defaultValueSql: "newsequentialid()" need to be added here.

enter image description here

After excuting Update-Database, I can insert the record with RawSQL.

enter image description here

Edit:

Another method to use HasDefaultValueSql in DbContext.

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        
        modelBuilder.Entity<Employee>()
           .Property(b => b.employee_id)
           .HasDefaultValueSql("newsequentialid()");
    }
Karney.
  • 4,803
  • 2
  • 7
  • 11
  • Why would that be the case? In [this](https://stackoverflow.com/questions/23081096/entity-framework-6-guid-as-primary-key-cannot-insert-the-value-null-into-column) thread it is clearly stated that my solution schould work as expected, but the migration is not creating the `newsequentialid()` attribute for the primary key row. – Beltway Jan 13 '21 at 11:21
  • @Beltway, can you insert the data with RawSql successfully? – Karney. Jan 14 '21 at 01:15
  • Excuse the late response. Yes I can, but only when manually creating a Guid and passing it explicitly. – Beltway Jan 26 '21 at 09:23
  • @Beltway, ok, I have understood how it work here. You can refer to the edited answer. – Karney. Jan 26 '21 at 10:07
  • Is there no way to achieve this through Fluent API or data annotations? I don't want to mess with the auto generated Migrations or tables. – Beltway Jan 26 '21 at 10:18
  • @Beltway, yes, you can refer to the edited answer. – Karney. Jan 27 '21 at 04:04