EF Core "Cannot insert explicit value for identity column" although IDENTIY_INSERT is set to ON

c# entity-framework-core sql-server

Question

I'm using Entity Framework Core 2.1 and want to Insert some Data using IDENTITY_INSERT. Although I'm setting IDENTITY_INSERT to On, i still get an exception on the line identityContext.SaveChanges();:

Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.'

Inner Exception

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

This is my code:

using (var identityContext = new IdentityDatabase(identityOptions))
{
    Console.WriteLine("Settings Identity Insert on");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ClientGroups ON");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Clients ON");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ModulePermissions ON");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Roles ON");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Users ON");

    identityContext.Clients.AddRange(identityclients);
    identityContext.ClientGroups.AddRange(identityClientGroups);
    identityContext.ClientGroupAssociations.AddRange(identityClientGroupAssociations);
    identityContext.AuthClients.AddRange(identityAuthClients);
    identityContext.Roles.AddRange(identityRoles);
    identityContext.Users.AddRange(identityUsers);
    identityContext.UserRoles.AddRange(identityUserRoles);
    identityContext.ModulePermissions.AddRange(identityModulePermissions);
    Console.WriteLine("Saving Data to .identity Database");
    identityContext.SaveChanges();

    Console.WriteLine("Setting Identity Insert off");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ClientGroups OFF");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Clients OFF");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ModulePermissions OFF");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Roles OFF");
    identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Users OFF");
}

I turned logging on and can see that the command was sent to the databse:

info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SET IDENTITY_INSERT dbo.ClientGroups ON info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SET IDENTITY_INSERT dbo.Clients ON info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SET IDENTITY_INSERT dbo.ModulePermissions ON info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SET IDENTITY_INSERT dbo.Roles ON info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SET IDENTITY_INSERT dbo.Users ON

1
1
8/13/2018 11:25:00 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

It was a mix of the comment of @damien-the-unbeliever and the suggested answer of @ivan stoev

  1. You can only have IDENTITY_INSERT turned ON for one table at a time
  2. The code must be wrapped in a transaction because EF Core will use separate connections otherwise.

Here is the code

using (var identityContext = new IdentityDatabase(identityOptions))
{
    using (var transaction = identityContext.Database.BeginTransaction())
    {
        Console.WriteLine("Inserting AuthClients");
        identityContext.AuthClients.AddRange(identityAuthClients);
        identityContext.SaveChanges();

        Console.WriteLine("Inserting ClientGroups");
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ClientGroups ON");
        identityContext.ClientGroups.AddRange(identityClientGroups);
        identityContext.SaveChanges();
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ClientGroups OFF");

        Console.WriteLine("Inserting Clients");
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Clients ON");
        identityContext.Clients.AddRange(identityclients);
        identityContext.SaveChanges();
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Clients OFF");

        Console.WriteLine("Inserting ClientGroupAssociations");
        identityContext.ClientGroupAssociations.AddRange(identityClientGroupAssociations);
        identityContext.SaveChanges();

        Console.WriteLine("Inserting Users");
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Users ON");
        identityContext.Users.AddRange(identityUsers);
        identityContext.SaveChanges();
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Users OFF");

        Console.WriteLine("Inserting Roles");
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Roles ON");
        identityContext.Roles.AddRange(identityRoles);
        identityContext.SaveChanges();
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Roles OFF");

        Console.WriteLine("Inserting UserRoles");
        identityContext.UserRoles.AddRange(identityUserRoles);
        identityContext.SaveChanges();

        Console.WriteLine("Inserting ModulePermissions");
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ModulePermissions ON");
        identityContext.ModulePermissions.AddRange(identityModulePermissions);
        identityContext.SaveChanges();
        identityContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ModulePermissions OFF");

        Console.WriteLine("Commiting transaction");
        transaction.Commit();
    }
}
0
8/5/2019 3:03:30 AM

Popular Answer

EF Core opens / closes the connection for each database related operation, so setting the identity on with ExecuteSqlCommand has no effect.

You need to either explicitly keep the connection opened during the whole process:

identityContext.Database.OpenConnection();
try
{
    // your code …
}    
finally
{
    identityContext.Database.CloseConnection();
}

or wrap the whole process in explicit transaction:

using (var transaction = identityContext.Database.BeginTransaction())
{
    // your code …
    transaction.Commit();
}
Ivan Stoev

Related Questions

EF Core insert explicit value for identity column

When inserting the new SparePart instance, the MachineType and SpareType references need to be fetched from the same DbContext beforehand. Otherwise EF thinks you are trying to create a new MachineType and SpareType. And because the Id field is set, the database won't allow the insert. Something like so:...newItem.MachineType = _context.MachineType...
.net-core c# entity-framework-core
asked by Piittis

Cannot insert explicit value for identity column while updating entity

I am using ASP.NET Boilerplate template....I want to update ...Details... table, which contains more than one item. If an item exists, it must update, otherwise a new one must be added and all other entries relating to ...Master... primary key in ...Details... table must be deleted. But it is showing an error:...Cannot insert explicit value for ide...
aspnetboilerplate entity-framework entity-framework-6 identity-insert
asked by Nighil

EF Core: Column with ValueGeneratedOnAdd trows "Cannot insert explicit value for identity column..."

You are trying to initialize request.Result with new Result() which has no values. That may cause this error.
.net-core aws-lambda c# entity-framework entity-framework-core
asked by Riddhi

Cannot insert explicit value for identity column - into related table

My issue was that I needed to use my context to look up the actual entity, using info passed, instead of using the one with all the same values that was passed into my api directly.
entity-framework-core
asked by Chris

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

For my API I'm using Entity Framework Core with code first migrations. I've created some relations which are working fine. Now, I've added another relation (one to many) and suddenly I'm slapped around the ears with this error:...Cannot insert explicit value for identity column in table 'Companies' when IDENTITY_INSERT is set to OFF."...Offcourse, ...
asp.net-core entity-framework-core sql-server
asked by MysticEarth

Entity Framework Core - Cannot insert explicit value when saving to another table

IDbContextTransaction trn = testDBContext.Database.BeginTransaction(); Item itm = new Item(); itm.ItemId = 23; itm.ItemName = "item23"; Type typt = typeof(Item); DbSet<Item> Item1 = testDBContext.Set<Item>(); testDBContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Item] ON"); //Item1.FromSql("SET IDENTITY_INSERT [dbo].[Item] ON").Singl...
asp.net-core asp.net-core-mvc entity-framework-core sql sqlexception
asked by MADHAN

EF Core - Cannot insert explicit value for identity column SqlException

In my app I am using EF Core to define 2 entities (Code First), ...Meeting... and ...PostcodeDetail... defined as follows:...public class Meeting { public int Id { get; set;} public PostcodeDetail PostcodeDetail { get; set; } // other properties removed for brevity } public class PostcodeDetail { public int Id { get; set; } public IColl...
asp.net-core entity-framework-core
asked by Vinyl Warmth

"Cannot insert explicit value for identity column in table" when inserting object with EF Core

I am trying to insert a new row to my table, using EF Core, SQL Server and C#, but I am having trouble getting EF Core to use the identity column properly....Here's what I am doing:...I am creating a new object using the Entity Framework generated class (I've included the entity class definition at the end of my post)...EmployeePermissions employee...
c# entity-framework entity-framework-core sql-server
asked by Mr. King

Cannot insert explicit value for identity column in table 'xxx' when IDENTITY_INSERT is set to OF

Ensure to not overwrite the ...CriteriaGroup... with an untracked object, because in this case the ...CriteriaGroup... is considered new....The described issue could happen if for example you don't load the related ...CriteriaGroupFk... when loading the ...Accomplishment..., and afterwards assigning an untracked object to ...CriteriaGroupFk.... To ...
asp.net asp.net-core c# entity-framework-core
asked by user7217806

Cannot insert explicit value when IDENTITY_INSERT is set to OFF in Entity Framework

I found the solution to my problem. It may not be the cleanest code, but it worked just fine....//Create and add term var transaction = await db.Database.BeginTransactionAsync(); var termEntity = new Term { TermId = termId.Value }; if (db.Terms.Find(termId.Value) == null) { db.Terms.Add(termEntity); } else { db.Terms.Update(termEntity...
asp.net-mvc c# entity-framework-core ssms
asked by Laberge

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow