How can I force entity framework to insert identity columns?

**EF 6** method, using the [msdn article][1]:

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

[1]:

[To see links please register here]

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.

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

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.

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

``` c#
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.

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