IDENTITY_INSERT during seeding with EntityFramework 6 Code-First

Question

I have an entity that has an Auto-identity (int) column. As part of the data-seed I want to use specific identifier values for the "standard data" in my system, after that I want to have the database to sort out the id value.

So far I've been able to set the IDENTITY_INSERT to On as part of the insert batch, but Entity Framework does not generate an insert statement that include the Id. This makes sense as the model thinks the database should provide the value, but in this case I want to provide the value.

Model (pseudo code):

public class ReferenceThing
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id{get;set;}
    public string Name{get;set;}
}

public class Seeder
{
    public void Seed (DbContext context)
    {

        var myThing = new ReferenceThing
        {
            Id = 1,
            Name = "Thing with Id 1"
        };

        context.Set<ReferenceThing>.Add(myThing);

        context.Database.Connection.Open();
        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing ON")

        context.SaveChanges();  // <-- generates SQL INSERT statement
                                //     but without Id column value

        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing OFF")
    }
}

Anyone able to offer any insight or suggestions?

Answer 1

So I might have resolved this one by resorting to generating my own SQL insert statements that include the Id column. It feels like a terrible hack, but it works :-/

public class Seeder
{
    public void Seed (DbContext context)
    {

        var myThing = new ReferenceThing
        {
            Id = 1,
            Name = "Thing with Id 1"
        };

        context.Set<ReferenceThing>.Add(myThing);

        context.Database.Connection.Open();
        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing ON")

        // manually generate SQL & execute
        context.Database.ExecuteSqlCommand("INSERT ReferenceThing (Id, Name) " +
                                           "VALUES (@0, @1)", 
                                           myThing.Id, myThing.Name);

        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing OFF")
    }
}

Answer 2

I created an alternate constructor for my DbContext that takes a bool allowIdentityInserts. I set that bool to a private field of the same name on the DbContext.

My OnModelCreating then "unspecifies" the Identity spec if I create the context in that "mode"

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        if(allowIdentityInsert)
        {
            modelBuilder.Entity<ChargeType>()
                .Property(x => x.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        }
    }

This allows me to insert Ids without changing my actual database identity spec. I still need to use the identity insert on/off trick you did, but at least EF will send Id values.

Answer 3

If you use database first model, then you should change StoreGeneratedPattern property of ID column from Identity to None.

After that, as I replied here, this should help:

using (var transaction = context.Database.BeginTransaction())
{
    var myThing = new ReferenceThing
    {
        Id = 1,
        Name = "Thing with Id 1"
    };

    context.Set<ReferenceThing>.Add(myThing);

    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing ON");

    context.SaveChanges();

    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT ReferenceThing OFF");

    transaction.Commit();
}

Answer 4

Can not be done without a second EF level model - copy the classes for the seeding.

As you said - your metadata says that the DB provides the value, which it does not during the seeding.

Answer 5

According to this previous Question you need to begin a transaction of your context. After saving the change you have to restate the Identity Insert column too and finally you must have to commit the transaction.

using (var transaction = context.Database.BeginTransaction())
{
    var item = new ReferenceThing{Id = 418, Name = "Abrahadabra" };
    context.IdentityItems.Add(item);
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Test.Items ON;");
    context.SaveChanges();
    context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");
    transaction.Commit();
}

Answer 6

For future Googlers, I found the answers suggesting some conditional logic in the OnModelCreating() didn't work for me.

The main issue with this approach is EF caches the model, so it's not possible to switch identity on or off in the same app domain.

The solution we adopted was to create a second derived DbContext which allows identity insert. This way, both models can be cached, and you can use the derived DbContext in the special (and hopefully) rare cases when you need to insert identity values.

Given the following from @RikRak's question:

public class ReferenceThing
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
}

public class MyDbContext : DbContext 
{
    public DbSet<ReferenceThing> ReferenceThing { get; set; }   
}

We added this derived DbContext:

public class MyDbContextWhichAllowsIdentityInsert : MyDbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<ReferenceThing>()
                    .Property(x => x.Id)
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    }
}

Which would then be used with the Seeder as follows:

var specialDbContext = new MyDbContextWhichAllowsIdentityInsert();

Seeder.Seed(specialDbContext);

Answer 7

Say you have a table named Branch with a column of type integer named BranchId. By convention with SQL Server, EF will assume that a column with of type integer is an Identity column.

So it will automatically set the column's Identity Specification to:

• (Is Identity) Yes
• Identity Increment 1
• Identity Seed 1

If you want to seed an entity with id values that are assigned, then use the DatabaseGeneratedOption as follows:

public class Branch
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int BranchId { get; set; }
    public string Description { get; set; }
}

You can then seed the data and assign whatever value you want to the BranchId.

Answer 8

After experimenting several options found on this site, the following code worked for me (EF 6). Notice that it first attempts a normal update if the item already exists. If it does not, then tries a normal insert, if the error is due to IDENTITY_INSERT then tries the workaround. Notice also that db.SaveChanges will fail, hence the db.Database.Connection.Open() statement and optional verification step. Be aware this is not updating the context, but in my case it is not necessary. Hope this helps!

public static bool UpdateLeadTime(int ltId, int ltDays)
{
    try
    {
        using (var db = new LeadTimeContext())
        {
            var result = db.LeadTimes.SingleOrDefault(l => l.LeadTimeId == ltId);

            if (result != null)
            {
                result.LeadTimeDays = ltDays;
                db.SaveChanges();
                logger.Info("Updated ltId: {0} with ltDays: {1}.", ltId, ltDays);
            }
            else
            {
                LeadTime leadtime = new LeadTime();
                leadtime.LeadTimeId = ltId;
                leadtime.LeadTimeDays = ltDays;

                try
                {
                    db.LeadTimes.Add(leadtime);
                    db.SaveChanges();
                    logger.Info("Inserted ltId: {0} with ltDays: {1}.", ltId, ltDays);
                }
                catch (Exception ex)
                {
                    logger.Warn("Error captured in UpdateLeadTime({0},{1}) was caught: {2}.", ltId, ltDays, ex.Message);
                    logger.Warn("Inner exception message: {0}", ex.InnerException.InnerException.Message);
                    if (ex.InnerException.InnerException.Message.Contains("IDENTITY_INSERT"))
                    {
                        logger.Warn("Attempting workaround...");
                        try
                        {
                            db.Database.Connection.Open();  // required to update database without db.SaveChanges()
                            db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT[dbo].[LeadTime] ON");
                            db.Database.ExecuteSqlCommand(
                                String.Format("INSERT INTO[dbo].[LeadTime]([LeadTimeId],[LeadTimeDays]) VALUES({0},{1})", ltId, ltDays)
                                );
                            db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT[dbo].[LeadTime] OFF");
                            logger.Info("Inserted ltId: {0} with ltDays: {1}.", ltId, ltDays);
                            // No need to save changes, the database has been updated.
                            //db.SaveChanges(); <-- causes error

                        }
                        catch (Exception ex1)
                        {
                            logger.Warn("Error captured in UpdateLeadTime({0},{1}) was caught: {2}.", ltId, ltDays, ex1.Message);
                            logger.Warn("Inner exception message: {0}", ex1.InnerException.InnerException.Message);
                        }
                        finally
                        {
                            db.Database.Connection.Close();
                            //Verification
                            if (ReadLeadTime(ltId) == ltDays)
                            {
                                logger.Info("Insertion verified. Workaround succeeded.");
                            }
                            else
                            {
                                logger.Info("Error!: Insert not verified. Workaround failed.");
                            }
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        logger.Warn("Error in UpdateLeadTime({0},{1}) was caught: {2}.", ltId.ToString(), ltDays.ToString(), ex.Message);
        logger.Warn("Inner exception message: {0}", ex.InnerException.InnerException.Message);
        Console.WriteLine(ex.Message);
        return false;
    }
    return true;
}

Answer 9

Try adding this code to your DB Context "to keep it clean" so as to speak:

Usage Scenario example (Add ID 0 default records to entity type ABCStatus:

protected override void Seed(DBContextIMD context)
{
    bool HasDefaultRecord;
    HasDefaultRecord = false;
    DBContext.ABCStatusList.Where(DBEntity => DBEntity.ID == 0).ToList().ForEach(DBEntity =>
    {
        DBEntity.ABCStatusCode = @"Default";
        HasDefaultRecord = true;
    });
    if (HasDefaultRecord) { DBContext.SaveChanges(); }
    else {
        using (var dbContextTransaction = DBContext.Database.BeginTransaction()) {
            try
            {
                DBContext.IdentityInsert<ABCStatus>(true);
                DBContext.ABCStatusList.Add(new ABCStatus() { ID = 0, ABCStatusCode = @"Default" });
                DBContext.SaveChanges();
                DBContext.IdentityInsert<ABCStatus>(false);
                dbContextTransaction.Commit();
            }
            catch (Exception ex)
            {
                // Log Exception using whatever framework
                Debug.WriteLine(@"Insert default record for ABCStatus failed");
                Debug.WriteLine(ex.ToString());
                dbContextTransaction.Rollback();
                DBContext.RollBack();
            }
        }
    }
}

Add this helper class for Get Table Name extension method

public static class ContextExtensions
{
    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;
    }
}

The code to add to the DBContext:

public MyDBContext(bool _EnableIdentityInsert)
    : base("name=ConnectionString")
{
    EnableIdentityInsert = _EnableIdentityInsert;
}

private bool EnableIdentityInsert = false;

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<DBContextIMD, Configuration>());
        //modelBuilder.Entity<SomeEntity>()
        //    .Property(e => e.SomeProperty)
        //    .IsUnicode(false);

        // Etc... Configure your model
        // Then add the following bit
    if (EnableIdentityInsert)
    {
        modelBuilder.Entity<SomeEntity>()
            .Property(x => x.ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        modelBuilder.Entity<AnotherEntity>()
            .Property(x => x.ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    }
}

//Add this for Identity Insert

/// <summary>
/// Enable Identity insert for specified entity type.
/// Note you should wrap the identity insert on, the insert and the identity insert off in a transaction
/// </summary>
/// <typeparam name="T">Entity Type</typeparam>
/// <param name="On">If true sets identity insert on else set identity insert off</param>
public void IdentityInsert<T>(bool On)
    where T: class
{
    if (!EnableIdentityInsert)
    {
        throw new NotSupportedException(string.Concat(@"Cannot Enable entity insert on ", typeof(T).FullName, @" when _EnableIdentityInsert Parameter is not enabled in constructor"));
    }
    if (On)
    {
        Database.ExecuteSqlCommand(string.Concat(@"SET IDENTITY_INSERT ", this.GetTableName<T>(), @" ON"));
    }
    else
    {
        Database.ExecuteSqlCommand(string.Concat(@"SET IDENTITY_INSERT ", this.GetTableName<T>(), @" OFF"));
    }
}

//Add this for Rollback changes

/// <summary>
/// Rolls back pending changes in all changed entities within the DB Context
/// </summary>
public void RollBack()
{
    var changedEntries = ChangeTracker.Entries()
        .Where(x => x.State != EntityState.Unchanged).ToList();

    foreach (var entry in changedEntries)
    {
        switch (entry.State)
        {
            case EntityState.Modified:
                entry.CurrentValues.SetValues(entry.OriginalValues);
                entry.State = EntityState.Unchanged;
                break;
            case EntityState.Added:
                entry.State = EntityState.Detached;
                break;
            case EntityState.Deleted:
                entry.State = EntityState.Unchanged;
                break;
        }
    }
}