1

I have a DB that created by EF6 Code First. Some tables have an identity column. I need to insert some records to these tables with OLD Id values. I tried the solution that appeared in this link How to switch between DatabaseGeneratedOption.Identity... that demonstrates how to switch between computed and none options, but not on an identity column. When I tried to do it, I got this error message: "Cannot insert explicit value for identity column in table 'Links' when IDENTITY_INSERT is set to OFF." Is there any way to solve this?

Community
  • 1
  • 1
Efi_K
  • 43
  • 5

1 Answers1

2

The problem you are facing is that EF sends explicit ID values to the DBMS, but it expects none while IDENTITY_INSERT is set to off.

You have to do the following additionally: context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Links ON");

This tells your DBMS to also accept explicit values. For this to work, you will have to wrap this command and (at least) SaveChanges() into one transaction.

DevilSuichiro
  • 1,049
  • 8
  • 21
  • Throwing SqlException when SaveChanges() is called. This happens cause my entity don't have their ID attached on insert command graph. I think so this behavior happens because the DatabaseGenerateOption can't be switched at runtime - from default(Identity) to (None). Some advice to me ? Can I switch the DatabaseGenerateOption at runtime ? – Henrique Mar 27 '18 at 14:48
  • @Henrique if the SqlException states that the ID value cannot be null, the model still has the DatabaseGeneratedOption.Identity. This setting IS set at runtime, namely while model creation right before the first EF db access. SqlExceptions (and DbUpdateExceptions wrapping them) most prominently point to mismatches between the model and the database schema. – DevilSuichiro Mar 27 '18 at 22:13
  • @DevilSuichiro I was missing this behavior, thank you! – Henrique May 17 '18 at 18:13