How to turn on identity-insert in .net core [duplicate]

Question

I made a few tables in EF and entered in some seed data where I give value to a few columns with a primary key. When I run the application I am getting the error message:

Cannot insert explicit value for identity column in table 'Persons' when IDENTITY_INSERT is set to OFF.

How do I turn it on? I read on here to use:

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

above the property that is a primary key. I am still getting the same error message unfortunately. Please help.

I added [DatabaseGenerated(DatabaseGeneratedOption.None)] to all my properties that have a primary key. When I ran the migration I can see that the identity column is removed, But I am still getting the same error message.

When I go into SQL SEO I can still see the identity column on my primary key. I tried refreshing the database. What am I doing wrong? The only thing I can do is go into properties and remove the identity, but why can't I do it the way mentioned above?

Crypt

Do you want the primary key column to never be an identity column, or just temporarily turn off it's identity-ness so you can manually assign some key values for a few records? – Agatha

The SQL statement to turn IDENTITY_INSERT off is SET IDENTITY_INSERT [dbo].[mytable] OFF assuming your table name is mytable and your schema is dbo. The reason for IDENTITY_INSERT being set to off is to prevent you from manually setting the value for the primary key. – Stupidity

The error message indicates that I need to turn on identity_insert. I am trying to temporary add seed data where values are entered into a primary key. Do I use the sql statement (not sure where to even type it in) or do I use the data annotation above? – Crypt

Marked as duplicate because that really is the only answer. The error is SQL Server telling that there is an identity column in an insert statement. Doing that in SQL requires setting IDENTITY INSERT ON for the table, but that's not what should be done here. The only correct solution is: prevent EF from inserting identity values, by adding the DatabaseGeneratedOption.Identity option, or the corresponding fluent mapping. It was tried here, but not on the right entity or column, or there was a conflicting fluent mapping, which always takes precedence. – Skull

The so-called duplicate refers to Entity Framework classic, not EF Core. Not to mention that this question gets much more activity than that one. Voting to reopen. – Epigene

@Alexei-checkCodidact I tend to agree with your reasoning, but I'm not sure what you expect from reopening it. Almost all answers are variations on "execute SET IDENTITY_INSERT within a transaction". There's really no need for any other answers in that vein. Also, the question, as it has evolved, is very unclear. It's not clear why OP had the issue (no minimal reproducible example). We can't tell if they have to change the data or the database, it's not even clear if they had identity columns and if they did, if they want to keep them. So, when reopened I'd immediately vote to close it as unclear. – Skull

@GertArnold I agree that the post body is confusing, but for this one the title says all and does a pretty good job at helping people with the underlying problem - how to identity-insert in .net core? With 35-0 votes and almost 60K views this post managed to help quite a few developers, which is one of the main goals of SO. – Epigene

Voting to reopen. The linked question is not a duplicate. Myself and apparently many others come here looking to answer the question, "how do I turn IDENTITY_INSERT on/off for a set of operations in EF Core", and the linked question deals entirely with configuring the EF model. Seeing that a question is marked duplicate dissuades people from reading this question. – Vinous

Answer 1

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

Whitley

is there way to do on/off once for 20 tables – Daemon

@Daemon SQL Server only allows IDENTITY_INSERT to be enabled for one table at a time. If you try to set it for another table before turning it off for the previous table SQL server will throw an error: learn.microsoft.com/en-us/sql/t-sql/statements/… – Melinamelinda

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

Kurys

This solution 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. https://mcmap.net/q/399024/-how-to-turn-on-identity-insert-in-net-core-duplicate – Quadripartite

Answer 3

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

Epigene

This code uses context.Model.FindEntityType(typeof(T)).Relational().TableName to bypass the assumption that the model class name is the same as the table name: github.com/aspnet/EntityFrameworkCore/issues/11586 – Katmandu

@Katmandu - that's better. I will give a try and update the answer. Thanks. – Epigene

Do you need to call save changes after executing a sql command? – Embolism

Answer 4

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 

Quadripartite

You should replace the second await context.EnableIdentityInsertAsync<T>(); with DisableIdentityInsertAsync. :) – Loosestrife

Thanks @SelçukÖztürk – Quadripartite

Answer 5

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

Kickback

Just to point out if you get this error in the ExecuteSqlInterpolated method: cannot convert from string to System.FormattableString in VS, it won't stop it from working, but simply use it this way if you want it gone with a dollar string interpolation symbol – db.Database.ExecuteSqlInterpolated($"SET IDENTITY_INSERT MyDB.Users ON"); – Taoism

Thanks, Mohammad. It works for me with the Async method too (.Net core5). SaveChangesAsync() – Vittoria

Answer 6

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.

Artemisa

Answer 7

Below solution worked for me.(Link) I have added below annotations. and removed [Key] Annotation.

[KeyAttribute()]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Id { get; set; }

Namespace can be changed according to the entity framework version. For Entity framework core namespace is System.ComponentModel.DataAnnotations.Schema I did not face a data migration since I have tried in a new project.

Robedechambre

Answer 8

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

Taoism

Answer 9

If you don't want to use EF core's auto-generating primary key values feature, you can turn it off. You can add your data to the primary key It should resolve the error - Set Identity Insert off

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.None)]

public int StudentId { get; set; }

Setting Database Generation option to None helped me. You can find more about it here- https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations

Clywd

This helped me, but to get to actual code to this answer, the above link has another link to here that did it for me. – Dextrad

Answer 10

In order to add related entities with an object graph using the DbContext I used a DbCommandInterceptor which automatically sets INSERT_IDENTITY ON for the table in question and then OFF after the insert. This works with IDs manually set and DbContext.SaveChanges. I used it in my integration tests but after a performance optimization maybe it could be suitable for production code in some cases. Here is my answer to a similar SO question which explains the details.

Trieste

Answer 11

Use "SET IDENTITY_INSERT [table] ON/OFF" into transaction

    public static void TranslateDatabase(ref BDVContext bdvContext) 
    {
        bdvContext.Foro.RemoveRange(bdvContext.Foro);
        bdvContext.SaveChanges();

        using (var transaction = bdvContext.Database.BeginTransaction()) 
        { 

            bdvContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[Foro] On");

            using (old_balsaContext db = new old_balsaContext()) 
            {
                long id = 0;
                foreach (ForoA77 post in db.ForoA77.Where(x => x.Fecha > new DateTime(2000,1,1) & x.IdPadre == 0 ) )
                {
                    bdvContext.Foro.Add(new Foro 
                    {
                          Id = ++id
                        , ParentId = 0
                        , EditId = 0
                        , IdDomains = 2
                        , UserNick = post.IdUsuario == 1 ? bdvContext.Users.Where(x => x.Id == 2).Single().User : post.Nick?? ""
                        , IdUsers = post.IdUsuario == 1 ? (int?)2 : null
                        , Title = post.Asunto?? ""
                        , Text = post.Texto?? ""
                        , Closed = post.Cerrado?? false
                        , Banned = post.Veto?? false
                        , Remarqued = post.Remarcado?? false
                        , Deleted = false
                        , Date = post.Fecha?? new DateTime(2001,1,1)
                    });
                }
            }

            bdvContext.SaveChanges();

            bdvContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [dbo].[Foro] Off");

            transaction.Commit();
        }
    }

Note, my entityframework was generated by reverse engineering

Dominant