The underlying provider failed on Open EF6 iis

Question

After I upgraded the program to .net 4.5, upgraded EntityFramework to the latest and used Autofac to make some adjustments to the application, I encountered this problem:
After the program has been running for a few days, it will suddenly start to report frequently:

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidCastException: Cannot cast an object of type "System.Data.SqlClient.SqlTransaction" to type "System.Transactions.SafeIUnknown".。
   在 System.Transactions.Transaction.JitSafeGetContextTransaction(ContextData contextData)
   在 System.Transactions.Transaction.FastGetTransaction(TransactionScope currentScope, ContextData contextData, Transaction& contextTransaction)
   在 System.Transactions.Transaction.get_Current()
   在 System.Data.ProviderBase.DbConnectionPool.GetFromTransactedPool(Transaction& transaction)
   在 System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   在 System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   在 System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   在 System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   在 System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   在 System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   在 System.Data.SqlClient.SqlConnection.Open()
   在 System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   在 System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
   在 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.c__DisplayClass2_0.b__0()
   在 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   在 System.Data.Entity.Core.EntityClient.EntityConnection.Open()

I think this should be caused by changing the program to dependency injection, but I created a counter in Dbcontext creation and Dispose, confirming that all Dbcontext has been dispose, I don't understand what went wrong
This is my connection string

Data Source=***; Initial Catalog=cloud; MultipleActiveResultSets=true; Pooling=True; UID=sa;PWD=***;

In some places, I use transactions, but I rarely use them.

public TransResult UseTrans(Action<DbTransaction<TContext>> action)
        {
            if (currentTransHasError)
            {
                return new TransResult() { IsSuccess = false, Message = currentTransErrorMessage };
            }
            var result = new TransResult() { IsSuccess = true };
            bool isNested = true;
            try
            {

                if (currentContextTrans == null)
                {
                    isNested = false;
                    currentContextTrans = db.Database.BeginTransaction();
                }
                var trans = new DbTransaction<TContext>(this, isNested);
                action?.Invoke(trans);
                trans.Commit();

            }
            catch (Exception e)
            {

                result.Message = currentTransErrorMessage = e.Message;
                result.IsSuccess = false;
                currentTransHasError = true;
                if (currentContextTrans != null)
                {
                    currentContextTrans.Rollback();

                }

                // TODO: Handle failure
            }
            finally
            {
                if (!isNested)
                {
                    if (currentContextTrans != null)
                    {
                        currentContextTrans.Dispose();
                        currentContextTrans = null;
                    }
                    currentTransErrorMessage = string.Empty;
                    currentTransHasError = false;
                }
            }
            return result;

        }

It would be awesome if you could provide a minimal reproducible example. — mjwills
are you using .Net Framework or .Net Core? i can assume you're using MVC 5 using .Net Framework and implemented DI. I have some encountered issues back then, i would suggest you use .Net Core (as DI is already part of the build), and select .Net Framework as platform, as .Net Core now supports .Net Framework. — Jeff
If you use .Net Core, this will be a lot of work, and I am worried that I may encounter many unknown problems.@Jeff — JoyLing
yeah I undertstand, it goes both ways. implementing a DI on MV5 is somehow tricky too. I have been using Unity.MVC in my case for MVC5 projects, worked fine at my end using this DI implementation using Unity using this link -> tech.trailmax.info/2014/09/… — Jeff
@mjwills, sorry for the confusion. what i meant was .Net Framework libraries can be used on a .Net Core environment, given it supports the .Net Core version. The user still has to select .Net Framework though when creating a new project in .NetCore, select the dropdown and chose .NetFramework -> clip2net.com/s/44yRac6 — Jeff

Karim Karim · Accepted Answer · 2020-10-16T12:41:31

we had a similar issue and we resolved it by adding multiple active result set in database server.

"Multiple Active Result Sets (MARS) is a feature that works with SQL Server to allow the execution of multiple batches on a single connection. When MARS is enabled for use with SQL Server, each command object used adds a session to the connection."

The underlying provider failed on Open EF6 iis

1 Answers