Entity Framework Identity Insert Discover How to use IDENTITY_INSERT

In Entity Framework, when you have a primary key field such as Id or AuthorId which is mapped to IDENTITY column in the database, it works well when you insert data.

In some cases, you might need to insert explicit values into a SQL Server IDENTITY column.
To do so, you need to enable IDENTITY_INSERT before calling SaveChanges() manually.

Let's say we want to create a new author with explicit AuthorId value.

Author author = new Author()
{
    AuthorId = 1001,
    Name = "Johny",
    Books = new List<Book>
    {
        new Book() { Title = "Learn VB.NET"},
        new Book() { Title = "C# Fundamentals for Absolute Beginners"},
    }
};

But you can't insert an author with explicit AuthorId value directly in the identity column, so you will need to turn on the IDENTITY_INSERT before calling SaveChanges().

using (var context = new BookStore())
{
    Author author = new Author()
    {
        AuthorId = 1001,
        Name = "Johny",
        Books = new List<Book>
        {
            new Book() { Title = "Learn VB.NET"},
            new Book() { Title = "C# Fundamentals for Absolute Beginners"},
        }
    };
    context.Authors.Add(author);
    
    context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [dbo].[Authors] ON");
    context.SaveChanges();
    context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [dbo].[Authors] OFF");
}

Unfortunately, when SaveChanges is called the explicit identity value is ignored, and a new value generated by the database is used.

Try it

The solution is to create a new context class which will inherit our main context class and override the OnModelCreating.

public class TempBookStore : BookStore
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Author>()
          .Property(a => a.AuthorId)
          .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        base.OnModelCreating(modelBuilder);
    }
}

In the OnModelCreating function we can set the attribute on the entity to not generate auto values for AuthorId. You will also need to make sure to wrap the SQL command and the row in a transaction.

using (var context = new TempBookStore())
{
    using (var transaction = context.Database.BeginTransaction())
    {
        Author author = new Author()
        {
            AuthorId = 1001,
            Name = "Johny",
            Books = new List<Book>
            {
                new Book() { Title = "Learn VB.NET"},
                new Book() { Title = "C# Fundamentals for Absolute Beginners"},
            }
        };
        context.Authors.Add(author);

        context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [dbo].[Authors] ON");
        context.SaveChanges();
        context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [dbo].[Authors] OFF");
            
        transaction.Commit();
    }
}