EF Core中执行Upsert操作时是否应捕获DbUpdateConcurrencyException？

核心逻辑说明 #

你需要实现的是**Upsert（插入或更新）**操作：用户首次调用命令时插入新记录，后续调用则更新已有记录。Nick Chapsas视频里的GPT-4生成代码覆盖了基础逻辑，但存在可优化的细节，下面逐一拆解。

现有代码的合理性与优化点 #

1. 基础Upsert逻辑的正确性 #

原代码通过FindAsync查询复合主键（UserId+EventTypeId），根据是否存在分支处理插入/更新，这个逻辑是成立的——EF Core会自动跟踪实体状态，修改属性后SaveChangesAsync会生成对应的UPDATE语句，新增则生成INSERT语句。

2. 并发异常处理的问题 #

代码直接吞掉DbUpdateConcurrencyException是不合理的：

• 该异常表示你查询到记录后，其他线程/请求已修改了该记录，此时直接忽略会导致对方的修改丢失。
• 正确做法是重新查询最新数据，根据业务需求合并修改，而非直接跳过。

优化后的并发处理逻辑：

public async Task Handle(UpsertUserNotificationSettingsCommand request, CancellationToken cancellationToken)
{
    bool saveFailed;
    do
    {
        saveFailed = false;
        var userNotificationSettings = await _context.UserNotificationSettings.FindAsync(
            new object[] { request.UserId, request.EventTypeId }, 
            cancellationToken);
        
        if (userNotificationSettings == null)
        {
            var entity = new UserNotificationSettings
            {
                UserId = request.UserId,
                EventTypeId = request.EventTypeId,
                NotificationsEnabled = request.NotificationsEnabled,
                SoundId = request.SoundId
            };
            _context.UserNotificationSettings.Add(entity);
        }
        else
        {
            userNotificationSettings.NotificationsEnabled = request.NotificationsEnabled;
            userNotificationSettings.SoundId = request.SoundId;
        }

        try
        {
            await _context.SaveChangesAsync(cancellationToken);
        }
        catch (DbUpdateConcurrencyException ex)
        {
            saveFailed = true;
            // 重新加载实体最新状态并处理冲突
            foreach (var entry in ex.Entries)
            {
                if (entry.Entity is UserNotificationSettings settings)
                {
                    var databaseValues = await entry.GetDatabaseValuesAsync(cancellationToken);
                    if (databaseValues != null)
                    {
                        entry.CurrentValues.SetValues(databaseValues);
                        // 业务示例：保留当前请求的设置，覆盖数据库最新值
                        entry.CurrentValues["NotificationsEnabled"] = request.NotificationsEnabled;
                        entry.CurrentValues["SoundId"] = request.SoundId;
                    }
                    else
                    {
                        // 实体已被其他请求删除，重新插入新记录
                        var newEntity = new UserNotificationSettings
                        {
                            UserId = request.UserId,
                            EventTypeId = request.EventTypeId,
                            NotificationsEnabled = request.NotificationsEnabled,
                            SoundId = request.SoundId
                        };
                        _context.UserNotificationSettings.Add(newEntity);
                    }
                }
                else
                {
                    throw new NotSupportedException(
                        $"无法处理类型 {entry.Entity.GetType().Name} 的并发冲突");
                }
            }
        }
    } while (saveFailed);
}

3. 可选：高版本EF Core原生Upsert（EF Core 7+） #

如果项目使用EF Core 7及以上版本，可直接利用数据库原生Upsert语句（如SQL Server的MERGE），减少查询往返次数，提升性能与并发安全性：

// EF Core 7+ 实现方式
public async Task Handle(UpsertUserNotificationSettingsCommand request, CancellationToken cancellationToken)
{
    // 尝试更新已有记录
    var affectedRows = await _context.UserNotificationSettings
        .Where(s => s.UserId == request.UserId && s.EventTypeId == request.EventTypeId)
        .ExecuteUpdateAsync(s => s
            .SetProperty(e => e.NotificationsEnabled, request.NotificationsEnabled)
            .SetProperty(e => e.SoundId, request.SoundId),
            cancellationToken);

    // 若没有匹配到记录，执行插入
    if (affectedRows == 0)
    {
        var entity = new UserNotificationSettings
        {
            UserId = request.UserId,
            EventTypeId = request.EventTypeId,
            NotificationsEnabled = request.NotificationsEnabled,
            SoundId = request.SoundId
        };
        await _context.UserNotificationSettings.AddAsync(entity, cancellationToken);
        await _context.SaveChangesAsync(cancellationToken);
    }
}

EF Core 8+ 可使用更简洁的Merge语法：

public async Task Handle(UpsertUserNotificationSettingsCommand request, CancellationToken cancellationToken)
{
    await _context.UserNotificationSettings
        .Merge()
        .On(s => new { s.UserId, s.EventTypeId })
        .WhenMatched(s => s
            .SetProperty(e => e.NotificationsEnabled, request.NotificationsEnabled)
            .SetProperty(e => e.SoundId, request.SoundId))
        .WhenNotMatched(s => new UserNotificationSettings
        {
            UserId = request.UserId,
            EventTypeId = request.EventTypeId,
            NotificationsEnabled = request.NotificationsEnabled,
            SoundId = request.SoundId
        })
        .ExecuteAsync(cancellationToken);
}

总结 #

• 基础场景下，原代码的查询分支逻辑可正常运行，但并发异常不能直接忽略，需重新加载数据处理冲突。
• 使用EF Core 7+时，优先采用数据库原生Upsert方式，减少数据库交互次数，提升性能与可靠性。

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