Why does this EF insert with IDENTITY_INSERT not work?

c# entity-framework entity-framework-6 identity-insert

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.

1
10
2/4/2017 7:52:27 AM

Accepted Answer

This must never be used in production code,it is just for fun
I see that mine is still the accepted answer,again, not do use this (to solve this problem), check the other answers below

I do not suggest this because it is a crazy hack but anyway.

I think we can achieve it by intercepting the SQL command and changing the command text
(you can inherit from DbCommandInterceptor and override ReaderExecuting)

I don't have a working example at the moment and I have to go but I think it is doable

Sample code

    public class MyDbInterceptor : DbCommandInterceptor
    {
        public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {

            if (is your table)
            {
                command.CommandText = "Set Identity off ,update insert into ,Set Identity off"
                return;
            }
            base.ReaderExecuting(command, interceptionContext);

        }

    }

ORMs are a nice abstraction and I really like them but I don't think it makes sense to try to "hack" them to support lower(closer to the db) level operations.
I try to avoid stored procs but I think in this (as you said exceptional) case I think you should use one

2
2/28/2020 9:44:53 AM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow