MSSQL Error 'The underlying provider failed on Open' using WCF & EntityFramework

c# entity-framework sql-server wcf

Question

I use the EntityFramework 6 to make database queries:

public class Entities : DbContext {
    public Entities ();

    public DbSet<Analysis> Analysis { get; set; }
}

public class TableController {
    public function List<DataSet> GetDataSets () {

        var query = "SELECT ... FROM ... WHERE ...";

        var queryReturnType = typeof(Analysis);

        var result = this.Entities.Database.SqlQuery(queryReturnType, query);

        foreach (var elem in result) {
             ...
        }        
    }
}

I use all of this in a self-hosting WCF-service, which allows multi-threading:

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single, UseSynchronizationContext = false, ConcurrencyMode = ConcurrencyMode.Multiple)]
public class RestService : IRestService {
    ...
}

The query works fine for 90%-95% of all requests, but sometimes I receive an exception from the EntityFramework at the line where I try to iterate over the results of the query (foreach):

Error with underlying provider with open

at System.Data.Entity.Core.EntityClient.EntityConnection.Open()

at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)

at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func'1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)

at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65'1.b__63()

at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func'1 operation)

at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)

at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, ExecutionOptions executionOptions, Object[] parameters)

at System.Data.Entity.Internal.InternalContext.<>c__DisplayClass14'1.b__13()

at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()

The service is hosted on a Windows Server 2012 R2 and when I execute the service on a local Windows 10 machine, there error does not occure.

What may cause the error?

1
0
10/25/2016 9:29:51 AM

Accepted Answer

Looking at your connection string, it looks valid. I found this blog post, the problem here is that they were using Integrated Security. If you are running on IIS, your IIS user needs access to the database.

If you are using Entity Framework with Transactions, Entity Framework automatically opens and closes a connection with each database call. So when using transactions, you are attempting to spread a transaction out over multiple connections. This elevates to MSDTC.

(See this reference for more information.)

Please read this article: MSSQL Error 'The underlying provider failed on Open'

Entity Framework The underlying provider failed on Open

Error: The underlying provider failed on Open. How to resolve it?

0
5/23/2017 12:19:14 PM

Popular Answer

I got the same issue with EF. At beginning, the exception was thrown once a day, after 10 times/day and recently 48 times/day. Of course, we cannot reproduce this "The underlying provider failed on open". I did everything, be sure we don't leak connections, always use

using (var context = new DataContext()) {
    List<Billing> result = (from item in context.Billing.AsNoTracking()
                            select item).ToList();
    return result;
}

For us, the solution was to force the opening of the connection.

public DataContext() : base("your connectionstring")               
    **this.Database.Connection.Open();**
    this.Configuration.LazyLoadingEnabled = false;
    this.Configuration.ProxyCreationEnabled = false;
}

Hope it works for you, too!



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow