SqlException.IsTransient returns false for TCP Provider error 35 (broken pipe) — transport-level failures should be considered transient

### Describe the bug
When a pooled SQL connection goes stale and the underlying TCP socket is broken, SqlException.IsTransient returns false, even though this is a recoverable transient error. Retrying the operation on a fresh connection succeeds immediately.

This forces consumers to implement custom exception-chain walking logic to detect what should be a standard transient failure case.

```
Exception Details:
The failure occurs when attempting to execute a command on a connection that was retrieved from the pool but whose underlying TCP socket has been closed (e.g., by a load balancer idle timeout or network interruption).

Exception message:
System.AggregateException: One or more errors occurred. (A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 35 - An internal exception was caught))

---> Microsoft.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 35 - An internal exception was caught)

---> System.IO.IOException: Unable to write data to the transport connection: Broken pipe.

---> System.Net.Sockets.SocketException (32): Broken pipe
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.CreateException(SocketError error, Boolean forAsyncThrow)
at Microsoft.Data.SqlClient.ManagedSni.SniNetworkStream.WriteAsync(ReadOnlyMemory`1 buffer, CancellationToken cancellationToken)
at Microsoft.Data.SqlClient.ManagedSni.SslOverTdsStream.WriteAsync(ReadOnlyMemory`1 buffer, CancellationToken cancellationToken)
at System.Net.Security.SslStream.WriteAsyncInternal[TIOAdapter](ReadOnlyMemory`1 buffer, CancellationToken cancellationToken)
at Microsoft.Data.SqlClient.ManagedSni.SniSslStream.WriteAsync(ReadOnlyMemory`1 buffer, CancellationToken cancellationToken)
at Microsoft.Data.SqlClient.ManagedSni.SniPacket.WriteToStreamAsync(Stream stream, SniAsyncCallback callback, SniProviders provider)
at Microsoft.Data.SqlClient.ManagedSni.SniTcpHandle.SendAsync(SniPacket packet)
at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
at Microsoft.Data.SqlClient.TdsParser.TdsExecuteRPC(...)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(...)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)

Stack trace:
September 7, 2026, 11:23
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
---> System.AggregateException: One or more errors occurred. (A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 35 - An internal exception was caught))
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
---> Microsoft.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 35 - An internal exception was caught)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
---> System.IO.IOException: Unable to write data to the transport connection: Broken pipe.
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
---> System.Net.Sockets.SocketException (32): Broken pipe
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.CreateException(SocketError error, Boolean forAsyncThrow)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.ManagedSni.SniNetworkStream.WriteAsync(ReadOnlyMemory`1 buffer, CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.ManagedSni.SslOverTdsStream.WriteAsync(ReadOnlyMemory`1 buffer, CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.ManagedSni.SslOverTdsStream.WriteAsync(ReadOnlyMemory`1 buffer, CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at System.Net.Security.SslStream.WriteAsyncInternal[TIOAdapter](ReadOnlyMemory`1 buffer, CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.ManagedSni.SniSslStream.WriteAsync(ReadOnlyMemory`1 buffer, CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.ManagedSni.SniPacket.WriteToStreamAsync(Stream stream, SniAsyncCallback callback, SniProviders provider)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.ManagedSni.SniTcpHandle.SendAsync(SniPacket packet)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, IList`1 rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteNonQueryInternal(CommandBehavior behavior, AsyncCallback callback, Object stateObject, Int32 timeout, Boolean inRetry, Boolean asyncWrite)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(Func`3 beginMethod, Func`2 endFunction, Action`1 endAction, Object state, TaskCreationOptions creationOptions)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQueryAsync(CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.Internal.Data.DatabaseCommand.ExecuteAsync[TResult](Func`3 executeAsync, Func`2 executeSync, CancellationToken cancellationToken, Boolean disallowAsyncCancellation, Boolean isConnectionMonitoringQuery)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.Internal.Data.DatabaseCommand.ExecuteAsync[TResult](Func`3 executeAsync, Func`2 executeSync, CancellationToken cancellationToken, Boolean disallowAsyncCancellation, Boolean isConnectionMonitoringQuery)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.Internal.Data.DatabaseCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken, Boolean disallowAsyncCancellation, Boolean isConnectionMonitoringQuery) in /_/src/DistributedLock.Core/Internal/Data/DatabaseCommand.cs:line 64
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.Internal.Data.DatabaseCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken, Boolean disallowAsyncCancellation) in /_/src/DistributedLock.Core/Internal/Data/DatabaseCommand.cs:line 58
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.SqlServer.SqlApplicationLock.ExecuteAcquireCommandAsync(DatabaseConnection connection, String lockName, TimeoutValue timeout, CancellationToken cancellationToken) in /_/src/DistributedLock.SqlServer/SqlApplicationLock.cs:line 60
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.SqlServer.SqlApplicationLock.ExecuteAcquireCommandAsync(DatabaseConnection connection, String lockName, TimeoutValue timeout, CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.SqlServer.SqlApplicationLock.Medallion.Threading.Internal.Data.IDbSynchronizationStrategy.TryAcquireAsync(DatabaseConnection connection, String resourceName, TimeoutValue timeout, CancellationToken cancellationToken) in /_/src/DistributedLock.SqlServer/SqlApplicationLock.cs:line 43
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.SqlServer.SqlApplicationLock.Medallion.Threading.Internal.Data.IDbSynchronizationStrategy.TryAcquireAsync(DatabaseConnection connection, String resourceName, TimeoutValue timeout, CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.Internal.Data.DedicatedConnectionOrTransactionDbDistributedLock.TryAcquireAsync[TLockCookie](TimeoutValue timeout, IDbSynchronizationStrategy`1 strategy, CancellationToken cancellationToken, IDistributedSynchronizationHandle contextHandle) in /_/src/DistributedLock.Core/Internal/Data/DedicatedConnectionOrTransactionDbDistributedLock.cs:line 79
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.Internal.Data.DedicatedConnectionOrTransactionDbDistributedLock.TryAcquireAsync[TLockCookie](TimeoutValue timeout, IDbSynchronizationStrategy`1 strategy, CancellationToken cancellationToken, IDistributedSynchronizationHandle contextHandle)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.SqlServer.SqlDistributedLock.Medallion.Threading.Internal.IInternalDistributedLock.InternalTryAcquireAsync(TimeoutValue timeout, CancellationToken cancellationToken) in /_/src/DistributedLock.SqlServer/SqlDistributedLock.cs:line 83
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.Internal.DistributedLockHelpers.AcquireAsync[THandle](IInternalDistributedLock`1 lock, Nullable`1 timeout, CancellationToken cancellationToken) in /_/src/DistributedLock.Core/Internal/DistributedLockHelpers.cs:line 45
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.SqlServer.SqlDistributedLock.AcquireAsync(Nullable`1 timeout, CancellationToken cancellationToken) in /_/src/DistributedLock.SqlServer/SqlDistributedLock.IDistributedLock.cs:line 80
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.SqlServer.SqlDistributedLock.Medallion.Threading.IDistributedLock.AcquireAsync(Nullable`1 timeout, CancellationToken cancellationToken) in /_/src/DistributedLock.SqlServer/SqlDistributedLock.IDistributedLock.cs:line 16
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Medallion.Threading.DistributedLockProviderExtensions.AcquireLockAsync(IDistributedLockProvider provider, String name, Nullable`1 timeout, CancellationToken cancellationToken) in /_/src/DistributedLock.Core/DistributedLockProviderExtensions.cs:line 36
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Elsa.Workflows.Runtime.Distributed.DistributedWorkflowClient.WithLockAsync[R](Func`1 func)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Elsa.Workflows.Runtime.Distributed.DistributedWorkflowClient.WithLockAsync[R](Func`1 func)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at Elsa.Workflows.Runtime.Distributed.DistributedWorkflowClient.RunInstanceAsync(RunWorkflowInstanceRequest request, CancellationToken cancellationToken)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
September 7, 2026, 11:23
at System.Runtime.CompilerServices.AsyncMethodBuilderCore.Start[TStateMachine](TStateMachine& stateMachine)
330a218dc06b48f9a2413f9a3ba86f23
system-identifier
```

### To reproduce
Unfortunately this error is fundamentally transient which means that we cannot consistently reproduce it, this is especially prevalent in our live environment which connects our .NET application to AWS RDS via the AWS RDS proxy

### Expected behavior
SqlException.IsTransient should return true when the exception represents a transport-level failure with an underlying SocketException or IOException, as these are recoverable by retrying on a fresh connection.

### Further technical details
Environment
Microsoft.Data.SqlClient version: 6.1.1
Target framework: .NET 10.0
Platform: Linux (ECS container)
Database: AWS RDS SQL Server
Note: This issue began occurring after upgrading from .NET 8 to .NET 10. It's unclear whether this is a regression or simply more frequent due to runtime/networking changes.

**Additional context**
Current Workaround
We have to manually walk the exception chain:
```C#
private static bool IsTransientTransportError(Exception? exception)
{
while (exception != null)
{
if (exception is SocketException or IOException)
return true;

if (exception is AggregateException agg)
{
foreach (var inner in agg.InnerExceptions)
{
if (IsTransientTransportError(inner))
return true;
}
}

exception = exception.InnerException;
}
return false;
}
```
Suggested Fix":
The IsTransient implementation should consider a SqlException transient if:

Its inner exception chain contains a SocketException, or
Its inner exception chain contains an IOException related to transport (broken pipe, connection reset, etc.)
This would align with the stated purpose of DbException.IsTransient: to indicate "whether the error could be a transient error, i.e., if retrying the triggering operation may succeed without any other change."

Related
#649 — Original issue for implementing SqlException.IsTransient

Start at Microsoft.Data.SqlClient.SqlException.IsTransient and reproduce the pooled-connection failure described in the issue, focusing on TCP Provider error 35 and the nested broken-pipe exceptions. Done means this transport-level failure is recognized as transient, with regression coverage demonstrating that IsTransient returns true.

Written by the indexing model from the issue text.