如何在Dapper+Polly架构下实现实体不存在则插入并保持简洁性？

我太懂这种感受了——Dapper和Dapper.Contrib的简洁性真的是用了就回不去，结果加Polly重试的时候，因为要先检查记录是否存在，一下子就把干净的代码给搞复杂了，完全破坏了原来的体验对吧？下面分享几个能兼顾重试逻辑和代码简洁性的方案：

方案1：封装Dapper.Contrib扩展方法 #

把「存在性检查+重试+插入」的逻辑封装成通用扩展方法，业务代码里直接调用这个扩展，和原来用Dapper.Contrib的方式几乎一致。

public static class DapperContribPollyExtensions
{
    /// <summary>
    /// 带重试和存在性检查的插入方法
    /// </summary>
    /// <typeparam name="T">实体类型（需包含Id属性）</typeparam>
    /// <param name="connection">数据库连接</param>
    /// <param name="entity">待插入实体</param>
    /// <param name="retryPolicy">Polly重试策略</param>
    public static async Task InsertWithRetry<T>(this IDbConnection connection, T entity, Policy retryPolicy) 
        where T : class
    {
        await retryPolicy.ExecuteAsync(async () =>
        {
            // 反射获取实体Id值（也可以用表达式树优化性能）
            var idProperty = typeof(T).GetProperty("Id") 
                ?? throw new InvalidOperationException("实体必须包含Id属性用于存在性检查");
            var idValue = idProperty.GetValue(entity);

            // 检查记录是否存在
            var existingEntity = await connection.GetByIdAsync<T>(idValue);
            if (existingEntity == null)
            {
                // 不存在则执行插入
                await connection.InsertAsync(entity);
            }
            // 若存在可根据业务需求选择：抛出异常/跳过/更新字段
        });
    }
}

业务层调用示例：

public async Task InsertPayment(Payment payment)
{
    // 定义重试策略
    var retryPolicy = Policy
        .Handle<SqlException>()
        .WaitAndRetryAsync(3, attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)));

    // 调用扩展方法，保持代码简洁
    await _connection.InsertWithRetry(payment, retryPolicy);
}

方案2：利用数据库幂等性语句（推荐） #

把存在性检查的逻辑推到数据库层面，使用数据库原生的幂等插入语法，这样应用层不需要额外查库，直接用Polly重试插入语句即可，代码简洁性拉满。

MySQL示例（ON DUPLICATE KEY UPDATE） #

public async Task InsertPayment(Payment payment)
{
    var retryPolicy = Policy
        .Handle<SqlException>(ex => ex.Number == 1062) // 捕获唯一键冲突错误
        .WaitAndRetryAsync(3, attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)));

    await retryPolicy.ExecuteAsync(async () =>
    {
        var sql = @"INSERT INTO Payments (Id, Amount, CreateTime)
                    VALUES (@Id, @Amount, @CreateTime)
                    ON DUPLICATE KEY UPDATE 
                        Amount = @Amount, 
                        CreateTime = @CreateTime"; // 存在则更新字段，或写DO NOTHING

        await _connection.ExecuteAsync(sql, payment);
    });
}

SQL Server示例（MERGE语句） #

public async Task InsertPayment(Payment payment)
{
    var retryPolicy = Policy
        .Handle<SqlException>(ex => ex.Number == 2627) // 捕获唯一键冲突错误
        .WaitAndRetryAsync(3, attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)));

    await retryPolicy.ExecuteAsync(async () =>
    {
        var sql = @"MERGE INTO Payments AS Target
                    USING (VALUES (@Id, @Amount, @CreateTime)) AS Source (Id, Amount, CreateTime)
                    ON Target.Id = Source.Id
                    WHEN NOT MATCHED THEN
                        INSERT (Id, Amount, CreateTime) VALUES (Source.Id, Source.Amount, Source.CreateTime)
                    WHEN MATCHED THEN
                        UPDATE SET Amount = Source.Amount, CreateTime = Source.CreateTime;";

        await _connection.ExecuteAsync(sql, payment);
    });
}

方案3：用装饰器模式封装Repository #

如果你的项目用了依赖注入，可以把Dapper.Contrib的操作和重试逻辑封装到Repository实现里，业务层只依赖抽象接口，调用方式和原来一样简洁。

// 抽象接口
public interface IPaymentRepository
{
    Task InsertWithRetry(Payment payment);
}

// 实现类（整合Dapper.Contrib+Polly）
public class PaymentRepository : IPaymentRepository
{
    private readonly IDbConnection _connection;
    private readonly Policy _retryPolicy;

    public PaymentRepository(IDbConnection connection)
    {
        _connection = connection;
        _retryPolicy = Policy
            .Handle<SqlException>()
            .WaitAndRetryAsync(3, attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)));
    }

    public async Task InsertWithRetry(Payment payment)
    {
        await _retryPolicy.ExecuteAsync(async () =>
        {
            var existing = await _connection.GetByIdAsync<Payment>(payment.Id);
            if (existing == null)
            {
                await _connection.InsertAsync(payment);
            }
        });
    }
}

业务层调用示例：

private readonly IPaymentRepository _paymentRepo;

public PaymentService(IPaymentRepository paymentRepo)
{
    _paymentRepo = paymentRepo;
}

public async Task ProcessPayment(Payment payment)
{
    // 调用方式极其简洁，重试逻辑完全被封装
    await _paymentRepo.InsertWithRetry(payment);
}

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