Connection resiliency does not work inside a transaction.

[vrapp]

Connection resiliency does not work inside a database transaction.
Here's how to reproduce.

1. run the following sql script:

Create table table1(id int)
insert into table1 select 1

2. Build and run the following program. Adjust server name, database name, and if necessary the credentials.

Microsoft.Data.SqlClient.SqlConnection cn = new();
cn.ConnectionString = "Data Source=(servername);Initial Catalog=(database);Integrated Security=True;Encrypt=False;TrustServerCertificate=True;Connect Retry Count=50;Connect Retry Interval=5";
cn.Open();
Microsoft.Data.SqlClient.SqlCommand cmd = new() {Connection = cn};
try
{
cmd.CommandText = "update table1 set id=id+1";
cmd.ExecuteNonQuery();
Console.WriteLine("Press a key to continue");
Console.ReadKey() ; // disconnect/reconnect to the server while it's waiting for input
cmd.ExecuteNonQuery(); // will it be able to continue? with resiliency in effect it should
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine("press a key to continue");
Console.ReadKey();
}

3. When the program displays the prompt, terminate network connection to sql server, for example by having been connected by VPN and disconnecting it. Then reconnect and press the key in the program to continue. Result: no error, the 2nd UPDATE statement runs successfully.

4. Modify the above code by wrapping up UPDATE statements inside a transaction::

Microsoft.Data.SqlClient.SqlConnection cn = new();
cn.ConnectionString = "Data Source=(servername);Initial Catalog=(database);Integrated Security=True;Encrypt=False;TrustServerCertificate=True;Connect Retry Count=50;Connect Retry Interval=5";
cn.Open();
Microsoft.Data.SqlClient.SqlTransaction tr = cn.BeginTransaction(); // our two UPDATEs will be wrapped in a database transaction
Microsoft.Data.SqlClient.SqlCommand cmd = new() {Connection = cn,Transaction=tr};
try
{
cmd.CommandText = "update table1 set id=id+1";
cmd.ExecuteNonQuery();
Console.WriteLine("Press a key to continue");
Console.ReadKey() ;
cmd.ExecuteNonQuery(); // unfortunately, now it will fail here
tr.Commit();

}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine("press a key to continue");
Console.ReadKey();
}

5. Repeat step 3. Now after you press the key to continue, there's exception "The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection."

As a sidenote, the part "The connection is marked by the server as unrecoverable." is misleading, since the client application has no idea what the server did - it just disconnected.