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

c# entity-framework entity-framework-6

Question

EF6 code-first:

My company:

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.

When searching for a solution to this issue on Google, settingDatabaseGeneratedOption.Identity .

  1. Now that I can't instruct the DB to stop generating the Identity for me, this is absurd.

  2. It produces an odd side effect. It is creating a running persona that picks up just where I left off. even if I remove every entity from the database.

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

This could work if I turn on Identity insert before each insert, I think.

Like in this answer only on each insert

Is there a built-in mechanism to insert identity that doesn't need manually turning it on with every insert?

EDIT:

Inspiring by the aforementioned site, I also attempted the following:

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

EDIT 2:

Dropping the DB entirely and setting it up using

          [Key]
          [DatabaseGeneratedAttribute(DatabaseGeneratedOption.None)]

did the trick, however I have critical data in that DB that I can't afford to lose with every modification. I do that using migrations since the issue first appeared after a migration.

EDIT :

This seems to clarify the problem.

Identity change is not supported by migrations.

It probably won't work to establish the main key first, then alter the identity settings based on the migration.

The story's lesson. If you are unsure about your identities at the start, drop the creation and do a custom migration from the link.

1
2
5/23/2017 10:30:46 AM

Popular Answer

ZZZ_tmp
1
11/6/2016 4:25:40 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