启用身份验证的Blazor前端执行密码更改时异常

项目背景 #

• 基于.NET 8开发Blazor前端 + .NET WebAPI后端，共享类库存放DbContext、数据模型等，用于多端复用
• 使用.NET Core Identity实现身份验证与授权，数据库采用MySQL
• Blazor项目启用「个人帐户」身份验证，框架自动生成相关页面及数据库交互代码

问题现象 #

Blazor端测试自动生成的密码更改页面时，数据库更新环节抛出SQL语法错误；但WebAPI通过Swagger/Postman执行相同操作可正常运行，说明后端业务逻辑无问题。

浏览器报错信息 #

MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNING `VersionStamp`;

SELECT `VersionStamp`
FROM `IdentityUsers`
WHERE RO' at line 3

错误栈跟踪 #

Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken)
Microsoft.AspNetCore.Identity.EntityFrameworkCore.UserStore<TUser, TRole, TContext, TKey, TUserClaim, TUserRole, TUserLogin, TUserToken, TRoleClaim>.UpdateAsync(TUser user, CancellationToken cancellationToken)
Microsoft.AspNetCore.Identity.UserManager<TUser>.UpdateUserAsync(TUser user)
Microsoft.AspNetCore.Identity.UserManager<TUser>.ChangePasswordAsync(TUser user, string currentPassword, string newPassword)
ExpenseTracker.BlazorApp.Components.Account.Pages.Manage.ChangePassword.OnValidSubmitAsync() in ChangePassword.razor
+
        var changePasswordResult = await UserManager.ChangePasswordAsync(user, Input.OldPassword, Input.NewPassword);
Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
Microsoft.AspNetCore.Components.Forms.EditForm.HandleSubmitAsync()
Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)

相关代码片段 #

Blazor项目Program.cs的DbContext配置 #

builder.Services.AddDbContext<ExpenseTrackerDbContext>(options => options.UseMySQL(connectionString));

DbContext类（简化版） #

public class ExpenseTrackerDbContext : IdentityDbContext<ETUser, ETRole, Guid, ETUserClaim, ETUserRole, ETUserLogin, ETRoleClaim, ETUserToken>
{
    public ExpenseTrackerDbContext()
    {
    }

    public ExpenseTrackerDbContext(DbContextOptions<ExpenseTrackerDbContext> options) : base(options)
    {
    }
    public virtual DbSet<ETUserClaim> ETUserClaims { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        new EntityConfig.ETUserConfig().Configure(builder.Entity<ETUser>());
        builder.Entity<ETUser>(entity => { entity.ToTable("IdentityUsers"); });
    }
}

ETUser实体（继承IdentityUser） #

public partial class ETUser : IdentityUser<Guid>
{
    public string Name { get; set; } = null!;
    public string? Description { get; set; }
    public string CountryCode { get; set; } = null!;
    public DateTime? UserCreatedOn { get; set; }
    public DateTime? VersionStamp { get; set; }
}

ETUser实体配置类 #

public class ETUserConfig : IEntityTypeConfiguration<ETUser>
{
    public void Configure(EntityTypeBuilder<ETUser> builder)
    {
       builder.HasKey(e => e.Id).HasName("PRIMARY");
       builder.ToTable("ETUsers");

       builder.Property(e => e.Id)
            .HasColumnType("binary(16)")
            .HasDefaultValueSql("(uuid_to_bin(uuid()))")
            .HasConversion<byte[]>();

       builder.Property(e => e.Description).HasMaxLength(250);

       builder.Property(p => p.CountryCode)
            .HasMaxLength(10)
            .HasColumnType("VARCHAR(10)");

       builder.Property(p => p.UserCreatedOn)
            .IsRequired()
            .HasDefaultValueSql("CURRENT_TIMESTAMP")
            .ValueGeneratedOnAdd()
            .HasColumnType("DATETIME");

       builder.Property(e => e.VersionStamp).IsRequired()
            .ValueGeneratedOnAddOrUpdate()
            .HasDefaultValueSql("CURRENT_TIMESTAMP(3)")
            .HasColumnType("TIMESTAMP(3)")
            .IsConcurrencyToken();

       builder.Ignore(e => e.ConcurrencyStamp);
    }
}

Blazor自动生成的密码更改方法（未修改） #

private async Task OnValidSubmitAsync()
{
    var changePasswordResult = await UserManager.ChangePasswordAsync(user, Input.OldPassword, Input.NewPassword);
    if (!changePasswordResult.Succeeded)
    {
        message = $"Error: {string.Join(",", changePasswordResult.Errors.Select(error => error.Description))}";
        return;
    }

    await SignInManager.RefreshSignInAsync(user);
    Logger.LogInformation("User changed their password successfully.");

    RedirectManager.RedirectToCurrentPageWithStatus("Your password has been changed", HttpContext);
}

疑问 #

确认问题出在EF Identity生成SQL的逻辑上，但无法访问数据库更新的最内层调用，无法确定具体生成规则。

问题根源 #

1. MySQL版本兼容性：错误提示SQL中使用了RETURNING关键字，但MySQL 8.0.19及以下版本不支持该子句。EF Core在处理标记为IsConcurrencyToken且配置ValueGeneratedOnAddOrUpdate的字段时，会尝试用RETURNING获取更新后的令牌值，这在低版本MySQL中会触发语法错误。
2. 表名配置冲突：DbContext的OnModelCreating中，ETUserConfig将表名设为ETUsers，后续又通过builder.Entity<ETUser>().ToTable("IdentityUsers")覆盖，导致EF Core生成SQL时存在表名不一致的潜在干扰。

具体修复步骤 #

1. 统一用户表名配置 #

删除DbContext中重复的表名设置，仅保留配置类中的定义：

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    // 移除重复的ToTable配置，仅保留配置类的设置
    new EntityConfig.ETUserConfig().Configure(builder.Entity<ETUser>());
}

2. 调整并发令牌生成逻辑 #

针对MySQL不支持RETURNING的情况，有两种修复方式：

方式一：使用数据库计算列自动更新令牌（MySQL 8.0.13+支持）

修改ETUserConfig中的VersionStamp配置：

builder.Property(e => e.VersionStamp).IsRequired()
     .HasDefaultValueSql("CURRENT_TIMESTAMP(3)")
     .HasColumnType("TIMESTAMP(3)")
     .IsConcurrencyToken()
     // 配置为计算列，更新时自动刷新时间戳
     .HasComputedColumnSql("CURRENT_TIMESTAMP(3)", stored: true);

方式二：手动更新令牌（兼容所有MySQL版本）

在Blazor的密码更改方法中手动设置令牌值：

private async Task OnValidSubmitAsync()
{
    user.VersionStamp = DateTime.UtcNow; // 手动更新时间戳
    var changePasswordResult = await UserManager.ChangePasswordAsync(user, Input.OldPassword, Input.NewPassword);
    // 后续逻辑保持不变
}

3. 确保Identity识别自定义并发令牌 #

在ETUser的VersionStamp字段上添加[ConcurrencyCheck]特性，明确告知Identity这是并发校验字段：

[ConcurrencyCheck]
public DateTime? VersionStamp { get; set; }

4. 匹配驱动与MySQL版本 #

确保使用的Pomelo.EntityFrameworkCore.MySql版本与.NET 8兼容，若条件允许，将MySQL服务器升级到8.0.19+，原生支持RETURNING子句从根源避免此类问题。

内容的提问来源于stack exchange，提问作者Babu Mannavalappil