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

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

Answer 2

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 3

To force EF writing ID of your entity, you have to configure the ID as not store generated otherwise EF will never include the ID in the insert statement.

So, you need to change the model on the fly and configure the entity ID as you need.
The problem is that the model is cached and is quite tricky to change it on the fly (I'm quite sure I've done it but actually I can't find the code, probably I throwed it away). The shortest way is to create two different contexts where you configure your entity in two different ways, as DatabaseGeneratedOption.None (when you need to write the ID) and as DatabaseGeneratedOption.Identity (when you need the autonumbering ID).

Answer 4

I had a similar issue. In my production code the entities are relying on identity generation. But for integration testing I need to manually set some IDs. Where I don't need to set them explicitly I generated them in my test data builders. To achieve this I created a DbContext inheriting the one in my production code and configured the identity generation for each entity like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Entity1>().Property(e => e.Id).ValueGeneratedNever();
    modelBuilder.Entity<Entity2>().Property(e => e.Id).ValueGeneratedNever();
    ...
}

But that wasn't enough and I had to disable the SQL Server IDENTITY_INSERT. This worked when inserting data in a single table. But when you have entities related to one another and you want to insert a graph of objects this fails on DbContext.SaveChanges(). The reason is that as per SQL Server documentation you can have IDENTITY_INSERT ON just for one table at a time during a session. My colleague suggested to use a DbCommandInterceptor which is similar to the other answer to this question. I made it work for INSERT INTO only but the concept could be expanded further. Currently it intercepts and modifies multiple INSERT INTO statements within a single DbCommand.CommandText. The code could be optimized to use Span.Slice in order to avoid too much memory due to string manipulation but since I couldn't find a Split method I didn't invest time into this. I am using this DbCommandInterceptor for integration testing anyway. Feel free to use it if you find it helpful.

/// <summary>
/// When enabled intercepts each INSERT INTO statement and detects which table is being inserted into, if any.
/// Then adds the "SET IDENTITY_INSERT table ON;" (and same for OFF) statement before (and after) the actual insertion.
/// </summary>
public class IdentityInsertInterceptor : DbCommandInterceptor
{
    public bool IsEnabled { get; set; }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        if (IsEnabled)
        {
            ModifyAllStatements(command);
        }

        return base.ReaderExecuting(command, eventData, result);
    }

    private static void ModifyAllStatements(DbCommand command)
    {
        string[] statements = command.CommandText.Split(';', StringSplitOptions.RemoveEmptyEntries);
        var commandTextBuilder = new StringBuilder(capacity: command.CommandText.Length * 2);

        foreach (string statement in statements)
        {
            string modified = ModifyStatement(statement);
            commandTextBuilder.Append(modified);
        }

        command.CommandText = commandTextBuilder.ToString();
    }

    private static string ModifyStatement(string statement)
    {
        const string insertIntoText = "INSERT INTO [";
        int insertIntoIndex = statement.IndexOf(insertIntoText, StringComparison.InvariantCultureIgnoreCase);
        if (insertIntoIndex < 0)
            return $"{statement};";

        int closingBracketIndex = statement.IndexOf("]", startIndex: insertIntoIndex, StringComparison.InvariantCultureIgnoreCase);
        string tableName = statement.Substring(
            startIndex: insertIntoIndex + insertIntoText.Length,
            length: closingBracketIndex - insertIntoIndex - insertIntoText.Length);

        // we should probably check whether the table is expected - list with allowed/disallowed tables
        string modified = $"SET IDENTITY_INSERT [{tableName}] ON; {statement}; SET IDENTITY_INSERT [{tableName}] OFF;";
        return modified;
    }
}

Answer 5

The answer works for Entity Framework 6 Just use IDENTITY_INSERT outside transaction

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

Answer 6

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

Answer 7

Even if you turn off IDENTITY_INSERT, you have just told SQL that I will send you Identity, you did not tell entity framework to send Identity to SQL server.

So basically, you have to create DbContext as shown below ..

// your existing context
public abstract class BaseAppDbContext : DbContext { 


    private readonly bool turnOfIdentity = false;
    protected AppDbContext(bool turnOfIdentity = false){
        this.turnOfIdentity = turnOfIdentity;
    }


    public DbSet<IdentityItem> IdentityItems {get;set;}

    protected override void OnModelCreating(DbModelBuilder modelBuilder){
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<IdentityItem>()
           .HasKey( i=> i.Id )

           // BK added the "Property" line.
           .Property(e => e.Id)
           .HasDatabaseGeneratedOption(
               turnOfIdentity ?
                   DatabaseGeneratedOption.None,
                   DatabaseGeneratedOption.Identity
           );

    }
}

public class IdentityItem{

}


public class AppDbContext: BaseAppDbContext{
    public AppDbContext(): base(false){}
}

public class AppDbContextWithIdentity : BaseAppDbContext{
    public AppDbContext(): base(true){}
}

Now use it this way...

using (var db = new AppDbContextWithIdentity())
{
    using(var tx = 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");
       tx.Commit();
    }
}

Answer 8

I had a very similar problem.

The solution was something like:

db.Database.ExecuteSqlCommand("disable trigger all on  myTable ;") 
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT myTable  ON;");
db.SaveChanges();
db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT myTable  OFF");
db.Database.ExecuteSqlCommand("enable trigger all on  myTable ;") 

In my case, the message Explicit value must be specified for identity... was because on insert a trigger called and would insert something else.

ALTER TABLE myTable NOCHECK CONSTRAINT all

Can also be usefull