How can I force entity framework to insert identity columns?

Question

I want to write some C# code to initialize my database with some seed data. Clearly, this is going to require the ability to be able to set the values of various Identity columns when inserting. I'm using a code-first approach. By default, DbContext handles the database connection and so you can't SET IDENTITY_INSERT [dbo].[MyTable] ON. So, what I've done so far is use the DbContext constructor that lets me specify a DB connection to be used. Then, I set IDENTITY_INSERT to ON in that DB connection, and then try to insert my records using entity framework. Here's an example of what I've got so far:

public class MyUserSeeder : IEntitySeeder {
    public void InitializeEntities(AssessmentSystemContext context, SqlConnection connection) {
        context.MyUsers.Add(new MyUser { MyUserId = 106, ConceptPersonId = 520476, Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginId = "520476", Password="28c923d21b68fdf129b46de949b9f7e0d03f6ced8e9404066f4f3a75e115147489c9f68195c2128e320ca9018cd711df", IsEnabled = true, SpecialRequirements = null });
        try {
            connection.Open();
            SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT [dbo].[MyUser] ON", connection);
            int retVal = cmd.ExecuteNonQuery();
            context.SaveChanges();
        }
        finally {
            connection.Close();
        }
    }
}

So close and yet so far - because, although cmd.ExecuteNonQuery() works fine, when I then run context.SaveChanges(), I'm informed that "Explicit value must be specified for identity column in table 'MyUser' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

Presumably, because MyUserId (which is the Identity column in the MyUser table) is the primary key, entity framework doesn't try to set it when I call context.SaveChanges(), even though I gave the MyUser entity a value for the MyUserId property.

Is there a way to force entity framework to try and insert even primary key values for an entity, then? Or maybe a way to temporarily mark MyUserId as not being a primary key value, so EF tries to insert it?

Answer 1

EF 6 method, using the msdn article:

using (var dataContext = new DataModelContainer())
using (var transaction = dataContext.Database.BeginTransaction())
{
    var user = new User()
    {
        ID = id,
        Name = "John"
    };

    dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] ON");

    dataContext.User.Add(user);
    dataContext.SaveChanges();

    dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");

    transaction.Commit();
}

Update: To avoid error "Explicit value must be specified for identity column in table 'TableName' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column", you should change value of StoreGeneratedPattern property of identity column from Identity to None in model designer.

Note, changing of StoreGeneratedPattern to None will fail inserting of object without specified id (normal way) with error "Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF".

Answer 2

You don't need to do any funny business with the connection, you can cut out the middle man and just use ObjectContext.ExecuteStoreCommand.

You could then achieve what you want by doing this:

context.ExecuteStoreCommand("SET IDENTITY_INSERT [dbo].[MyUser] ON");

I don't know of any inbuilt way of telling EF to set identity insert on though.

It's not perfect, but it'd be more flexible and less "hacky" than your current approach.

Update:

I just realised that there is a second part to your problem. Now that you've told SQL that you want to do identity inserts, EF isn't even trying to insert values for said identity (why would it? we haven't told it to).

I've not got any experience with a code first approach, but from some quick searches it seems that you need to tell EF that your column shouldn't be generated from the store. You'll need to do something like this.

Property(obj => obj.MyUserId)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
    .HasColumnName("MyUserId");

Hopefully this will get you pointed in the right direction :-)

Answer 3

Bit late to the party, but in case somebody encounters this problem in EF5 with DB first: I couldn't get either solution to work, but found another workaround:

Before the running the .SaveChanges() command, I reset the table's identity counter:

Entities.Database.ExecuteSqlCommand(String.Format("DBCC CHECKIDENT ([TableNameHere], RESEED, {0})", newObject.Id-1););
Entities.YourTable.Add(newObject);
Entities.SaveChanges();

This means that .SaveChanges() needs to be applied after every addition - but at least it works!

Answer 4

Here is the solution of the problem. I have tried it on EF6 and it worked for me. Following is some pseudo code that should work.

First of all you need to create the overload of the default dbcontext. If you check the base class, you will find the one with passing existing dbConnection. Check following code-

public MyDbContext(DbConnection existingConnection, bool contextOwnsConnection)
        : base(existingConnection, contextOwnsConnection = true)
    {
        //optional
        this.Configuration.ProxyCreationEnabled = true;
        this.Configuration.LazyLoadingEnabled = true;
        this.Database.CommandTimeout = 360;
    }

And in On modelcreating remove the db generated option like,

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyTable>()
            .Property(a => a.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        base.OnModelCreating(modelBuilder);
    }

Now in code you need to pass a connection object explicitly,

using (var connection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString))
        {
            connection.Open();
            using (var context = new MyDbContext(connection, true))
            {
                context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[MyTable] ON");
                context.MyTable.AddRange(objectList);
                context.SaveChanges();
                context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[MyTable] OFF");
            }

            connection.Close();
        }

Answer 5

After careful consideration, I've decided that entity framework's refusal to insert identity columns is a feature, not a bug. :-) If I were to be inserting all entries in my database including their identity values, I'd also have to create an entity for every link table that entity framework had created automatically for me! It's just not the right approach.

So what I'm doing is setting up seeding classes that just use C# code and create EF entities, then use a DbContext to save the newly-created data. It takes a bit longer to take the dumped SQL and turn it into C# code, but there isn't (and shouldn't be) too much data just for "seeding" data - it should be a smallish amount of data which is representative of the kind of data that would be in a live DB that can quickly be put into a fresh DB for debugging/development purposes. This does mean that if I want to link entities together, I do have to do queries on what has already been inserted or my code wouldn't know their generated identity value, eg. This kind of thing will appear within the seeding code, after I have set up and done context.SaveChanges for MyRoles:

var roleBasic = context.MyRoles.Where(rl => rl.Name == "Basic").First();
var roleAdmin = context.MyRoles.Where(rl => rl.Name == "Admin").First();
var roleContentAuthor = context.MyRoles.Where(rl => rl.Name == "ContentAuthor").First();

MyUser thisUser = context.MyUsers.Add(new MyUser {
    Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginUsername = "naldred", Password="c1c966821b68fdf129c46de949b9f7e0d03f6cad8ea404066f4f3a75e11514748ac9f68695c2128e520ca0275cd711df", IsEnabled = true, SpecialRequirements = null
});
thisUser.Roles.Add(roleBasic);

Doing it this way also makes it more likely I will update my seeding data when I change the schema, because I will likely break the seeding code when I change it (if I remove a field or entity, the existing seeding code that uses that field/entity will fail to compile). With a SQL script for doing seeding, that wouldn't be the case, and nor would the SQL script be database-agnostic.

So I think that if you're trying to set the identity fields of entities for doing DB seeding data, you've definitely taken the wrong approach.

If I were actually dragging a load of data from say SQL Server to PostgreSQL (a full live DB, not just some seeding data), I could do it via EF, but I'd want to have two contexts open at the same time, and write some code to grab all the various entities from the source context and put them into the destination context, then save changes.

Generally, the only time it's appropriate to insert identity values is when you're copying from one DB to another DB within the same DBMS (SQL Server -> SQL Server, PostgreSQL -> PostgreSQL, etc.), and then you'd do it in a SQL script and not EF code-first (the SQL script wouldn't be DB-agnostic, but it wouldn't need to be; you're not going between different DBMSs).

Answer 6

This idea only works reliably if the target table is empty, or records are being inserted with ids higher than all already existing ids in the table!

3 years on and I hit a similar problem transferring production data into a test system. The users wanted to be able to copy the production data into the test system whenever they wanted to, so instead of setting up a transfer job in SQL Server I looked for a way to accomplish the transfer in the application using the existing EF classes. This way I could provide the users a menu item to start the transfer whenever they wanted.

The application uses a MS SQL Server 2008 database and EF 6. As the two databases generally have the same structure I thought I could easily transfer data from one DbContext instance to another by reading the records of each entity using AsNoTracking() and just Add() (or AddRange()) the records to the appropriate property on the target DbContext instance.

Here is a DbContext with one entity to illustrate:

public class MyDataContext: DbContext
{
    public virtual DbSet<Person> People { get; set; }
}

To copy the People data I did the following:

private void CopyPeople()
{
    var records = _sourceContext.People.AsNoTracking().ToArray();
    _targetContext.People.AddRange(records);
    _targetContext.SaveChanges();
}

As long as the tables were copied in the right order (to avoid problems with foreign key constraints) this worked very well. Unfortunately tables using identity columns made things a little difficult, as EF ignored the id values and just let SQL Server insert the next identity value. For tables with identity columns I ended up doing the following:

1. Read all the records of a given entity
2. Order the records by id in ascending order
3. set the identity seed for the table to the value of the first id
4. keeping track of the next identity value, add the records one by one. If the id is not the same as the expected next identity value set the identity seed to the next required value

As long as the table is empty (or all the new records have ids higher that current hisghest id), and the ids are in ascending order, EF and MS SQL will insert the required ids and neither system will complain.

Here is a bit of code to illustrate:

private void InsertRecords(Person[] people)
{
    // setup expected id - presumption: empty table therefore 1
    int expectedId = 1;

    // now add all people in order of ascending id
    foreach(var person in people.OrderBy(p => p.PersonId))
    {
        // if the current person doesn't have the expected next id
        // we need to reseed the identity column of the table
        if (person.PersonId != expectedId)
        {
            // we need to save changes before changing the seed value
            _targetContext.SaveChanges();

            // change identity seed: set to one less than id
            //(SQL Server increments current value and inserts that)
            _targetContext.Database.ExecuteSqlCommand(
                String.Format("DBCC CHECKIDENT([Person], RESEED, {0}", person.PersonId - 1)
            );

            // update the expected id to the new value
            expectedId = person.PersonId;
        }

        // now add the person
        _targetContext.People.Add(person);

        // bump up the expectedId to the next value
        // Assumption: increment interval is 1
        expectedId++;
    }

    // now save any pending changes
    _targetContext.SaveChanges();
}

Using reflection I was able to write a Load and a Save method that worked for all the entities in the DbContext.

It's a bit of a hack, but it allows me to use the standard EF methods for reading and writing entities and overcomes the problem of how to set identity columns to particular values under a set of given circumstances.

I hope this will be of help to someone else faced with a similar problem.

Answer 7

Is there a way to force entity framework to try and insert even primary key values for an entity?

Yes, but not as cleanly as I would like to see.

Assuming you are using an auto-generated identity key, EF will completely ignore your attempt to store the key value. This appears to be "By design" for the many good reasons detailed above, but there are still times when you want to fully control your seed data (or an inital load). I suggest EF accomidate this kind of seeding in a future version. But until they do, just write a little code that works within the framework and automates the messy details.

Eventho VendorID is ignored by EF, you can use it with basic looping and counting to determine how many place holder records to add between your live records. The place holders are assigned the next available ID number when they are added. Once your live records have the requested IDs, you just need to delete the junk.

    public class NewsprintInitializer: DropCreateDatabaseIfModelChanges<NewsprintContext>
    {
        protected override void Seed(NewsprintContext context)
        {
            var vendorSeed = new List<Vendor>
            {
                new Vendor { VendorID = 1, Name = "#1 Papier Masson / James McClaren" },
                new Vendor { VendorID = 5, Name = "#5 Abitibi-Price" },
                new Vendor { VendorID = 6, Name = "#6 Kruger Inc." },
                new Vendor { VendorID = 8, Name = "#8 Tembec" }
            };

            //  Add desired records AND Junk records for gaps in the IDs, because .VendorID is ignored on .Add
            int idx = 1;
            foreach (Vendor currentVendor in vendorSeed)
            {
                while (idx < currentVendor.VendorID)
                {
                    context.Vendors.Add(new Vendor { Name = "**Junk**" });
                    context.SaveChanges();
                    idx++;
                }
                context.Vendors.Add(currentVendor);
                context.SaveChanges();
                idx++;
            }
            //  Cleanup (Query/Find and Remove/delete) the Junk records
            foreach (Vendor del in context.Vendors.Where(v => v.Name == "**Junk**"))
            {
                context.Vendors.Remove(del);
            }
            context.SaveChanges();

            // setup for other classes

        }
    }

It worked as expected, except I had to do "SaveChanges" frequently to keep the IDs in order.

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

I had this work by creating a inherited context:

My regular context with EF migrations :

public class MyContext : DbContext
{
    public MyContext() : base("name=MyConnexionString")
    {...}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // best way to know the table names from classes... 
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        ...
    }
}

My alternate context used to override identity.

Do not register this context for EF migrations (I use it to transfer data from another database) :

public class MyContextForTransfers : MyContext
{
    public MyContextForTransfers() : base()
    {
        // Basically tells the context to take the database as it is...
        Database.SetInitializer<MyContextForTransfers >(null);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
         // Tells the Context to include Isd in inserts
         modelBuilder.Conventions.Remove<StoreGeneratedIdentityKeyConvention>();
         base.OnModelCreating(modelBuilder);
    }
}

How to insert (error management is highly simplified...):

public void Insert<D>(iEnumerable<D> items)
{
    using (var destinationDb = new MyContextForTransfers())
    {
        using (var transaction = destinationDb.Database.BeginTransaction())
        {
            try
            {
                destinationDb.Database.ExecuteSqlCommand($"SET IDENTITY_INSERT [dbo].[{typeof(D).Name}] ON");
                destinationDb.Set<D>().AddRange(items);
                destinationDb.SaveChanges();
                destinationDb.Database.ExecuteSqlCommand($"SET IDENTITY_INSERT [dbo].[{typeof(D).Name}] OFF");
                transaction.Commit();
             }
             catch
             {
                transaction.Rollback();
             }
         }
    }
}

Checking for migrations before any transaction might be a good idea, with the "regular" context and configuration :

Answer 10

I'm just a DBA, but whenever something like this pops up, I consider it a code smell. That is, why do you have anything that relies on certain rows having certain identity values? That is to say, in your above example, why does Mrs Novelette need an identity value of 106? Rather than rely on that always being the case, you can get her identity value and use that wherever you'd have hardcoded 106. A little more cumbersome, but way more flexible (in my opinon).

Answer 11

This did the trick (I happen to be using EFCore 3.x at the moment, surely is valid for later versions). Call this with input of true (i.e. set IDENTITY_INSERT to ON) sometime before your first call to SaveAsync. Call it again with false when done (maybe not required). Key point is that OpenConnection had to be set to make this work. If saving multiple times on one context, in my experience one still only needs to call this once, at the start, but even if this is called / set to true multiple times it didn't hurt.

public void Set_IDENTITY_INSERT(bool turnOn, DbContext context, string tableName)
{
    var db = context.Database;

    string command = $"SET IDENTITY_INSERT {tableName} {(turnOn ? "ON" : "OFF")}";

    if(turnOn)
        db.OpenConnection();

    db.ExecuteSqlCommand(command);

    if(!turnOn)
        db.CloseConnection();
}

Answer 12

ef core 6 or before

await _dbCtx.Database.ExecuteSqlRawAsync($"SET IDENTITY_INSERT [table_name] ON");

_dbCtx.Set<TableName>().Add(new TableName { Id = 1, Name = "qwe"});

await _dbCtx.SaveChangesAsync()

await _dbCtx.Database.ExecuteSqlRawAsync($"SET IDENTITY_INSERT [table_name] OFF");

ef core 7 +

you need to wrap it in the transaction. (because, Ef 7 adds 'SET IMPLICIT_TRANSACTIONS OFF' to improve the perf, that will interfere with the prev command)

using var transaction = _dbCtx.Database.BeginTransaction();

await _dbCtx.Database.ExecuteSqlRawAsync($"SET IDENTITY_INSERT [table_name] ON");

_dbCtx.Set<TableName>().Add(new TableName { Id = 1, Name = "qwe"});

await _dbCtx.SaveChangesAsync()

await _dbCtx.Database.ExecuteSqlRawAsync($"SET IDENTITY_INSERT [table_name] OFF");

await transaction.CommitAsync();

Answer 13

My solution for EF6 Code First. Depends on the table not requiring any other column to be filled in.

  db.Database.ExecuteSqlCommand($"SET IDENTITY_INSERT [**table**] ON; insert into [**table**] ([ID]) values (@id)", new SqlParameter("id", entity.ID));

  db.Entry(entity).State = EntityState.Modified;

  await db.SaveChangesAsync();

First line creates an empty row in the table, with the desired ID. Second line marks the model as dirty, otherwise no changes would be detected. Third line saves the changes.

This solution is not perfect, but resolves the NOT FOR REPLICATION error for Code First.

Update 16/08/22

To explain my reasoning for the 'limited' solution, and why it I thought it was worth posting it here.

Our requirement was for a maintenance script, I did not want to affect either our models or our context class permanently. Our identity column is there for a reason.

Luckily (I guess), I was in a position where I already knew the table had no required columns other than the primary key. As has been pointed out, this isn't always the case, but it was this time.

Had I have had additional columns to worry about, I would have been reluctant to add them to the SQL script. Even though the table question rarely changes, it is still clearly a code smell.

But with all things, it is a trade off (see every other answer), and if you're reading this and wondering how to deal with additional columns.

I think my first choice would be to look at inheriting my main context class, and deconstructing identify column there. Overkill for my immediate requirements, as the initial solution works very well.

My second choice, as I alluded to above, would be to add the extra column(s) to the SQL script with sensible defaults. Then perhaps come up with a manual process, ensuring that SQL stayed in sync.

Answer 14

I couldn't find a way to insert records into a table. Basically, I created a SQL script with something like this...

            sb.Append("SET IDENTITY_INSERT [dbo].[tblCustomer] ON;");

            foreach(...)
            {
                var insert = string.Format("INSERT INTO [dbo].[tblCustomer]
                     ([ID],[GivenName],[FamilyName],[NINumber],[CustomerIdent],
                      [InputterID],[CompanyId],[Discriminator]) 
                      VALUES({0}, '{1}', '{2}', '{3}', '{4}', 2, 2, 'tblCustomer'); ", 
                          customerId, firstName, surname, nINumber, Guid.NewGuid());

            sb.Append(insert);
                ...
            }

            sb.Append("SET IDENTITY_INSERT [dbo].[tblCustomer] OFF;");
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                var svrConnection = new ServerConnection(sqlConnection);
                var server = new Server(svrConnection);
                server.ConnectionContext.ExecuteNonQuery(sb.ToString());
        }

I am using EF 6.