Unable to cast object of type 'Microsoft.Data.ProviderBase.DbConnectionClosedConnecting' to type 'Microsoft.Data.SqlClient.SqlInternalConnectionTds'

[mrvogiacu]

Describe the bug

When I start the NET CORE API, and refresh my website client. I got this exception

System.InvalidCastException: Unable to cast object of type 'Microsoft.Data.ProviderBase.DbConnectionClosedConnecting' to type 'Microsoft.Data.SqlClient.SqlInternalConnectionTds'.
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()

To reproduce

Not sure how to reproduce. I see this exception happened when the API is just starting, or the API wakup after idle a long time

Expected behavior

A clear and concise description of what you expected to happen.

Further technical details

NET 8
SQL Server 2022
IIS 10

[apoorvdeshmukh]

@mrvogiacu Can you provide any steps to reproduce this issue? Can you mention the specific API when you are seeing this issue?

[mrvogiacu]

Hi @apoorvdeshmukh ,

It's really hard to reproduce it.
As I said, sometimes I get it when I start the local API and refresh my local website, it calls the local API, and I get this exception in the log file. In the production hosting, when the IIS sleeps because no requests, and I access the production website, I get the same exception. But if I continue calling the API, I never get this exception

More information, I set up the DI for the SQL class with the scope is Singleton.
For now, this bug is resolved when I remove the SQL DI and create the dynamic SQL instance each time I call to DB

[apoorvdeshmukh]

@mrvogiacu Thank you for the response. I'll discuss this internally with the team and get back to you.
Meanwhile, a search on this exception returned following threads. Does any of the workaround discussed in these threads work for you?
dotnet/efcore#11595
DevExpress/DevExtreme.AspNet.Data#352 (comment)

[mrvogiacu]

Hi @apoorvdeshmukh ,
I try to research and not see the link you gave me above. I take a look, and all of them talk about EF, but I am using Dapper with store procedure.

I guess the problem can be DI, because after I stopped using DI and let the SQL instance be created by my DA class, I did not get this exception anymore.
Before this exception, I was already using Scoped DI and got another problem :D