虽然IDENTIY_INSERT设置为ON，但EF Core“无法为标识列插入显式值”-Java 学习之路

我'm using Entity Framework Core 2.1 and want to Insert some Data using IDENTITY_INSERT. Although I' m将IDENTITY_INSERT设置为On，我仍然在 identityContext.SaveChanges(); 行上获得异常：

Microsoft.EntityFrameworkCore.DbUpdateException：'更新条目时发生错误 . 有关详细信息，请参阅内部异常 . 内部异常SqlException：当IDENTITY_INSERT设置为OFF时，无法在表'ClientGroups'中为identity列插入显式值 .

这是我的代码：

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");
}

我打开登录，可以看到命令被发送到数据库：

info：Microsoft.EntityFrameworkCore.Database.Command [20101]执行DbCommand（1ms）[Parameters = []，CommandType ='Text'，CommandTimeout ='30'] SET IDENTITY_INSERT dbo.ClientGroups ON info：Microsoft.EntityFrameworkCore.Database . 命令[20101]执行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]执行DbCommand（0ms）[Parameters = []，CommandType ='Text'，CommandTimeout = '30'] SET IDENTITY_INSERT dbo.Users ON

2 回答

EF Core打开/关闭每个数据库相关操作的连接，因此使用 ExecuteSqlCommand 设置标识无效 .

您需要在整个过程中明确保持连接打开：

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

或者将整个过程包装在显式事务中：

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

回复于 2024-04-26T17:42:23+08:00

这是@ damien-the-unbeliever的评论和@ivan stoev的建议答案的混合

一次只能为一个表启用IDENTITY_INSERT
代码必须包装在事务中，因为EF Core将使用单独的连接 .

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();
}

}

回复于 2024-04-26T17:42:23+08:00

虽然IDENTIY_INSERT设置为ON，但EF Core“无法为标识列插入显式值”

2 回答

相关问题