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?

@Nkosi With some of the legacy objects we have which we've not broken up yet, there are 50+ properties, which leads to a lot of manual mapping of values in the inline SQL. I was just was hoping for something that would allow us to maybe supply a predicate and the dao, and it would take care of the rest. Something like: `connection.InsertIfNotExistsAsync(dao, predicate)`. — John H, Apr 08 '18 at 18:01

score 3 · Accepted Answer · edited Apr 08 '18 at 19:14

3

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);
        }
    });
}

edited Apr 08 '18 at 19:14

John H

14,422
4
41
74

answered Apr 08 '18 at 18:05

Nkosi

235,767
35
427
472

That worked beautifully, thank you very much. Have a nice night. – John H Apr 08 '18 at 19:08

Inserting an entity if it doesn't already exist

1 Answers1