SET IDENTITY_INSERT TableName ON does not work on Entity Framework Core 2.2

Question

I'm trying to insert a record into a table that has the ID auto generated. For this reason this is my code:

using (var context = new DbContext(GetConnection().Options))
{
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Customers ON;");
    context.SaveChanges();
    context.Customers.Add(customer);
    context.SaveChanges();
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Customers OFF;");
}

But the exception is always the same:

Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.

I also tried to execute the command SET IDENTITY_INSERT dbo.Customers ON manually in SQL Server Management Studio and after execute the code above but the same exception is thrown.

Why?

Answer 1

By default, Entity Framework will throw an error that you got when you try to insert value for identity column. Even if you enable identity_insert, it will be in database level not Entity Framework.

To overcome this, you already done the first step. But as a second step, you need to tell Entity Framework this field be insertable. You can do this with 2 different way.

1. Go to your model class, and add [DatabaseGenerated(DatabaseGeneratedOption.None)] to your identity column. With this method you can insert record with identity column, without depending on transaction.

2. Also, you can execute your whole code in the same connection. This way, EF Core will not throw an error when you try to insert your record. Please check following code.

   using (var db = new Context())
   {
       db.Categories.Add(new Category
       {
           Id = 5,
           Name = "test"
       });
   
       db.Database.OpenConnection();
       try
       {
           db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Categories ON;");
           db.SaveChanges();
           db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Categories OFF;");
       }
       finally
       {
           db.Database.CloseConnection();
       }
   }
   

UPDATE

HasDatabaseGeneratedOption(DatabaseGeneratedOption.None) can also be used when working only with Entity Framework. Entity Framework Core Fluent API doesn't have implementation for HasDatabaseGeneratedOption(DatabaseGeneratedOption.None).