SqlCommand async Task may never complete because of unhandled exception

[SergeyBokhantsev]

Describe the bug

Async Task of SqlCommand sometimes could never go to Completed state (if exception occurs during command execution).
Thus, if you await the SqlCommand then your code may stuck forever at waiting the task completion.

Observed case:
There is a trivial code to interact with Azure SQL DB via EntityFramework. We noticed that sometimes the module that is working with DB freezes forever. Analyzing memory dumps we saw that it is always stuck awaiting EntityFramework operations like Select, Find, SaveChangesAsync etc.
In every case there is an unobserved exception logged also with the following details about the root exception:
Invalid operation. The connection is closed.
System.AggregateException: System.InvalidOperationException:
at Microsoft.Data.SqlClient.SqlConnection.GetOpenTdsConnection
at Microsoft.Data.SqlClient.SqlCommand+<>c__DisplayClass186_0.b__0
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw
at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop
at System.Threading.Tasks.Task.ExecuteWithThreadLocal

To reproduce

Unfortunately I don't have code to reproduce the issue because in our case the issue was caused by a sporadic network connection problem.
But taking into account the above exception call stack, the issue in source code can be seen:

• Because of some exception (network issue in our case) that happens here

  SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs

  Line 2407 in bb4c3b7

  endFunc(this, tsk, true, endMethod /*inInternal*/);

• the execution gets here into the catch block https://github.com/dotnet/SqlClient/blob/bb4c3b7a3a3125b23bc8ddce105ff3ba67a25414/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs#L2448C29-L2448C70

• where the _activeConnection.GetOpenTdsConnection() throws InvalidOperationException at

  SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnection.cs

  Line 2152 in bb4c3b7

  throw ADP.ClosedConnectionError();

• thus execution never reaches

  SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlCommand.cs

  Line 2450 in bb4c3b7

  globalCompletion.TrySetException(e);

  
  and the task never gets completed.

I tested the described case by compiling custom SqlClient.dll where an artificial exceptions was thrown in lines 2407 and 2448, and can confirm it causes same behavior and Entity Framework operation stuck forever awaiting SqlCommand.

Expected behavior

Exceptions during SqlCommand execution must not prevent root Task from completion.

Further technical details

Microsoft.Data.SqlClient version: 5.2.0
EntityFramework 6.4.4
.Net 8

[cvdsoftware]

I experienced this problem myself a few times, with a locally hosted SQL Server, also caused by a network hiccup.

We have a service monitoring tool, if the service seems to be stuck we restart it.

Would be nice when this is fixed to backport the fix to older versions.