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?

Feldt

this might be helpful – Reckon

@MichalKlouda That answers seems to approach things from a DB-first, not a code-first, approach. – Feldt

possible duplicate of IDENTITY_INSERT ON not being respected for Entity Framework DBSet.Add method – Ickes

Answer 1

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

Feldt

There are valid cases for doing this, like importing data from another system. – Chaconne

Answer 2

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

Celia

This doesn't at all address the issue of EF not inserting the key -- instead it address how to make it transactional – Esquiline

Well, it makes it transactional, but it solves problem with identity inserting, doesn't it? – Celia

When I tried this, I got the error: "Explicit value must be specified for identity column in table 'myTable' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column." (Even though I DID set a value for my table record object's identity field prior to calling SaveChanges.) – Besant

Jon, it's another problem, 1st result from google search: support.microsoft.com/en-us/kb/908711 – Celia

@RomanO Thanks for the help! However, I checked, and the "Not For Replication" property on my identity column is set to false. I suspect that the problem is that EF isn't trying to send my specified ID value to the database (as discussed in the "Update" to Doctor Jones' answer). – Besant

it seems wrap it in a transaction is required to make this to work – Pooley

This means all of the sudden your code has to have knowledge of table names. The point of Entity Framework is to abstract that information. – Heterogamy

xr280xr, yes, it's not ideal solution, but show me better? :) – Celia

Thank you for the update, I could not figure out how to get EF to send my custom "key value" to SQL server. But your update helped me when using a DB first model by switching StoreGeneratedPattern = None. This is also an useful resource for this problem: entityframework.net/identity-insert – Hector

The wrapping Transaction is actually crucial: Without it EF will wrap each call to ExecuteSqlCommand in its own transaction and Identity Insert would not be switched on for the following statement. Great answer! – Finedrawn

Don't seem to work too well if you want one part of your application to insert identities using the same DbContext while leaving the rest untouched. Got sick and tired fighting EF6 so I decided for that isolated scenario where I wanted to control the identity column, I could re-use the same DbContext, and line out context.Database.ExecuteSqlCommands one for each of: 1) SET IDENTITY_INSERT ON, 2) Parameterized SQL, 3) SET IDENTITY_INSERT_OFF. This is strictly for identity column updates. I can then just follow this up with a regular EF6 update if I need to do more work. Done. – Goyette

ExecuteSqlCommand is out of date now. Use ExecuteSqlRaw or 'ExecuteSqlInterpolated` – Journeywork

This worked in EF7 with some changes: ctx.Database.CreateExecutionStrategy().ExecuteInTransaction(...) for the transaction and ctx.Database.ExecuteSqlRaw(...) for the set identity_insert. – Mcnutt

Answer 3

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

Ecklund

Hmm, that Property code would have to be done in OnModelCreating wouldn't it? There wouldn't be some way to indicate that information to EF after OnModelCreating has been executed? – Feldt

Big problems? Not if it was just a one-off right when you initially ran the application, to seed the existing data. It would also be highly useful for quickly setting up development databases, in a DB-agnostic way (sure you can do this with TSQL, but there you're locked into SQL Server). – Feldt

Yeah I had a Friday afternoon brain fault, hence I removed my comment. I still think creating an entity specially for performing your identity inserts is a possible option if you can't find a better way of doing it. Let us know what you come up with, I'll be interested to know! – Ecklund

You can apply the DatabaseGenerationOption conditionally. I explained how I did it in my answer. – Jealous

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. – Gere

The funny business you are referring to is creating a transaction, which should be considered a necessary part of a query like this. If you forgo the transaction, other calls to the database at the same time that expect IDENTITY_INSERT to be ON will be affected. – Renee

@Renee no. The "funny business" I was referring to was manually manipulating the SQL connection, i.e. what happens in the body of InitializeEntities from the OP. – Ecklund

For anyone coming from a "Database First" EF model - Open the Design and right-click your column, open Properties. Switch [StoreGeneratedPattern] to [none]. – Hector

Answer 4

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!

Runyon

Hey your solutions works for the update purpose but there is one problem coming now in my side is that it just starts the first record add with primary key 0. – Charlatan

Day saver, solutions for modifying columns is not ideal because you would need to update a production server twice. This works perfect. Just keep in mind that when you know you will be inserting the first record, remove the -1. – Snigger

Why isn't the first row being inserted with the correct ID? – Troopship

If the insert code can possibly be run concurrently (e.g. part of a typical website), make sure to wrap the SQL command and the row add in a transaction. Otherwise, occasionally, your app will thinking the new object has one ID while SQL Server will have stored a different one. Loads of fun to debug! – Hardwick

@EdwardBrey How do you wrap the three command above in a transaction when using EF? – Runyon

Using the transaction support built into EF or EF Core. – Hardwick

Answer 5

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

Brandiebrandise

This very approach is described in zeffron.wordpress.com/2016/06/03/… for more details – Chaconne

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.

Olecranon

I think the format needs to be $"DBCC CHECKIDENT ('{tableName}', RESEED, {actualId - 1});" – Congruence

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.

Quevedo

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

Concussion

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 :

Schaaff

Answer 10

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

Protozoology

Answer 11

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

Stepdame

Because I'm initializing the data from another database. It's a whole lot simpler to keep the identity values from that DB and keep the foreign key references the same. – Feldt

Identity values should always be treated as arbitrary. If they have some intrinsic value (as you seem to be ascribing to them), don't make the column an identity column. – Stepdame

That applies under normal circumstances, but this is data seeding with some very specific data. I would argue that it doesn't apply under this special circumstance. Indeed if you were seeding the database from a SQL script, you would be likely to turn IDENTITY_INSERT ON while you inserted the data, and specify the identity field values. – Feldt

@BenThul This is standard practice when software come with out of box/system values and then client adds own values via software. for example, system values have identity column values negative. And during usage, positive values are added. Then you have upgrade to your soft and you add more values to scripts - these all negative and don't interfere with client's values. – Thynne

@Thynne - for something that you say is standard, I've not seen it in the intervening ten years since I wrote up this answer. Which, by the way, the tide of time has provided even better solutions for this problem. Counterpoint to your observation: I would also consider it an anti-pattern to mingle vendor and user data in the same table. – Stepdame

How many products have you released into the wild in 10 years? User data can have 2 forms - actual input, and dynamic metadata used to describe the process of user data input. When it comes to the metadata, I've seen this done in several places. Vendor metadata comes in negative, custom metadata in positive. If the client wants to start over - delete all the positive metadata and you have a product like out of the box. And, as I said before - helps with upgrades. We know exactly the IDs of the records that need to be scripted without destroying custom input – Thynne

"We know exactly the IDs of the records that need to be scripted without destroying custom input" - so too would you know this if you didn't colocate the data. Want to blow away the user data? Truncate the user data table. Want to completely change the vendor supplied data? Do whatever you want in that separate table. Honestly, the way you're describing it seems like a hammer in search of a nail. – Stepdame

@BenThul think this. if I had some boilerplate metadata, we know IDs, then user adds theirs, then we do upgrade, we don't know what IDs there would be using autoincrement. Now. In this case we need to have 2 tables. But if we have our IDs negative and customer goes with autoincrement, we're not in the way of each other. Especially Important when some app code has there IDs hardcoded in specialized objects. Or, lets say enums. Patten vs anti-patter... arguable. – Thynne

Answer 12

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

Colorable

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.

Phore

@GertArnold, I've added context for my 'highly limited solution'. My hope is that it will help someone in a similar situation to the one I found myself in. If it doesn't help you, I'd recommend looking at one of the other excellent answers. – Phore

@GertArnold, I find your attitude bizarre. It is a solution that deals with a specific scenario. Your only objection (that you've mentioned) appears to be that you think it is unlikely to have a table with no required columns. I agree, it's not typically indicative of good design. And yet, that was the situation I had. I believe my solution is less invasive than others for my specific requirements. I think I have provided enough context (now) that it will help another person in my predicament. I think most people will be able to work that out. And I think you need to chill out. – Phore

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.

Lori

This is pretty awful and is vulnerable to SQL Injection - please don't do it like this, not ever! – Octangular

I agree its awful but I only use it when seeding the DB not in live code, so SQL Injection isn't an issue. – Lori

This is just about the only thing that will work. Manual insertion. Should protect against SQL injection, but it's the right approach since EF is insufferable. – Finish