EF Core调用Add方法时如何禁用实体跟踪，交由SQL Server处理主键重复校验

核心原理说明：EF Core添加实体时的本地主键重复校验，仅在上下文跟踪器中已存在同类型、同主键的实体时才会触发，只要避免该前提即可实现完全依赖数据库主键约束做幂等校验的需求。

方案1：全局/局部禁用实体跟踪（最推荐，改动最小） #

如果不需要对IdempotentOperations实体做后续修改，所有针对该表的查询都加上AsNoTracking()扩展方法，查询返回的实体不会被上下文跟踪，本地缓存Local集合就不会留存相关数据：

// 所有查询IdempotentOperations的地方都加AsNoTracking
var ops = await opsRepo.IdempotentOperations.AsNoTracking().Where(...).ToListAsync();

如果整个上下文都不需要跟踪功能，可以在DbContext配置中全局设置默认无跟踪：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    // 全局默认查询不跟踪
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

配置完成后你可以直接删掉原有代码中Local.Any的前置检查，仅保留catch块里的冲突处理逻辑即可，EF Core不会再做本地重复校验，会直接把插入请求发给SQL Server。

方案2：添加前分离本地已跟踪的实体 #

如果部分场景确实需要跟踪该实体，可以在添加新实体前，把本地缓存中同主键的实体从跟踪器中分离：

Action throwIdempotentOpError = () => {
    throw new ExecutionError("The operation you are trying to perform was already performed, please try again with a new client mutation id");
};

// 移除本地已跟踪的同主键实体
var localEntity = opsRepo.IdempotentOperations.Local.FirstOrDefault(op => op.ClientMutationId == mutationGuid);
if (localEntity != null)
{
    opsRepo.Entry(localEntity).State = EntityState.Detached;
}

var operation = new IdempotentOperation {
    ClientMutationId = mutationGuid,
    CreatedAt = DateTime.UtcNow,
    UpdatedAt = DateTime.UtcNow,
    RawDocument = context.Document.OriginalQuery,
    Status = IdempotentOperationStatus.Started
};

try {
    opsRepo.IdempotentOperations.Add(operation);
    await opsRepo.SaveChangesAsync();
} catch (DbUpdateException ex) {
    if (ex.InnerException != null && ex.InnerException.Message.StartsWith("Violation of PRIMARY KEY constraint 'PK_IdempotentOperations'")) {
        throwIdempotentOpError();
    }
    throw;
}

方案3：直接执行原生SQL插入（性能最优） #

完全绕过EF Core的实体跟踪系统，直接通过原生SQL执行插入，没有任何本地校验开销，适合高并发的幂等校验场景：

Action throwIdempotentOpError = () => {
    throw new ExecutionError("The operation you are trying to perform was already performed, please try again with a new client mutation id");
};

try {
    await opsRepo.Database.ExecuteSqlRawAsync(@"
        INSERT INTO IdempotentOperations (ClientMutationId, CreatedAt, UpdatedAt, RawDocument, Status)
        VALUES ({0}, {1}, {2}, {3}, {4})
    ", 
    mutationGuid, 
    DateTime.UtcNow, 
    DateTime.UtcNow, 
    context.Document.OriginalQuery, 
    IdempotentOperationStatus.Started);
} catch (DbUpdateException ex) {
    if (ex.InnerException != null && ex.InnerException.Message.StartsWith("Violation of PRIMARY KEY constraint 'PK_IdempotentOperations'")) {
        throwIdempotentOpError();
    }
    throw;
}

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