IDENTITY_INSERT ON error with foreign key

entity-framework entity-framework-6

Question

I'm trying to set Id manually for some entries.

I have a Customer class with an Identity field

  modelBuilder.Entity<Customer>().Property(f => f.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);

and in the insert part I have

    using (var transaction = ctx.Database.BeginTransaction())
    {
      ctx.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Customers] ON");
      ctx.Customers.AddRange(customersList);
      ctx.SaveChanges();
      ctx.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Customers] OFF");
      transaction.Commit();
    }

but I'm still getting the exception

Explicit value must be specified for identity column in table 'Customers' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

In the StateEntries of my exception, I can see that all the fields that are stored in another table like "Contacts" seems to be the cause customer.Contacts = new List<Contact> {new Contact {Name = "Test", … }}

How can I make it work?

1
0
9/26/2018 10:34:25 AM

Accepted Answer

That error message is misleading. You are succeeding in turning IDENTITY_INSERT on, but EF is still generating an INSERT statement under the assumption that the key value will be generated by the IDENTITY column.

So you additionally must generate an INSERT statement containing the key value. You can, of course, use .ExecuteSqlCommand() to perform the INSERT as well. Or you can get EF to do it, but you must use a model where that Entity does not have database-generated keys. OnModelCreating configures the model only once per ModelType, so you can't just put a switch in there.

Instead you can create a subtype of your DbContext for seeding, overriding the entity configuration of the base DbContext. Like this:

class Db_Seed: Db
{
    public Db_Seed(string constr) : base(constr)
    {
        Database.SetInitializer<Db_Seed>(null);  //no initializer
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Customer>().Property(c => c.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    }
}

You still must set IDENTITY_INSERT on (since the table does have an IDENTITY), but with this context EF will not generate an INSERT statement that assumes the server will generate the key.

1
9/26/2018 1:08:46 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow