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?

Lob

Usually when I see something like this in my code I feel that there is something wrong with the design. If the Id is not an identity then don't make it one... If you need something in the table with the Id of 1 you may want to add an extra column for the identifier. Generally my lookup tables have an Id, a Name that I use for referencing internally and a Description that is what appears in the UI. – Schreiner

@Schreiner Good point, and I feel the same, but there is a vague requirement for "super" users to be able to write SQL reports that will run across multiple installations, so to keep the report code simpler, we've decided to try known Id values. I can almost the opening of a can of worms here... – Lob

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

Lob

This is how I would do it. (well the second half of your answer, the first half isnt needed) – Publisher

@Lob What should I pass instead of @0, @1 because it gives me an error. – Blister

Works for me if I use "@p0, @p1" instead of "@0, @1." (note: the quote placement is just because StackOverflow thought I was tagging users.) – Polik

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.

Ethbinium

I believe the issue with using HasDatabaseGeneratedOption(DatabaseGeneratedOption.None) is that when you then use your application with entity framework, the insert will fail because EF is now expecting an ID to be supplied with any inserts. – Fissirostral

@Fissirostral when I use my application, the context is created with a constructor that leaves allowIdentityInsert "false", so the app works as expected. – Ethbinium

My OnModelCreating() never gets called when I instantiate my dbcontext with my custom constructor. Am I missing something? – Ellamaeellan

@NormanBentley are you making a call to base() before the body of that custom constructor? You may need to do that. – Ethbinium

@Chris This doesn't seem to work as the migration then thinks the models are out of sync with the db. How did you get around that? Also, how are you getting the migrations to use your custom constructor? – Jamnes

@Jamnes I don't use migrations. If I did, I would do all this "identity cheating" outside the context of migrations. In my case, I was building a new app from scratch to replace an old one, but the client had printed reports with IDs generated by the old app and wanted them preserved. So I only pulled in data before demoing the new app, and then never again once it went live, hence no need for migrations. – Ethbinium

@Chris how did you make this work? It looks like OnModelCreating() gets called before my constructor overload where I set the allowIdentityInsert. Even worse, once the model is built it's cached, so if I use the parameterless constructor before the one which allows identity insert, it will use the cached model. Any tips would be much appreciated! – Engorge

@Kevin, OnModelCreating() can't be called before your constructor... I don't think that's possible. Maybe the wrong constructor is being called? – Ethbinium

@Chris I added some logging to see what was happening, and indeed the OnModelCreating() was being called before my constructor had a chance to set it's values. I don't know how they made it do that. Anyhow, we created a second derived DbContext which allows identity insert and used that in the special cases when we need to insert into those columns. See my answer below for more details. – Engorge

@Kevin ahh, maybe the base constructor is calling OnModelCreating and since it's overridden, you're seeing it happen in your subclass before the subclass constructor passes the base() call. – Ethbinium

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();
}

Marshmallow

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.

Tonita

Thought this might be the case. Although I read that it was not possible to have two entities over the same underlying table (#5094063) – Lob

Yeah. Generally one reason I stay away from doing data loads through EF. Plus EF having terrifically bad performance for bulk operations anyway. – Tonita

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();
}

Ploughboy

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);

Engorge

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.

Gulledge

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

Goodwill

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

Kelantan