EF6 code first - Cannot insert explicit value for identity column in table '' when IDENTITY_INSERT is set to OFF

Question

Using EF6 code-first :

My entity :

public class Symbol
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]
    public int Id
    {
        get; set; 
    }

    public string Name { get; set; }
}

Error :

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

The common solution for this when googling the problem is setting DatabaseGeneratedOption.Identity.

1. This does not make sense now that I can't tell the DB not to generate the Identity for me

2. 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.

Scenario :

   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"} 

I'm guessing if I set Identity insert on before each insert, this might work.

Like in this answer only on each insert

Is there any built in way to insert identity with out having to set identity insert on manually on each insert ?

EDIT :

I've also tried something inspired by the link above :

    public void InsertRange(IEnumerable<TEntity> entities)
    {
        _context.IdentityInsert<TEntity>(true);

        _dbSet.AddRange(entities);
        _context.SaveChanges();

        _context.IdentityInsert<TEntity>(false);
    }


    public static string GetTableName<T>(this DbContext context) where T : class
    {
        ObjectContext objectContext = ((IObjectContextAdapter) context).ObjectContext;
        return objectContext.GetTableName<T>();
    }

   public static string GetTableName<T>(this ObjectContext context) where T : class
    {
        string sql = context.CreateObjectSet<T>().ToTraceString();
        Regex regex = new Regex(@"FROM\s+(?<table>.+)\s+AS");
        Match match = regex.Match(sql);

        string table = match.Groups["table"].Value;
        return table;
    }

    public static void IdentityInsert<T>(this DbContext context ,bool on) where T : class
    {
        if (on)
        {
            context.Database.ExecuteSqlCommand(string.Concat(@"SET IDENTITY_INSERT ", context.GetTableName<T>(), @" ON"));
        }
        else
        {
            context.Database.ExecuteSqlCommand(string.Concat(@"SET IDENTITY_INSERT ", context.GetTableName<T>(), @" OFF"));
        }
    }

It didn't work

EDIT 2 :

Well droping the DB completely and then setting it with

          [Key]
          [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]

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 .

EDIT :

This seems to explain the issue

Migrations don't support identity change.

Probably having the primary key defined before and then depending on the migration when i change its identity settings doesn't work.

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.

Answer 1

Turn off identity in db and manage your key values in your Code.... This is the best approach