Inserting an entity if it doesn't already exist

Question

We've been using Dapper and Dapper.Contrib for the ease in which we can perform regular database operations, which has been great. However, since introducing Polly to add retry policies for some of our operations, I've not been able to find a way to keep the same simplicity because of the necessity to check for a record's existence before executing a retry.

Here's a simplified example of how we're currently performing inserts:

public async Task Insert(Payment payment)
{
    var retryPolicy = // Create using Polly.
    using (var connection = new SqlConnection(_connectionString))
    {
        var dao = MapToDao(payment);
        await retryPolicy.ExecuteAsync(() => connection.InsertAsync(dao));
    }
}

[Table("Payment")]
public class PaymentDao
{
    [ExplicitKey]
    public Guid PaymentId { get; set; }
    // A whole bunch of properties omitted for brevity
}

where Payment is our domain model, and PaymentDao is our data access object.

We do actually have logic in the service that calls Insert that explicitly checks for duplicates, but this is negated by the retry policy. This means since the introduction of Polly, we are seeing a small number of duplicate payments being inserted.

I can fix this by doing the following:

public async Task Insert(Payment payment)
{
    var retryPolicy = // Create using Polly.
    using (var connection = new SqlConnection(_connectionString))
    {
        var dao = MapToDao(payment);

        await retryPolicy.ExecuteAsync(() => connection.ExecuteAsync(
            @"IF ((SELECT COUNT(*) FROM dbo.Payment WHERE SubscriptionId = @subscriptionId) = 0)
            BEGIN
                INSERT INTO Payment
                (
                    PaymentId,
                    SubscriptionId,
                    // Lots of columns omitted for brevity.
                )
                VALUES
                (
                    @PaymentId,
                    @SubscriptionId,
                    // Lots of values omitted for brevity.
                )
            END",
            new
            {
                dao.PaymentId,
                dao.SubscriptionId,
                // Lots of properties omitted for brevity.
            }));
    }
}

However, as you can see, it becomes pretty long-winded. Is there a simpler way of doing this?

Answer 1

You can consider the alternative of checking first using the model and then performing the insert given that the search uses fewer parameters

using (var connection = new SqlConnection(_connectionString)) {
    var dao = MapToDao(payment);
    var sql = "SELECT COUNT(1) FROM dbo.Payment WHERE SubscriptionId = @subscriptionId";
    await retryPolicy.ExecuteAsync(async () => { 
        var exists = await connection.ExecuteScalarAsync<bool>(sql, new {dao.SubscriptionId});
        if(!exists) {
            await connection.InsertAsync(dao);
        }
    });
}