How to turn on identity-insert in .net core

Question

In EF Core 1.1.2, I got this to work with transactions. In my "database initializer" that put seed data into the tables. I used the technique from this EF6 answer. Here's a sample of the code:

using (var db = new AppDbContext())
using (var transaction = db.Database.BeginTransaction())
{
    var user = new User {Id = 123, Name = "Joe"};
    db.Users.Add(user);
    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT MyDB.Users ON;");
    db.SaveChanges();
    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT MyDB.Users OFF");
    transaction.Commit();
}

Improved solution based on NinjaCross' answer.

This code is added directly in the database context class and allows to save changes by also specifying that identity insert is needed for a certain type (mapped to a table).

Currently, I have only used this for integrative testing.

public async Task<int> SaveChangesWithIdentityInsertAsync<TEnt>(CancellationToken token = default)
{
    await using var transaction = await Database.BeginTransactionAsync(token);
    await SetIdentityInsertAsync<TEnt>(true, token);
    int ret = await SaveChangesExAsync(token);
    await SetIdentityInsertAsync<TEnt>(false, token);
    await transaction.CommitAsync(token);

    return ret;
}

private async Task SetIdentityInsertAsync<TEnt>(bool enable, CancellationToken token)
{
    var entityType = Model.FindEntityType(typeof(TEnt));
    var value = enable ? "ON" : "OFF";
    string query = $"SET IDENTITY_INSERT {entityType.GetSchema()}.{entityType.GetTableName()} {value}";
    await Database.ExecuteSqlRawAsync(query, token);
}

---

Steve Nyholm's answer works fine, but I will provide some extra explanation and some generic code with exception handling.

Normally the context takes care of the transaction, but in this case manually taking care of it is required. Why?

Database context will generate a BEGIN TRAN after the SET IDENTITY_INSERT is issued. This will make transaction's inserts to fail since IDENTITY_INSERT seems to affect tables at session/transaction level.

So, everything must be wrapped in a single transaction to work properly.

Here is some useful code to seed at key level (as opposed to table level):

Extensions.cs

[Pure]
public static bool Exists<T>(this DbSet<T> dbSet, params object[] keyValues) where T : class
{
    return dbSet.Find(keyValues) != null;
}

public static void AddIfNotExists<T>(this DbSet<T> dbSet, T entity, params object[] keyValues) where T: class
{
    if (!dbSet.Exists(keyValues))
        dbSet.Add(entity);
}

DbInitializer.cs

(assumes that model class name is the same as table name)

private static void ExecuteWithIdentityInsertRemoval<TModel>(AspCoreTestContext context, Action<AspCoreTestContext> act) where TModel: class
{
    using (var transaction = context.Database.BeginTransaction())
    {
        try
        {
            context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT " + typeof(TModel).Name + " ON;");
            context.SaveChanges();
            act(context);
            context.SaveChanges();
            transaction.Commit();
        }
        catch(Exception)
        {
            transaction.Rollback();
            throw;
        }
        finally
        {
            context.Database.ExecuteSqlCommand($"SET IDENTITY_INSERT " + typeof(TModel).Name + " OFF;");
            context.SaveChanges();
        }
    }
}

public static void Seed(AspCoreTestContext context)
{
    ExecuteWithIdentityInsertRemoval<TestModel>(context, ctx =>
    {
        ctx.TestModel.AddIfNotExists(new TestModel { TestModelId = 1, ModelCode = "Test model #1" }, 1);
        ctx.TestModel.AddIfNotExists(new TestModel { TestModelId = 2, ModelCode = "Test model #2" }, 2);
    });
}

Had to deal with the same issue and this seems to be a clean solution.

Credit to >> https://github.com/dotnet/efcore/issues/11586

I have made some changes so it now works with .Net Core 3.1 + (Tested in .Net 5) and also added this Method SaveChangesWithIdentityInsert

    public static class IdentityHelpers
{
    public static Task EnableIdentityInsert<T>(this DbContext context) => SetIdentityInsert<T>(context, enable: true);
    public static Task DisableIdentityInsert<T>(this DbContext context) => SetIdentityInsert<T>(context, enable: false);

    private static Task SetIdentityInsert<T>(DbContext context, bool enable)
    {
        var entityType = context.Model.FindEntityType(typeof(T));
        var value = enable ? "ON" : "OFF";
        return context.Database.ExecuteSqlRawAsync(
            $"SET IDENTITY_INSERT {entityType.GetSchema()}.{entityType.GetTableName()} {value}");
    }

    public static void SaveChangesWithIdentityInsert<T>(this DbContext context)
    {
        using var transaction = context.Database.BeginTransaction();
        context.EnableIdentityInsert<T>();
        context.SaveChanges();
        context.DisableIdentityInsert<T>();
        transaction.Commit();
    }

}

Usage

        var data = new MyType{SomeProp= DateTime.Now, Id = 1};
            context.MyType.Add(data);
        context.SaveChangesWithIdentityInsert<MyType>();

@Steve Nyholm answer is OK, But in .Net core 3 ExecuteSqlCommand is Obsolete, ExecuteSqlInterpolated replacement of ExecuteSqlCommand:

using (var db = new AppDbContext())
using (var transaction = db.Database.BeginTransaction())
{
    var user = new User {Id = 123, Name = "Joe"};
    db.Users.Add(user);
    db.Database.ExecuteSqlInterpolated($"SET IDENTITY_INSERT MyDB.Users ON;");
    db.SaveChanges();
    db.Database.ExecuteSqlInterpolated($"SET IDENTITY_INSERT MyDB.Users OFF");
    transaction.Commit();
}

Another way is to explicitly open a connection then SET IDENTITY_INSERT <table> ON.

var conn = context.Database.GetDbConnection();
if (conn.State != ConnectionState.Open)
    conn.Open();

 context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Posts ON");

 var post = new WeblogPost()
 {                    
               Id= oldPost.Pk,  //   <!--- explicit value to Id field
               Title = oldPost.Title,
                ...
 };
 context.Posts.Add(post);    
 conn.Close();

Apparently once a connection has been explicitly opened before an EF request, that connection is not automatically closed by EF, so the setting is applied to the same connection context.

This is the same reason that Steve's response with transactions works as transactions keep a connection alive.

Note: you don't want to put the connection into a using statement if you plan to use the same context again later in the application/request. The connection has to exist, so the best way to clear the connection context is to .Close() it, thereby returning EF to its default behavior of opening and closing the connection per operation.

The solution proposed by @sanm2009 contains some nice ideas.

However the implementation has some imperfections related to the misusage of Task/async/await.

The method SaveChangesWithIdentityInsert does not return Task, nor await for the calls to EnableIdentityInsert and DisableIdentityInsert.

This could lead to undesired side effects.

The following implementations supports both async/await, and non-awaitable paradigms.

#region IDENTITY_INSERT

        public static void EnableIdentityInsert<T>(this DbContext context) => SetIdentityInsert<T>(context, true);
        public static void DisableIdentityInsert<T>(this DbContext context) => SetIdentityInsert<T>(context, false);

        private static void SetIdentityInsert<T>([NotNull] DbContext context, bool enable)
        {
            if (context == null) throw new ArgumentNullException(nameof(context));
            var entityType = context.Model.FindEntityType(typeof(T));
            var value = enable ? "ON" : "OFF";
            context.Database.ExecuteSqlRaw($"SET IDENTITY_INSERT {entityType.GetSchema()}.{entityType.GetTableName()} {value}");
        }

        public static void SaveChangesWithIdentityInsert<T>([NotNull] this DbContext context)
        {
            if (context == null) throw new ArgumentNullException(nameof(context));
            using var transaction = context.Database.BeginTransaction();
            context.EnableIdentityInsert<T>();
            context.SaveChanges();
            context.DisableIdentityInsert<T>();
            transaction.Commit();
        }

        #endregion 

        #region IDENTITY_INSERT ASYNC

        public static async Task EnableIdentityInsertAsync<T>(this DbContext context) => await SetIdentityInsertAsync<T>(context, true);
        public static async Task DisableIdentityInsertAsync<T>(this DbContext context) => await SetIdentityInsertAsync<T>(context, false);

        private static async Task SetIdentityInsertAsync<T>([NotNull] DbContext context, bool enable)
        {
            if (context == null) throw new ArgumentNullException(nameof(context));
            var entityType = context.Model.FindEntityType(typeof(T));
            var value = enable ? "ON" : "OFF";
            await context.Database.ExecuteSqlRawAsync($"SET IDENTITY_INSERT {entityType.GetSchema()}.{entityType.GetTableName()} {value}");
        }

        public static async Task SaveChangesWithIdentityInsertAsync<T>([NotNull] this DbContext context)
        {
            if (context == null) throw new ArgumentNullException(nameof(context));
            await using var transaction = await context.Database.BeginTransactionAsync();
            await context.EnableIdentityInsertAsync<T>();
            await context.SaveChangesAsync();
            await context.DisableIdentityInsertAsync<T>();
            await transaction.CommitAsync();
        }


        #endregion 

Another way is to use ExecuteSqlRaw. Unlike ExecuteSqlInterpolated, you do not have to convert your passed string to a formattable string type.

using (var db = new AppDbContext())
using (var transaction = db.Database.BeginTransaction())
{
    var user = new User {Id = 123, Name = "Joe"};
    db.Users.Add(user);
    db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT MyDB.Users ON");
    db.SaveChanges();
    db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT MyDB.Users OFF");
    transaction.Commit();

` }