使用EF Core迁移数据库时IDENTITY_INSERT相关报错求助

问题描述 #

使用EF Core对十余年历史的数据库进行ETL迁移，执行GetAllUsers方法时出现两类报错：

1. Table 'AspNetUsers' does not have the identity property. Cannot perform SET operation
2. 若不设置IDENTITY_INSERT OFF，触发SqlException: Cannot insert explicit value for identity column in table 'UserConnections' when IDENTITY_INSERT is set to OFF

解决方案 #

一、解决“AspNetUsers无IDENTITY属性”报错

• 原因：代码中尝试对AspNetUsers表执行SET IDENTITY_INSERT ON/OFF，但该表的Id列是字符串类型（继承自IdentityUser），并非SQL Server的IDENTITY自增列，因此无法执行该操作。
• 修复步骤：
  1. 直接移除代码中针对AspNetUsers的IDENTITY_INSERT开关语句：

     // 删除以下两行代码
     _newContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [AspNetUsers] ON");
     _newContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [AspNetUsers] OFF");
     

  2. 确认新数据库中ConnexUsers表的Id列未设置为IDENTITY类型——因为你需要保留原用户Id（或冲突时生成新Guid），不需要数据库自动生成主键值。

二、解决“UserConnectionsIDENTITY列赋值失败”报错

• 原因：CreateUserConnections方法插入数据时，显式给UserConnections的IDENTITY自增列赋值，但此时IDENTITY_INSERT处于OFF状态，SQL Server禁止此类操作。
• 修复步骤：
  1. 定位UserConnections实体类的IDENTITY主键（通常是名为Id的自增字段）。
  2. 若需要保留原数据的主键值，在插入UserConnections时临时开启该表的IDENTITY_INSERT：
     修改CreateUserConnections方法逻辑：

     // 在插入UserConnections前执行
     _newContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [UserConnections] ON");
     // 执行Add和SaveChanges操作
     _newContext.UserConnections.Add(newConnection);
     _newContext.SaveChanges();
     // 插入完成后关闭
     _newContext.Database.ExecuteSqlRaw("SET IDENTITY_INSERT [UserConnections] OFF");
     

  3. 若不需要保留原主键值，移除对UserConnectionsIDENTITY列的显式赋值，让数据库自动生成自增ID。

三、额外优化建议

• 批量操作提升性能：当前循环中每次调用SaveChanges会频繁与数据库交互，建议收集所有用户及关联数据后，一次性执行SaveChanges。
• 冲突检查优化：_newContext.ConnexUsers.Any(x => x.Id == newUser.Id)会重复查询数据库，可先缓存已存在的用户ID集合：

  // 先查询所有已存在的用户ID并缓存
  var existingUserIds = _newContext.ConnexUsers.Select(u => u.Id).ToHashSet();
  
  foreach (var user in oldUsers) {
      var newUser = ConvertAspNetUserToConnexUser(user);
      if (existingUserIds.Contains(newUser.Id))
          newUser.Id = Guid.NewGuid().ToString();
      
      // 执行关联数据创建操作
      CreateUserConnections(ref newUser);
      CreateLicenseKey(newUser);
      CreateTaxCodes(newUser);
      
      _newContext.ConnexUsers.Add(newUser);
      existingUserIds.Add(newUser.Id); // 更新缓存
  }
  
  // 最后统一保存所有数据
  _newContext.SaveChanges();
  

内容的提问来源于stack exchange，提问作者Melvin Bradley