C# Polly WaitandRetry or delay and then retry

Question

I have created a policy which will wait and retry for: My step is depending on output of a SELECT query. Sometimes database takes a long time around 35-45 seconds to generate the value in a table. So, I have to wait till that time to check if value populated in database table or not using Polly retry.

Below is my logic for it:

var parameters = new { PackageID = packageId };
var query = $"Select ID From Staging..Log Where StagePkg=@PackageID";

var _retryPolicy = Policy
    .Handle<Exception>()
    .WaitAndRetryAsync(5, retryAttempt => {
            var timeToWait = TimeSpan.FromSeconds(Math.Pow(10, retryAttempt));
            Console.WriteLine($"Waiting {timeToWait.TotalSeconds} seconds");
            return timeToWait;
        }
    );

return await _retryPolicy.ExecuteAsync<BatchMailLog>(async () => await SybaseConnection.WithConnectionAsync(c => c.QueryFirstOrDefaultAsync<StageLog>(query, parameters)));

It's not getting executed the way I was thinking like: execute the query, check if query returning a value. If returned value is 0 or null then retry the query after 15 seconds and repeat till we get the value from select query.

I am getting error below:

System.NullReferenceException : Object reference not set to an instance of an object.

This error is happening because it didn't get the value from a query so it has to wait for few seconds and retry.

Answer 1

if returned value is 0 or null then retry the query after 15 seconds and repeat till we get the value from select query

This means you need to setup your policy in the following way:

• Trigger: if returned value is 0 or null
• Sleep duration: retry the query after 15 seconds
• Times: till we get the value from select query

With Polly you can describe this desired behaviour in the following way:

var retry = Policy
    .HandleResult<BatchMailLog>(b => (b?.XYZ ?? 0) == 0) //Trigger
    .WaitAndRetryForeverAsync( //Times
      _ => TimeSpan.FromSeconds(15)); //Sleep duration