Why does this EF insert with IDENTITY_INSERT not work?

Question

This is the query:

using (var db = new AppDbContext())
{
    var item = new IdentityItem {Id = 418, Name = "Abrahadabra" };
    db.IdentityItems.Add(item);
    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Test.Items ON;");
    db.SaveChanges();
}

When executed, the Id of the inserted record, on a new table, is still 1.

NEW: When I use either the transaction, or TGlatzer's answer, I get the exception:

Explicit value must be specified for identity column in table 'Items' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Answer 1

I did not honor the tags of the question telling this is about EF6.
This answer will work for EF Core

The real culprit here is not the missing transaction, but the small inconvenience, that Database.ExectueSqlCommand() will not keep the connection open, when not explicitly opened before.

using (var db = new AppDbContext())
{
    var item = new IdentityItem {Id = 418, Name = "Abrahadabra" };
    db.IdentityItems.Add(item);
    db.Database.OpenConnection();
    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Test.Items ON;");
    db.SaveChanges();
}

will also do, since SET IDENTITY_INSERT [...] ON/OFF will be bound to your connection.

Answer 2

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 db = new AppDbContext())
using (var transaction = db .Database.BeginTransaction())
{
    var item = new IdentityItem {Id = 418, Name = "Abrahadabra" };
    db.IdentityItems.Add(item);
    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Test.Items ON;");
    db.SaveChanges();
    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Test.Items OFF");
    transaction.Commit();
}