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
It was a mix of the comment of @damien-the-unbeliever and the suggested answer of @ivan stoev
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 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();
}