Cannot use IDENTITY_INSERT before AddRange to seed database

[JT-Bruch]

After scaffolding a database with the Migration tool, I am attempting to seed a database using data pulled from an existing database. This data has been generated using the Generate Scripts tool inside SSMS and then converted to code (lists of data models) and then called by using Entity.AddRange().

Many of the entities have pre-existing relationships & keys that need to be preserved for the application to function properly. I attempted to use a number of different methods to allow for SET IDENTITY_INSERT ON but nothing I have tried has worked.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Microsoft.EntityFrameworkCore.DbUpdateException occurred
  HResult=0x80131500
  Message=An error occurred while updating the entries. See the inner exception for details.
  Source=<Cannot evaluate the exception source>
  StackTrace:
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(Tuple`2 parameters)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at CmsDatabaseSeed.CmsDatabaseContext.EnsureSeedData() in 
Inner Exception 1:
SqlException: Cannot insert explicit value for identity column in table 'AclsRef' when IDENTITY_INSERT is set to OFF.

Steps to reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

EntityTable DataModel

public partial class EntityTable
{
    public int Id { get; set; }
    public int? Occur { get; set; }

    public static IEnumerable<EntityTable> EnsureSeedData()
    {
      var seedData = new List<EntityTable>
      {
        new EntityTable() {Id = 710, Occur = 1},
        new EntityTable() {Id  = 685, Occur = 1},
        new EntityTable() {Id  = 719, Occur = 1},
      }
   }
}

ModelCreation

      modelBuilder.Entity<EntityTable>(entity =>
      {
        entity.HasKey(e => e.Id)
          .HasName("PK_EntityTable");

        entity.Property(e => e.Id).HasColumnName("id");

        entity.Property(e => e.Occur).HasColumnName("occur");
      });

Context Setup

      using (var dbContext = new DatabaseContext("data source=;initial catalog=NewDatabase;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework", DbTypeEnum.SqlServer))
      {
        dbContext.Database.EnsureDeleted();
        var serviceProvider =  dbContext.GetInfrastructure<IServiceProvider>();
        var logFactory = serviceProvider.GetService<ILoggerFactory>();
        logFactory.AddProvider(new SqlLoggerProvider());

        dbContext.Database.Migrate();
        
        Console.WriteLine($"Number of items added: {dbContext.EnsureSeedData()}");
        Console.ReadLine();
        
      }

EnsureSeedData Attempt #1

    public int EnsureSeedData()
    {
      if (!AllMigrationsApplied())
      {
        throw new Exception("Migrations not applied.");
      }
      EntityTable.AddRange(CmsDatabaseSeed.Models.EntityTable.EnsureSeedData());
      SaveChanges();  // <---- Exception occurs here
    }

EnsureSeedData Attempt #2

    public int EnsureSeedData()
    {
      if (!AllMigrationsApplied())
      {
        throw new Exception("Migrations not applied.");
      }
      EntityTable.AddRange(CmsDatabaseSeed.Models.EntityTable.EnsureSeedData());
      Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[EntityTable] ON;")
      SaveChanges();  // <---- Exception occurs here
    }

This is the log for the above.

SET IDENTITY_INSERT [dbo].[EntityTable] ON;
SET NOCOUNT ON;
INSERT INTO [EntityTable] ([id], [occurs])
VALUES (@p0, @p1,
(@p2, @p3,
(@p,4 @p5,

Still fails.

EnsureSeedData Attempt #3

    public int EnsureSeedData()
    {
      if (!AllMigrationsApplied())
      {
        throw new Exception("Migrations not applied.");
      }
      var seedData = EntityTable.EnsureSeedData()
      EntityTable.AttachRange(seedData);
      Entry(seedData).State = EntityState.Added;
      SaveChanges();  // <---- Exception occurs here
    }

This is the exception for the above.

System.InvalidOperationException: 'The entity type 'List<EntityTable>' was not found. Ensure that the entity type has been added to the model.'

Still fails.

Further technical details

EF Core version: 1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017

[ajcvickers]

@JT-Bruch This is the same issue as described here: #703. Are you sure that you are setting IDENTITY_INSERT on for the correct table? In the SQL you posted it is set on for AclsRef, but then the insert is for Acls.

[JT-Bruch]

Yeah I am.. I was trying to generalize the code and messed up on the copy/paste. :) I will update and verify that it still is not working.

[JT-Bruch]

@ajcvickers I was able to figure it out. Needed the OpenConnection command before the SQL.

Here is a repo for where I was able to repro it for those in the future.

https://github.com/JT-Bruch/TestDatabaseSeed