Connect Retry Count / Connect Retry Interval not having any effect

[ryno1234]

As I understand, these connection string parameters and their associated properties ConnectRetryCount and ConnectRetryInterval only affect a connection after it has successfully opened and then subsequently dropped.

This implies (and I'd like to confirm) that this means for short lived DbContext operations that perhaps perform 1 quick single operation, retry-ability with a connection wouldn't likely come into play since we'd immediately open the connection and perform the single operation right after (for example, select a record by primary key) and dispose of our DbContext.

I can see how this may be more likely to come into play with a long-lived connection that's doing multiple operations or has long-running operations like slow queries, stored procedures, etc. or even a connection that is opened early and remains open until used which may occur at a later time. These all seem like scenarios where the retry-ability would come into play.

All that said, I cannot get these parameters to operate how I understand them to. Take, for example, this basic test:

    public class ResilientMessagingDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer("Data Source=localhost;Initial Catalog=MyDb;User ID=****;Password=****;Connect Retry Count=4;Connect Retry Interval=10;TrustServerCertificate=True;Connection Timeout=120;Encrypt=false")
            base.OnConfiguring(optionsBuilder);
        }
    }


   public class DatabaseTest
   {
       // 
       // ... boilerplate setup code not included
       //

       public void CanHandleTimeouts()
       {
           using var db = this.ServiceProvider.GetRequiredService<ResilientMessagingDbContext>();

           DateTime operationStartTime = default;
           TimeSpan lastElapsed = TimeSpan.Zero;
           try
           {
               operationStartTime = DateTime.Now;
               db .Message.First();
           }
           catch (Exception ex) {
               _logger.LogError("Failed");
           }
           finally
           {
               lastElapsed = DateTime.Now.Subtract(operationStartTime);
               _logger.LogInformation("Elapsed time: " + lastElapsed.ToString());
           }

           // Stop the SQL server
           Console.WriteLine("Sql Server Stopped...");

           try
           {
               operationStartTime = DateTime.Now;
               db .Message.First();
           }
           catch (Exception ex)
           {
               _logger.LogError("Failed");
           }
           finally
           {
               lastElapsed = DateTime.Now.Subtract(operationStartTime);
               _logger.LogInformation("Elapsed time: " + lastElapsed.ToString());
           }       
       }
   }

In this scenario, if I shut down my localhost SQL server after the first query, the 2nd query doesn't execute or throw an exception until Connection Timeout seconds. I've also confirmed that I can turn back on the SQL Server service during that Connection Timeout time of 120 seconds, and the connection will pick back up and the query will complete.

That's great, but that doesn't really respect the retry parameters. Also, it seems like there is some retry logic that happens anyway even without my retry parameters being set when the connection is being re-established after being dropped like in the scenario I mentioned that is happening in my test case. The DB connection is being picked back up if the connection comes back within the Connection Timeout time, so there must be some sort of retry logic happening there internally (I can see the SqlExceptions being thrown in the debug window)

What am I missing here?

[ryno1234]

Update: This may be the problem. I noticed my error number on the SqlException's is 2 and that does not appear to be considered a transient error.

[JRahnama]

@ryno1234 what version of M.D.SqlClient is in use?

[ryno1234]

5.2.2