EF Core 6添加一对多关联用户时触发IDENTITY_INSERT禁用错误

问题描述 #

使用Entity Framework Core 6和Fluent API实现一对多关系（User关联Role），插入新User时触发错误：Cannot insert explicit value for identity column in table 'Roles' when IDENTITY_INSERT is set to OFF。希望保留RoleId的自增特性，且无关联的模型操作正常。

错误堆栈 #

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'Roles' when IDENTITY_INSERT is set to OFF.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:501a6880-e298-43d5-be69-fcbebacdb15e
Error Number:544,State:1,Class:16
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at WorkIT_Backend.Services.UserService.Create(String username, String password, String role) in C:\Users\Ondřej\Desktop\škola\2022 PRF\WS\OPR3\WorkIT_Backend\WorkIT_Backend\WorkIT_Backend\Services\UserService.cs:line 54
   at WorkIT_Backend.Controllers.UsersController.CreateUser(UserDto user) in C:\Users\Ondřej\Desktop\škola\2022 PRF\WS\OPR3\WorkIT_Backend\WorkIT_Backend\WorkIT_Backend\Controllers\UsersController.cs:line 57
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

相关代码 #

Role模型 #

public sealed class Role
{
    public long RoleId { get; set; }

    public string? Name { get; set; }

    public ICollection<User> Users { get; set; }

    public Role()
    {
        Users = new HashSet<User>();
    }
}

User模型 #

public class User
{
    public long UserId { get; set; }

    public string? UserName { get; set; }

    public string? PasswordHash { get; set; }
    public long RoleId { get; set; }

    public virtual Role Role { get; set; }

    public User()
    {
    }
}

DbContext配置 #

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Role>(entity =>
    {
        entity.HasKey(q => q.RoleId);
        entity.Property(q => q.RoleId)
            .ValueGeneratedOnAdd();

        entity.Property(q => q.Name)
            .IsRequired();
        entity.HasIndex(q => q.Name)
            .IsUnique();
    });

    modelBuilder.Entity<User>(entity =>
    {
        entity.HasKey(q => q.UserId);
        entity.Property(q => q.UserId)
            .ValueGeneratedOnAdd();

        entity.Property(q => q.UserName)
            .IsRequired();

        entity.Property(q => q.PasswordHash)
            .IsRequired();

        entity.HasOne(u => u.Role)
            .WithMany(r => r.Users)
            .HasForeignKey(u => u.RoleId)
            .OnDelete(DeleteBehavior.ClientSetNull);
    });
}

用户创建服务 #

public async Task<User> Create(string username, string password, string role)
{
    EnsureNotNull(username, nameof(username));
    EnsureNotNull(password, nameof(password));
    EnsureNotNull(role, nameof(role));

    username = username.ToLower();

    if (_context.Users.Any(q => q.UserName == username))
        throw CreateException($"User {username} already exists.", null);

    var hash = _securityService.HashPassword(password);
    var userRole = await _roleService.GetRole(role);

    var ret = new User {UserName = username, PasswordHash = hash, Role = userRole};

    _context.Users.Add(ret);
    await _context.SaveChangesAsync();

    return ret;
}

角色服务 #

public class RoleService
{
    private readonly WorkItDbContext _context;
    private readonly SecurityService _securityService;


    public RoleService(WorkItDbContext context, SecurityService securityService)
    {
        _context = context;
        _securityService = securityService;
    }

    public async Task<Role> Create(string name)
    {
        EnsureNotNull(name, nameof(name));

        name = name.ToLower();
        if (_context.Roles.Any(q => q.Name == name))
            throw CreateException($"Role {name} already exists.", null);

        var ret = new Role {Name = name};
        _context.Add(ret);
        await _context.SaveChangesAsync();
        return ret;
    }

    public async Task<List<Role>> GetRoles()
    {
        var roles = await _context.Roles.ToListAsync();
        return roles;
    }

    public async Task<Role> GetRole(string name)
    {
        var role = await _context.Roles.FirstAsync(q => q.Name == name) ??
                   throw CreateException($"Role {name} does not exist.");
        return role;
    }
}

Program.cs配置 #

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddSingleton<IConfiguration>(builder.Configuration);
SecurityService securityService = new(builder.Configuration);
builder.Services.AddTransient<WorkItDbContext>();
builder.Services.AddSingleton<SecurityService>();
builder.Services.AddTransient<UserService>();
builder.Services.AddTransient<RoleService>();

问题原因 #

核心问题是DbContext生命周期配置错误：你将WorkItDbContext注册为Transient（每次请求服务时创建新实例），导致UserService和RoleService使用完全独立的DbContext实例。

当RoleService.GetRole()从自己的DbContext获取Role实体后，该实体在UserService的DbContext中处于**未跟踪（Detached）**状态。将此Role赋值给新User的Role属性并调用_context.Users.Add(ret)时，EF Core会将整个对象图（User + Role）标记为Added，尝试插入Role记录——但Role的RoleId是自增列，手动插入值触发了SQL Server的IDENTITY_INSERT限制。

解决方案 #

方案1：将DbContext改为Scoped生命周期（推荐） #

ASP.NET Core中DbContext的默认推荐生命周期是Scoped，同一个请求内的所有服务共用同一个DbContext实例，实体跟踪可正常工作：

修改Program.cs中的DbContext注册代码：

// 替换原有的AddTransient<WorkItDbContext>()
builder.Services.AddDbContext<WorkItDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("YourConnectionString"));
});

注：需确保appsettings.json中配置了对应的数据库连接字符串（"YourConnectionString"需替换为实际名称）。

方案2：保持Transient但手动附加Role实体 #

若必须保持DbContext为Transient，可在UserService中获取Role后，将其附加到当前DbContext并标记为未修改状态，告知EF Core该Role已存在于数据库中：

修改UserService的Create方法：

public async Task<User> Create(string username, string password, string role)
{
    // ... 原有代码 ...

    var hash = _securityService.HashPassword(password);
    var userRole = await _roleService.GetRole(role);

    // 附加Role到当前DbContext，标记为未修改
    _context.Attach(userRole).State = EntityState.Unchanged;

    var ret = new User {UserName = username, PasswordHash = hash, Role = userRole};

    _context.Users.Add(ret);
    await _context.SaveChangesAsync();

    return ret;
}

方案3：只设置RoleId外键属性 #

既然Role已存在，可直接设置User的RoleId外键，不使用导航属性，避免EF Core处理Role实体：

修改UserService的Create方法：

public async Task<User> Create(string username, string password, string role)
{
    // ... 原有代码 ...

    var hash = _securityService.HashPassword(password);
    var userRole = await _roleService.GetRole(role);

    // 仅设置外键RoleId，不赋值Role导航属性
    var ret = new User {UserName = username, PasswordHash = hash, RoleId = userRole.RoleId};

    _context.Users.Add(ret);
    await _context.SaveChangesAsync();

    return ret;
}

额外检查 #

确认SQL Server中Roles表的RoleId列已设置为自增（IDENTITY(1,1)），可通过SQL Server Management Studio查看表结构验证。

内容的提问来源于stack exchange，提问作者Ondřej Halata