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.

Penmanship

Based on this answer Entity Framework IDENTITY_INSERT ON doesn't work, you turned Identity Insert on just briefly, and it didn't include the insert. If its possible, try creating a big sql query string starting with "SET IDENTITY_INSERT Test.Items ON;" followed by your INSERT INTO... and finally "SET IDENTITY_INSERT Test.Items OFF;" (All in one query). Or, you can take a look at TransactionScope Class. – Roguery

@KeyurPATEL Using a direct INSERT query really defeats the purpose of an ORM. I much prefer the transaction option, but also prefer the built-in transaction, like in Aananda's answer. – Penmanship

@Penmanship : For the exception,can you please go to the model designer(edmx) and select the property Id (This is the Identity column) and modify StoreGenratedPattern to None instead of Identity ? You must change this,since you are inserting the identity column.This should fix the exception. – Emma

@Emma IDENTITY_INSERT is for exceptional tasks. If I change the pattern to none, then I won't get identity values on normal inserts. That is a terrible solution. – Penmanship

May be if nothing works, one option is to map stored procedure for insert for this entity. entityframeworktutorial.net/entityframework6/… . And you can pass a flag which would set identify insert depending on the flag value. – Flame

See #13086506 – Jaunty

The answer was in the first link in the first comment. Maybe a bit concealed, but the way to go is to open and close the db connection yourself. Could have saved you some rep points :) – Tichon

@GertArnold The main issue though is not how to maintain the connection, but how to let EF include the Id value in the insert command. – Alonso

Answer 1

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

Bronwen

On second thought,I think I'll delete this.I don't think you can get the Id you need from here... – Bronwen

Well,you can add a property on your context and then access it from interceptionContext.DbContexts.I won't delete the answer but I hope there is a better solution... – Bronwen

The techie in me loves the interception idea, but a stored proc is probably the neatest answer. – Penmanship

Oh, and I love the idea of the interceptor for inspecting the SQL queries, vs. the overkill of the SQL Profiler. This way I can even trace log the queries. – Penmanship

But ain't this execute for all the queries for that entity? Or am I missing something? Somewhere in the comments, I saw that you need this behavior-setting identify off - only for exceptional tasks. And yeah, command intercepters are really useful for logging queries; I do logging via intercepters based on some flag in the config so that I could control this. – Flame

@Flame I suspect Profk chose the sp approch. Yes,it executes for all queries .The IsYourTable method would have to check that the command is 1 Insert Into statement on the specific table. (the "interceptor" part of the answer is(might be) a solution to the problem but I also mentioned that it is not a good one,sp or something else that is working are better approaches) – Bronwen

Agreed. I had something in my mind - mapping stored proc for insert operation for that entity. EF supports this out of the box via fluent api - entityframeworktutorial.net/entityframework6/… .May be he could have a NotMapped property bool IsIdentityInsert in the entity and perform the identity on or off logic in stored proc. Again not sure whether this is an elegant way of achieving what OP needed. – Flame

I don't see any convincing evidence here that the ID value (that EF doesn't provide, see above comments) can be hacked into the insert command. – Tichon

@GertArnold You can add a property on the context (the user has to set it of course ) and then you can cast interceptionContext.DbContexts.First() to your context and access it. It doesn't make sense but you can pass some info to ReaderExecuting... – Bronwen

Well, a better way would probably be to get the ID value (or values, because in other scenarios multiple entities may be inserted) from the entity entries, but the hardest part is to modify the SQL adequately. Even if it works, it's unacceptably dirty imo. – Tichon

@GertArnold I agree it's unacceptably dirty and I don't think anyone can update the sql safely and correctly that's why I suggested the stored proc solution. I liked Akash Kava's answer but I guessed it didn't work since the OP didn't mark it as the solution. – Bronwen

So did I. Well, I'm glad we agree, and you already demonstrated some healthy self-evaluation. After having been around a couple of years I'm beyond surprise about OP's decisions what to accept :) – Tichon

@GertArnold I would never use something like that and I'm pretty sure ProfK (or any other sane person) is not going to use it . It is an interesting abuse of a feature though (and someone can think of a valid use case for it ,after all it is allowed) – Bronwen

I used this approach for my integration tests. My answer is below - https://mcmap.net/q/408992/-why-does-this-ef-insert-with-identity_insert-not-work. – Moitoso

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

Alkoran

Answer 3

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.

Pestana

I get an exception trying this, and with the other answer as well. Please see my edit to the OP for details. – Penmanship

Oops. I somehow assumed EF Core.. My Code works in EF Core :) - Or worked until your edit – Pestana

Haha, a rock and a hard place. The code, as changed by me, compiles and runs for me on EF 6, but still doesn't do what is intended. – Penmanship

For EF Core 2 this code actually works. Using OpenConnection before ExecuteSqlCommand keeps the connection open and the identity insert is possible. – Tense

after spending 4 hours this finally worked – Diphyllous

Answer 4

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

Denunciate

But even then, I will have to do a migration each time I use a different one of the two contexts. If the column is an Identity column, and I use the context with DatabaseGeneratedOption.None, I will get an exception from the db when I try and insert a value into that column. I think it better to use a stored proc for the very rare event I need identity insert. – Penmanship

After several tries, I stopped to migrate data content using EF as well. I do data migration using ADO.Net. Stored procedures is the fastest (best) way if you don't need to target different DBMSs – Denunciate

The code you forgot could be modelBuilder.Conventions.Add<System.Data.Entity.ModelConfiguration.Conventions.StoreGeneratedIdentityKeyConvention>(); in overriding the OnModelCreating method of DbContext. – Beore

Answer 5

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

Moitoso

Note that EF sometimes relies on the number of inserted items (i.e. uses @@ROWCOUNT). These queries will not work because @@ROWCOUNT is set to 0 after calling SET IDENTITY_INSERT [{tableName}] OFF; – Promise

Great solution but be careful: if you're using SaveChangesAsync, this will NOT work, You have to also override ReaderExecutingAsync, whose code is almost the same. – Physician

Answer 6

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

Joanne

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

Insentient

I like your thinking here, but it does seem you haven't tried your code. Reconfiguring IdentityItem causes the EF internal model to change, so the query throws the following exception: System.InvalidOperationException: The model backing the 'AppDbContext' context has changed since the database was created.. And you can't chain HasDatabaseGeneratedOption off HasKey, I had to do it like my edit in your code. – Penmanship

@Penmanship how about now? Creating two different context, this will cause model to be created once per context type. Sorry I forgot about the cache. If this does not work then only alternative is to create two separate contexts. – Insentient

I think this is a working solution. It's not a bad idea anyway to have specialized contexts for special tasks or for several segregations of an application. – Tichon

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

Radbun