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

Question

Using the following code, I get the error that states I need to put identity insert to ON for [Tournaments].[Tournaments] and [Games].[Game]

 if (!context.Items.Any())
        {
            context.Database.OpenConnection();
            context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [Items].[Items] ON;");
            Tournament Tournament = _tournamentDbContext.Tournaments.Where(x => x.Id == 1).First();
            Game _Game = _gameDbContext.Games.Where(x => x.Id == 1).First();
            ItemType ItemType = context.ItemTypes.Where(x => x.Id == 1).First();
            Item item = new Models.Items.Item
            {
                Id = 1,
                Name = "Test",
                ItemType = ItemType,
                Price = 100,
                Short_Description = "TestShort",
                Tournament = Tournament,
                Game = _Game,
                DateCreated = DateTime.Now
            };
            context.Items.Add(item);
            context.SaveChanges();
            context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [Items].[Items] OFF;");
            context.Database.CloseConnection();
        }

If I add the code which sets both identity_inserts on:

  if (!context.Items.Any())
        {
            context.Database.OpenConnection();
            context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [Items].[Items] ON;");
            context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [Tournaments].[Tournaments] ON;");
            context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [Game].[Games] ON;");
            Tournament Tournament = _tournamentDbContext.Tournaments.Where(x => x.Id == 1).First();
            Game _Game = _gameDbContext.Games.Where(x => x.Id == 1).First();
            ItemType ItemType = context.ItemTypes.Where(x => x.Id == 1).First();
            Item item = new Models.Items.Item
            {
                Id = 1,
                Name = "Test",
                ItemType = ItemType,
                Price = 100,
                Short_Description = "TestShort",
                Tournament = Tournament,
                Game = _Game,
                DateCreated = DateTime.Now
            };
            context.Items.Add(item);
            context.SaveChanges();
            context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [Items].[Items] OFF;");
            context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [Tournaments].[Tournaments] OFF;");
            context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [Game].[Games] OFF;");
            context.Database.CloseConnection();
        }

It errors, System.Data.SqlClient.SqlException: 'IDENTITY_INSERT is already ON for table 'aspnet-SpidShop-FA900737-72CD-4ABA-BDEF-598D51CB5A43.Items.Items'. Cannot perform SET operation for table 'Tournaments.Tournaments'.'

I have no idea what to do, as I'm not even changing Tournaments and Games tables, only Items. Any idea how to fix this issue?

UPDATE:

I got through the identity insert problems by deleting all migrations, disabling initialize method, then redoing the migrations.

I'm facing another problem:

if (!context.Items.Any())
        {
            Tournament Tournament = _tournamentDbContext.Tournaments.Where(x => x.Id == 1).First();
            Game Game = _gameDbContext.Games.Where(x => x.Id == 1).First();
            ItemType ItemType = context.ItemTypes.Where(x => x.Id == 1).First();
            Item item = new Models.Items.Item
            {
                Name = "Test",
                ItemType = ItemType,
                Price = 100,
                Short_Description = "TestShort",
                Tournament = Tournament,
                Game = Game,
                DateCreated = DateTime.Now
            };
            context.Items.Add(item);
            context.SaveChanges();
        }

The last line throws SqlException: Invalid object name 'Game'.

The model:

public class Item
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Short_Description { get; set; }
    public decimal Price { get; set; }
    public ItemType ItemType { get; set; }
    public Tournament Tournament { get; set; }
    public Game Game { get; set; }
    [DataType(DataType.Date)]
    public DateTime DateCreated { get; set; }
}

And my context

 public class ItemDbContext : DbContext
{
    private string Schema = "Items";
    public ItemDbContext(DbContextOptions<ItemDbContext> options) : base(options)
    {
    }

    public DbSet<Item> Items { get; set; }
    public DbSet<ItemType> ItemTypes { get; set; }
    public DbSet<ItemTag> ItemTags { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Item>().ToTable("Item", Schema);
        modelBuilder.Entity<ItemType>().ToTable("ItemType", Schema);
        modelBuilder.Entity<ItemTag>().ToTable("ItemTag", Schema);
    }

}

Game is not null, and it gets matched properly.

First decide if you want the database to generate the keys or if your .NET Code should generate the keys, then adjust your question. There is no point in configuring identity columns in your tables if you want to set the keys yourself. — Lasse V. Karlsen
Yes, I want the keys to be autogenerated. I set them myself here, because it's the seed method. Even if I remove "Id = 1", it throws the same error. — Ashley
I removed "Id = 1", and deleted IDENTITIY_INSERT ON for those 3 tables. I still get the same error. — Ashley
SET IDENTITY_INSERT can only be active for one table at a time — marc_s
That makes sense. I deleted all of them. I still get the same error, even though I'm not insert ANYTHING into Tournaments or Games. The error pops are SaveChanges(). Could you check the edited question? — Ashley

Ashley Ashley · Accepted Answer · 2018-01-25T17:15:31

I had multiple contexts, I put all in one, and removed IDs, as they should be autogenerated.

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

1 Answers