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

Question

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

wertzui

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

View more on Stack Overflow

wertzui · Accepted Answer · 8/5/2019 3:03:30 AM

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

You can only have IDENTITY_INSERT turned ON for one table at a time
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();
    }
}

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related