Question: How do you resolve the error in C# Asp.Net 6 that says "Microsoft.Data.SqlClient.SqlException (0x80131904): New transaction is not allowed because there are other threads running in the session."?
Please Login to see the rest of the answer
Answer: This error is because you have specified that the Database Context runs as a Singleton Life Time in your Startup.cs class.
Remember if you explicitly specified that the Life Time of the Database Context should be of a Singleton, then you simply are saying that there will be only one instance of the Database Context running throughout the Life Cycle of your application until the Application Pool Recycles the.
This can create a nightmare for developers to debug the code, defining the Database Context Life Time as Singleton means that all requests and transactions going to the Database should only use one initialized DB Context.
This is causing that error as you are calling a function that calls the Database Context in an Async Function, this call runs the request on a background thread (meaning, the initial call to the database grabs the only one Singleton Initialized database that every request should use, and runs away with it in the background thread, leaving other requests stranded waiting for that background thread to return the instance of the Database Context. Thank Lord!!)
A rule of thumb will be to have every abstract/interfaced function have its counterpart, meaning have two functions one that utilizes Async Await and another one that does not. This will help you in the future in case you realized that calling a none Async Function from an Async Function is simply a bad idea, then you can change to call the none Async Interfaced function from a none Async Controller function.
I hope this helps you, if it does leave a comment below to let everyone know.