title: Inserting an entity if it doesn't already exist
body: <p>We've been using <code>Dapper</code> and <code>Dapper.Contrib</code> for the ease in which we can perform regular database operations, which has been great. However, since introducing <code>Polly</code> 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. </p>
  
  <p>Here's a simplified example of how we're currently performing inserts:</p>
  
  <pre><code>public async Task Insert(Payment payment)
  {
      var retryPolicy = // Create using Polly.
      using (var connection = new SqlConnection(_connectionString))
      {
          var dao = MapToDao(payment);
          await retryPolicy.ExecuteAsync(() =&gt; connection.InsertAsync(dao));
      }
  }
  
  [Table("Payment")]
  public class PaymentDao
  {
      [ExplicitKey]
      public Guid PaymentId { get; set; }
      // A whole bunch of properties omitted for brevity
  }
  </code></pre>
  
  <p>where <code>Payment</code> is our domain model, and <code>PaymentDao</code> is our data access object.</p>
  
  <p>We do actually have logic in the service that calls <code>Insert</code> that explicitly checks for duplicates, but this is negated by the retry policy. This means since the introduction of <code>Polly</code>, we are seeing a small number of duplicate payments being inserted.</p>
  
  <p>I can fix this by doing the following:</p>
  
  <pre><code>public async Task Insert(Payment payment)
  {
      var retryPolicy = // Create using Polly.
      using (var connection = new SqlConnection(_connectionString))
      {
          var dao = MapToDao(payment);
  
          await retryPolicy.ExecuteAsync(() =&gt; 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.
              }));
      }
  }
  </code></pre>
  
  <p>However, as you can see, it becomes pretty long-winded. Is there a simpler way of doing this?</p>