The underlying provider failed on Open.

Question

Hi I am exposing a database in a WCF web service using the Entity Framework. When I run it I get this exception:

EntityException was unhandled by user code - The underlying provider failed on Open, the error occurs in the line "if (query).Count<User>() == 1)" in the code below:

using (var context = new SHADBEntities1())
      {
        var users = context.Users;

        var query =
          from user in users
          where user.Email == email && user.Password == password
          select user;

        if (query.Count<User>() == 1)
          return true;
        else
          return false;
      }

Anybody knows what is the problem? Do you need more information? Below I have pasted the exception details:

System.Data.EntityException was unhandled by user code
  Message=The underlying provider failed on Open.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
       at System.Data.EntityClient.EntityConnection.Open()
       at System.Data.Objects.ObjectContext.EnsureConnection()
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__3[TResult](IEnumerable`1 sequence)
       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
       at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
       at SHAServiceRole.BusinessLogicClasses.BusinessLogic.UserLoginBL(String email, String password) in C:\Users\a\Documents\Visual Studio 2010\SHA\SHAWebService\SHAServiceRole\BusinessLogicClasses\UsersBL.cs:line 23
       at SHAServiceRole.SHAService.UserLogin(String email, String password) in C:\Users\a\Documents\Visual Studio 2010\SHA\SHAWebService\SHAServiceRole\SHAService.svc.cs:line 23
       at SyncInvokeUserLogin(Object , Object[] , Object[] )
       at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
       at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
  InnerException: System.Data.SqlClient.SqlException
       Message=An attempt to attach an auto-named database for file C:\Users\a\Documents\Visual Studio 2010\SHA\SHAWebService\SHAServiceRole\App_Data\SHADB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=14
       LineNumber=65536
       Number=15350
       Procedure=""
       Server=\\.\pipe\09C87934-CF43-45\tsql\query
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
            at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
            at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
            at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
            at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
            at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
            at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
            at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
            at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
            at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
            at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
            at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
            at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
            at System.Data.SqlClient.SqlConnection.Open()
            at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
       InnerException:

 

---

rune007

Answer 1

Hi!

The stacktrace you supplied contains the following information:

Message=An attempt to attach an auto-named database for file C:\Users\a\Documents\Visual Studio 2010\SHA\SHAWebService\SHAServiceRole\App_Data\SHADB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

And this is your problem. Something has already loaded your database in a another SQL instance of some kind.

Try to figure out what has done this, and you may be a step closer to your solution.

Hope this helps!

---

--Rune

Answer 2

Hi Mr. Rune

Thank you for your reply! It must be said that my WCF service is a service role in a Windows Azure project.

I have been surprised by this error, I am a beginner, and so far MS products has been easy to work with. Now I am trying do do something which is almost a Hello World for WCF and Entity Framework and SQL Server and then I get this awfull error.

Yes your respons has narrowed down the identity of the error, but I still don't know what to do.

I have uploaded my project files to Skydrive (2 files: WCF web service and ASP.NET MVC client) 

- it's an utterly simple Azure project with the WCF service role, it contains the DB in the App_Data folder.

- Then there is the client which is an ASP.NET MVC application. With which I am trying to connect with the service.

http://cid-5c0bc0a6f7bdc3c6.office.live.com/browse.aspx/Rune?permissionsChanged=1&1

I am running on a brand new PC and I have downloaded the newest products from MS less than a week ago, I haven't done any configuration or anything.

Thank you for the help so far and I am wishing you can help me further to make this thing which I thought would be simple.

 

---

rune007

Answer 3

Hi!

Unfortunatly, I'm not so familiar with how Azure works, and how the development environment for Azure works, but I tested your code here, and it works for me. But I discovered in one run that I suddenly got a new address to the Azure service. So maybe this is your problem? That Azure creates two services, and both tries to work against the same database?

I'm afraid I can't help you anymore, the message says that the databasefile is already in use and that is what you should try to find out why.

You could try to post at the Azure forum (http://social.msdn.microsoft.com/Forums/en-US/category/windowsazureplatform) and see if anyone there has a suggestion on your problem.

---

--Rune

Answer 4

Hi Rune

Thank you for your reply, it turns out to be the answer somewhat to my problem.

Your observation:

"I discovered in one run that I suddenly got a new address to the Azure service. So maybe this is your problem? That Azure creates two services, and both tries to work against the same database?"

,seems to be crucial. I tried to test it out putting my code in just a straight WCF web service from VS (Instead of an Azure service role), and everything now worked as it should.

So that seems to be there where the problem lies. I must then turn to the Azure forum to solve it.

 

 

---

rune007