Cannot attach the file as database / The underlying provider failed on Open

Question

I have an MVC3 application using the Entity Framework. I haven't used this application in about a year. Recently, I opened it up on a new PC and tried to run in debug mode and hit the following exception/stack trace in debug. The main exceptions seem to be "Cannot attach the file as database" and "The underlying provider failed on Open". I searched for these errors on stack overflow and the answers I found don't seem to match this situation. For example, one of the answers for "Cannot attach the file as database" suggests that an old version of the db might be lying around on the local db, but I don't see any databases in the local database when I browse to localdb in visual studio.

I am looking for guidance about what to investigate next to troubleshoot this issue. Any suggestions?

The actual code where it fails is:

            var currentHunt = (from ph in this.repo.GetAllPuzzleHunts()
                           orderby ph.PuzzleHuntId descending
                           select ph).FirstOrDefault();

This is the first time the web app tries to get data from the db via the Entity Framework.

FWIW this machine has Entity Framework 6.0, and I'm using Visual Studio 2012.

Exception/Stack trace follow:

System.Data.DataException was unhandled by user code
  HResult=-2146233087
  Message=An exception occurred while initializing the database. See the InnerException for details.
  Source=EntityFramework
  StackTrace:
       at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
       at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
       at System.Data.Entity.Internal.LazyInternalContext.<InitializeDatabase>b__4(InternalContext c)
       at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input)
       at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action)
       at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
       at System.Data.Entity.Internal.InternalContext.Initialize()
       at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
       at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
       at System.Data.Entity.Internal.Linq.InternalSet`1.GetEnumerator()
       at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
       at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
       at System.Linq.OrderedEnumerable`1.<GetEnumerator>d__0.MoveNext()
       at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
       at GutolMvcApp.Controllers.HomeController.Index() in c:\Users\jordanat\Documents\Visual Studio 2012\Projects\Gutol\Gutol\GutolMvcApp\Controllers\HomeController.cs:line 25
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41()
       at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _)
       at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End()
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
       at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()
  InnerException: System.Data.Entity.Core.EntityException
       HResult=-2146233087
       Message=The underlying provider failed on Open.
       Source=EntityFramework
       StackTrace:
            at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
            at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection()
            at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
            at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.<GetResults>b__9()
            at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
            at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
            at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
            at System.Lazy`1.CreateValue()
            at System.Lazy`1.LazyInitValue()
            at System.Lazy`1.get_Value()
            at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
            at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
            at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
            at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
            at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
            at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
            at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
            at System.Data.Entity.Internal.EdmMetadataRepository.QueryForModelHash(Func`2 createContext)
            at System.Data.Entity.Internal.InternalContext.QueryForModelHash()
            at System.Data.Entity.Internal.ModelCompatibilityChecker.CompatibleWithModel(InternalContext internalContext, ModelHashCalculator modelHashCalculator, Boolean throwIfNoMetadata)
            at System.Data.Entity.Internal.InternalContext.CompatibleWithModel(Boolean throwIfNoMetadata)
            at System.Data.Entity.Database.CompatibleWithModel(Boolean throwIfNoMetadata)
            at System.Data.Entity.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context)
            at System.Data.Entity.Internal.InternalContext.<>c__DisplayClasse`1.<CreateInitializationAction>b__d()
            at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
       InnerException: System.Data.SqlClient.SqlException
            HResult=-2146232060
            Message=Cannot attach the file 'C:\Users\...\Visual Studio 2012\Projects\Gutol\Gutol\GutolMvcApp\App_Data\PuzzleCollateral.mdf' as database 'PuzzleCollateral'.

Thanks for your help!

Solution 1

Check your connection strings. If you're on a new computer you may need to change them somewhat or open a port in the firewall.

One simple way to get the proper connection string is to connect to the DB Server and view the properties.

1. Open SQL Server Object Explorer (View > SQL Server Object Explorer)
2. Connect to the DB Server (Click "Connect to Server" and enter "(LocalDB)\v11.0")
3. Right click on the appropriate DB and select "Properties"
4. View the connection string

Solution 2

Try it this way:

https://stackoverflow.com/a/16339164/1390999

Open the "Developer Command Propmpt for VisualStudio" under your start/programs menu. Run the following commands:

sqllocaldb.exe stop v11.0

sqllocaldb.exe delete v11.0

After that, try to Start your MVC Application

Comments

[DanJosef]

Thanks for the suggestion. I looked into this guide for connection strings: creating a connection string. I wrote the following connection string and added it to the web.config file for my project: <add name="PuzzleCollateralContext" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Puzz‌​leCollateral.mdf;Int‌​egrated Security=True" providerName="System.Data.SqlClient" /> Even with the new connection string I get the same exception.I updated Web.config).

[Michael Richardson]

I use localdb on one of my projects. My connect string doesn't contain anything about AttachDbFilename, but I see that they mention it in the tutorial. Try something like <add name="PuzzleCollateralContext" connectionString="Data Source=(LocalDB)\v11.0;Initial Catalog=PuzzleCollateral;Int‌​egrated Security=True" providerName="System.Data.SqlClient" />

[DanJosef]

Again, I get the same error. Is there something I can do at runtime to see that it's using the right connection string? I find it surprising that the exception says: {"Cannot attach the file 'C:\\Users\\<snip>\\App_Data\\PuzzleCollateral.mdf' as database 'PuzzleCollateral'."} I don't know where it's inferring the name PuzzleCollateral.

[Michael Richardson]

Did you try connecting to the DB as I mentioned in the edited answer?

[DanJosef]

Yes, I tried this. The connection string for localdb reported by SSOE is: Data Source=(localdb)\Projects;Initial Catalog=master;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False. Using this connections string in Web.Config I get the same result. Seeing as how I can't seem to make any change on the runtime behavior, I think I might be editing the connection string in the wrong place, or it's not using the string I think it should be. Is that possible? I'm editing the web.config file in the MVC project.

[Michael Richardson]

When you connected to the localdb server did you see the specific database (PuzzleCollateral) you're trying to connect to? The connection string you're giving is for the "master" db. I'm not positive that it wouldn't work, but if the db you're looking for isn't there, then it definitely won't work.

[DanJosef]

Nope, it's not there. This is a new machine so the DB does not exisit locally. My understanding is that Entity Framework will create it on the fly. The failure/exception occurs the first time it tries to access the missing db. That said, I'm even having trouble with EF 6.0 with a brand new project. I started a new project, created some code-first objects and populated them in debug, but the database doesn't show up locally via Visual Studio. Maybe I need to use SSMS?

[Michael Richardson]

You could try SSMS, but I don't think you'll get any different results. I've never used Code First, but looking at this page: msdn.microsoft.com/en-us/data/jj193542.aspx, it appears that the database should be generated on the first execution. I would definitely look into what's going on with the fresh project.

[DanJosef]

Found the issue... I couldn't see the dbs because i was connected to (localdb)\Projects instead of (localdb)\v11.0. Once I could see the db I deleted it in SSMS, and the project started working using the connection string you suggested in your comment from yesterday (connectionString="Data Source=(LocalDB)\v11.0;Initial Catalog=PuzzleCollateral;Int‌​egrated Security=True"). I am guessing that in the process of switching machines, running the unit tests, and upgrading to EF 6.0, I had a version incompatibility with the db.