EF Core 7多对多关联：关联已有数据创建Music时报错求助

尝试在Entity Framework Core 7中创建关联已有Category和Mood的Music实体时，遇到以下错误：

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_AgeRates"
DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.

实体结构 #

现有数据 #

业务代码 #

public record Command(CreateMusicRequest Model) : ICommand<Response<CreatedMusicResponse>>;

public class CommandHandler : ICommandHandler<Command, Response<CreatedMusicResponse>>
{
    private readonly IGenericRepository<Music> _musicRepositoryAsync;
    private readonly IGenericRepository<Category> _categoryRepositoryAsync;
    private readonly IGenericRepository<Mood> _moodRepositoryAsync;
    public readonly IGenericRepository<AgeRate> _ageRateRepositoryAsync;
    private readonly IUnitOfWork _unitOfWork;

    public CommandHandler(IGenericRepository<Music> musicRepositoryAsync,
          IGenericRepository<Category> categoryRepositoryAsync, 
          IGenericRepository<Mood> moodRepositoryAsync,
          IGenericRepository<AgeRate> ageRateRepositoryAsync,
          IUnitOfWork unitOfWork)
    {
        _musicRepositoryAsync = musicRepositoryAsync;
        _categoryRepositoryAsync = categoryRepositoryAsync;
        _moodRepositoryAsync = moodRepositoryAsync;
        _unitOfWork = unitOfWork;
        _ageRateRepositoryAsync = ageRateRepositoryAsync;
    }

    public async Task<Response<CreatedMusicResponse>> Handle(Command request, CancellationToken cancellationToken)
    {
        Music music = new()
              {
                  Name = request.Model.Name,
                  ImageUrl = request.Model.ImageUrl,
                  IsUsable = request.Model.IsUsable,
                  Lyrics=request.Model.Lyrics
              };

        music.AgeRate = await _ageRateRepositoryAsync.GetAsync(x => x.Id == request.Model.AgeRateId);

        foreach (var moodId in request.Model.MoodIds)
        {
            Mood existMood = await _moodRepositoryAsync.GetAsync(x => x.Id == moodId);

            if (existMood != null)
            {
                music.Moods.Add(existMood);
            }
        }

        foreach (var categoryId in request.Model.CategoryIds)
        {
            Category existCategory = await _categoryRepositoryAsync.GetAsync(x => x.Id == categoryId);

            if (existCategory != null)
            {
                music.Categories.Add(existCategory);
           }
       }

       await _musicRepositoryAsync.CreateAsync(music);
       await _unitOfWork.CommitAsync();

       return Response<CreatedMusicResponse>.Success(music.Adapt<CreatedMusicResponse>(), 201);
    }
}

EF Core 7多对多关联配置 #

public class MyDbContext : IdentityDbContext<IdentityUser, IdentityRole, string>
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {

    }
    public DbSet<Music> Musics { get; set; }
    public DbSet<AgeRate> AgeRates { get; set; }
    public DbSet<Mood> Moods { get; set; }
    public DbSet<Category> Categories { get; set; }


    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Music>().Navigation(m => m.Moods).AutoInclude();
        builder.Entity<Music>().Navigation(c => c.Categories).AutoInclude();
        builder.Entity<Music>().Navigation(c => c.AgeRate).AutoInclude();
        base.OnModelCreating(builder);
    }
}

核心问题 #

错误提示PK_AgeRates唯一约束冲突，本质是EF Core误将已存在的AgeRate实体判定为新实体，尝试重新插入导致冲突。常见触发原因：

1. 通用仓储的GetAsync方法使用了AsNoTracking，实体脱离上下文跟踪；
2. 直接赋值完整关联实体，EF无法识别其已存在于数据库。

具体解决方案 #

1. 处理AgeRate关联（推荐方案） #

无需查询AgeRate实体，直接给Music的外键属性赋值：

// 替换原有的music.AgeRate赋值代码
music.AgeRateId = request.Model.AgeRateId;

这种方式跳过实体查询，EF会自动建立关联，完全避免重复插入问题。

2. 确保关联实体处于跟踪状态 #

如果必须查询实体，检查仓储的GetAsync是否禁用了跟踪，若使用了AsNoTracking，需手动附加实体并标记为未修改：

var ageRate = await _ageRateRepositoryAsync.GetAsync(x => x.Id == request.Model.AgeRateId);
if (ageRate != null)
{
    _ageRateRepositoryAsync.Attach(ageRate); // 仓储需实现Attach方法，或直接调用DbContext.Attach
    music.AgeRate = ageRate;
}

3. 优化多对多关联处理 #

对于Mood和Category的多对多关联，可采用更高效的方式，无需查询实体：

// 替换原有的Mood循环代码
foreach (var moodId in request.Model.MoodIds)
{
    music.Moods.Add(new Mood { Id = moodId });
}

// 替换原有的Category循环代码
foreach (var categoryId in request.Model.CategoryIds)
{
    music.Categories.Add(new Category { Id = categoryId });
}

EF Core会自动识别这些是已存在的实体，仅创建中间关联表记录，不会插入新数据。

4. 检查仓储CreateAsync实现 #

确保CreateAsync仅执行添加实体的逻辑，无额外错误处理导致实体状态异常：

// 正确的CreateAsync实现示例
public async Task CreateAsync(T entity)
{
    await _dbSet.AddAsync(entity);
}

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