title: EF6 code first - Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF
body: <p>Using EF6 code-first : </p>
  
  <p>My entity :</p>
  
  <pre><code>public class Symbol
  {
      [Key]
      [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
      public int Id
      {
          get; set; 
      }
  
      public string Name { get; set; }
  }
  </code></pre>
  
  <p>Error : </p>
  
  <blockquote>
    <p>Cannot insert explicit value for identity column in table 'Symbols' when IDENTITY_INSERT is set to OFF.  </p>
  </blockquote>
  
  <p>The common solution for this when googling the problem  is setting <code>DatabaseGeneratedOption.Identity</code>.</p>
  
  <ol>
  <li><p>This does not make sense now that I can't tell the DB not to generate the Identity for me </p></li>
  <li><p>It results in a weird side effect. It is generating a running identity, running from where ever I left off. Where even if I delete all the entities in the table. </p></li>
  </ol>
  
  <p>Scenario :</p>
  
  <pre><code>   1) inserting   { 44, "s1"} , { 55, "s2"} , { 52, "s3"}          
        In DB : {1,"s1"} {2,"s2"} {3,"s3"} 
     2) delete all symbols   
     3) inserting   { 44, "s1"} , { 55, "s2"} , { 52, "s3"}          
        In DB : {4,"s1"} {5,"s2"} {6,"s3"} 
  </code></pre>
  
  <p>I'm guessing if I set Identity insert on before each insert, this might work. </p>
  
  <p><a href="https://stackoverflow.com/questions/24265300/identity-insert-during-seeding-with-entityframework-6-code-first">Like in this answer only on each insert</a></p>
  
  <p>Is there any built in way to insert identity with out having to set identity insert on manually on each insert ?</p>
  
  <p>EDIT :</p>
  
  <p>I've also tried something inspired by the link above  :</p>
  
  <pre><code>    public void InsertRange(IEnumerable&lt;TEntity&gt; entities)
      {
          _context.IdentityInsert&lt;TEntity&gt;(true);
  
          _dbSet.AddRange(entities);
          _context.SaveChanges();
  
          _context.IdentityInsert&lt;TEntity&gt;(false);
      }
  
  
      public static string GetTableName&lt;T&gt;(this DbContext context) where T : class
      {
          ObjectContext objectContext = ((IObjectContextAdapter) context).ObjectContext;
          return objectContext.GetTableName&lt;T&gt;();
      }
  
     public static string GetTableName&lt;T&gt;(this ObjectContext context) where T : class
      {
          string sql = context.CreateObjectSet&lt;T&gt;().ToTraceString();
          Regex regex = new Regex(@"FROM\s+(?&lt;table&gt;.+)\s+AS");
          Match match = regex.Match(sql);
  
          string table = match.Groups["table"].Value;
          return table;
      }
  
      public static void IdentityInsert&lt;T&gt;(this DbContext context ,bool on) where T : class
      {
          if (on)
          {
              context.Database.ExecuteSqlCommand(string.Concat(@"SET IDENTITY_INSERT ", context.GetTableName&lt;T&gt;(), @" ON"));
          }
          else
          {
              context.Database.ExecuteSqlCommand(string.Concat(@"SET IDENTITY_INSERT ", context.GetTableName&lt;T&gt;(), @" OFF"));
          }
      }
  </code></pre>
  
  <p>It didn't work </p>
  
  <p>EDIT 2 : </p>
  
  <p>Well droping the DB completely and then setting it with </p>
  
  <pre><code>          [Key]
            [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
  </code></pre>
  
  <p>did the trick, the thing is i got important data in that DB and can't afford to drop it on each change. For that I use migrations after a migration the problem emerged .  </p>
  
  <p>EDIT  : </p>
  
  <p>This seems to explain the issue </p>
  
  <p><a href="https://romiller.com/2013/04/30/ef6-switching-identity-onoff-with-a-custom-migration-operation/" rel="nofollow noreferrer">Migrations don't support identity change.</a></p>
  
  <p>Probably having the primary key defined before and then depending on the migration when i change its identity settings doesn't work.</p>
  
  <p>The moral of the story. Be sure of your identities on the beginning if not drop create or do the custom migration from the link.</p>