none
EF Code first w/ SQL Compact: Underlying provider failed on open: The database file cannot be found. Check the path to the database RRS feed

  • Question

  • I am about to lose it.  I am seriously about to lose my mind.

    My application, which was working fine, has stopped working in a particular area.  My automated tests, which were working fine a week ago, have stopped working.

    The problem is all centered around creating multiple databases with Entity Framework code first using Sql Compact 4.0. 

    Put simply, I create databases (an instance of a class based on DbContext), I save, I delete the database file.  I create another database using the same file name later.    Did I mention this was working fine?  It was, btw.  Just dandy.

    At some point I had had a problem with the error above if I created a database shortly after having deleted the underlying file.  I realized that if I created an instance of my DbContext class ("db") and then called Initialize ("db.Database.Initialize(true)"), the problem seemed to go away.

    It's back now, and it won't go away.  Here's the full exception:

    System.Data.EntityException: The underlying provider failed on Open. ---> System.Data.SqlServerCe.SqlCeException: The database file cannot be found. Check the path to the database. [ Data Source = c:\IDTOffline\UFT++ GUI1.tcp ]

    I'm going to go cry now.  I'm not even coherent enough at the moment to describe my problem more fully than that.  Maybe someone has seen this before.  Good night.

    Saturday, August 11, 2012 4:10 AM

All replies

  •  Note that it appears to be related to adding in support for migrations.  This is the code I added that made it break:

    var connectionString = BuildConnectionString(fileName);
    var configuration = new IDTTCDAL.Migrations.Configuration();
    configuration.TargetDatabase = new DbConnectionInfo(connectionString, "System.Data.SqlServerCe.4.0");
    var migrator = new DbMigrator(configuration);
    migrator.Update();

    Is that not the way to do migrations?  Is there something else I could do to have migration support and also make this problem disappear?
    Saturday, August 11, 2012 4:53 AM
  • I have a workaround, if not a solution or deep understanding of what's going on. 

    First, let me explain a bit about my environment.  I am primarily connecting to a MySql database (my "central server").  I allow users to export data to external files, which are Sql Compact 4.0 databases.  I also use Sql Compact 4.0 databases to architecture is also used to store "offline" versions of data that the user currently has checked out for editing.

    This architecture helps explain why I'm periodically deleting databases and then recreating them (as the user checks data in and out, etc.).

    I tried a couple of things.  First I tried setting an Initializer for the database when I was about to open a SQL Compact database.  I would then clear the Initializer when I was done so it wouldn't be applied to the MySql database.  Here's the code for that:

    internal sealed class Initializer : MigrateDatabaseToLatestVersion<MyDatabase, Migrations.Configuration> { }
    // Inside the MyDatabase class:
    private static bool s_aboutToCreateSQLCompact;
    public static bool AboutToCreateSQLCompact {
        get { return s_aboutToCreateSQLCompact; }
        set {
            if (s_aboutToCreateSQLCompact == value) return;
                s_aboutToCreateSQLCompact = value;
                if (value)
                    Database.SetInitializer(new Initializer());
                else
                    Database.SetInitializer<IDTDatabase>(null);
            }
        }

    That had the same problem.   I also tried checking to see if a migration was necessary before calling Update:

    var configuration = new IDTTCDAL.Migrations.Configuration();
    configuration.TargetDatabase = new DbConnectionInfo(connectionString, "System.Data.SqlServerCe.4.0");
    var migrator = new DbMigrator(configuration);
    var migrations = migrator.GetPendingMigrations();
    if (migrations != null && migrations.Any()) migrator.Update();
    That didn't help either.  In the end, I decided that there were many cases where I knew for sure I did not need to check for migrations at all.  If I had just created the database, I knew I could bypass checking migrations when I opened it.  By avoiding that code whenever possible, I was able to prevent the error from happening.
    Sunday, August 12, 2012 12:10 AM
  • Hi,

    Not sure what is your exact situation but at some point it seemed a bit like if you tried to migrate the database when the file is deleted ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Sunday, August 12, 2012 6:39 PM
  • I don't think that's it, Patrice.  And I am still seeing the problem in some cases.  Here's an example of the sequence of events that can lead to the problem:

    1. I have a "file" (data) checked out, which creates an offline version of the file as a Sql Compact (EF) database (eg, "c:\offline\MyFile.sdf").
    2. I run my program, which opens that file.  In this situation I want to check for migrations, in case the schema has changed  since that offline database was created.  So I create a DbMigrator to check that.
    3. In this particular case, no migration is necessary (the schema hasn't changed).  I still had to create a DbMigrator and hook it up to the database in order to check that, though.
    4. I check in the file, which deletes c:\offline\MyFile.sdf.
    5. I check out the file again, which is supposed to create MyFile.sdf.  I'm not using DbMigrator at all at this point.  I'm simply creating an instance of my DbContext ("MyDatabase"), adding objects to it, and calling offlinedb.SaveChanges().

    That's the point at which I get the error.  True, the file doesn't exist at this point, since I'm trying to create the database.  Under normal circumstances, of course, Entity Framework correctly creates the database for me like I would expect.

    At this point I think I'm going to have to do two things to work around this:

    • Continue only using DbMigrator to check for changes when I absolutely have to.
    • Before I check for migrations using DbMigrator, I'm going to just try to open the database and watch for exceptions.  There is no "datbase needs to be migrated" exception, but if I get any EntityException at all I'll try migrating and re-opening the database. 

    That won't make the problem go away, but it should greatly reduce the occurrence (since it would only happen if the user went through a particular sequence of check-ins/exports AND there was a schema change, which should be pretty rare once the product is release).

    I'm still hoping someone will know something about this "file not found" error.  I've got to move on from this, but hopefully at some point I'll get a chance to dig into the DbMigrator and EF source code and figure out why this is happening.

    Monday, August 13, 2012 1:04 PM
  • Have you seen the full call stack to see what raises this ? As it seems to be new when using migration it's likely related to do this (also I'm not sure to understand why you seems to migrate the file to just delete and recreate it). Just for testing you could try to create a project that migrates the file and another that delete/recreates. This is not to solve the issue but ot find out if it still happens when you are 100% sure that DbMigrator is really entirely killed before doing the delete/recreate step in case it would have some side effect.

    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".


    Tuesday, August 14, 2012 10:07 AM
  • That's not quite the sequence.  It's more like this:

    • I have a data object that I'm editing with an offline file.  I'm opening that file.  I have no idea at this point whether or not it needs to be migrated, so I have to check.  I'm not actually migrating it, I'm just using DbMigrator (migrator.Any()) to see if a migration is required. Note that no error happens here.
    • I check in the data object.  That deletes the offline file.
    • I check out the data object.  That creates a new version of the offline file.  DbContext.SaveChanges() is failing at that point, and that's where I can get the call stack.  Unfortunately, I suspect that the issue is not specifically in the call stack but rather with the state of the world at the point when I call SaveChanges, since obviously that works under normal circumstances.

    That's a good idea about creating two separate projects to separate the tasks.  I'll try that.

    FWIW, here's the call stack:

    System.Data.SqlServerCe.SqlCeConnection.Open(Boolean silent)
    System.Data.SqlServerCe.SqlCeConnection.Open()
    System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
    System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
    System.Data.EntityClient.EntityConnection.Open()
    System.Data.Objects.ObjectContext.EnsureConnection()
    System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
    System.Data.Entity.Internal.InternalContext.SaveChanges()
    System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
    System.Data.Entity.DbContext.SaveChanges()
    MySWTCDAL.MySWDatabase.Export(PersonalityVersion version, String fileName, Boolean useDataContractSerializer, Boolean encrypt) in C:\MySW\main\src\MySWTCDAL\MySWDatabase.cs: line 645
    MySWTCDAL.PersonalityVersion.CheckOut(Boolean newPersonality, PersonalityVersion& checkedOutPersonality) in C:\MySW\main\src\MySWTCDAL\PersonalityVersion.cs: line 570
    MySWTCAutomatedTests.DataLayerTests.VersionControlTestOnline() in C:\MySW\main\src\MySWTCAutomatedTests\DataLayerTests.cs: line 315
    • Edited by David Cater Tuesday, August 14, 2012 4:37 PM Formatting
    Tuesday, August 14, 2012 4:36 PM
  • I meant rather the exception call stack to see what triggers the call that finally produce this failure. If this is MySWTCAutomatedTests.DataLayerTests.VersionControlTestOnline()  what is the purpose of this method ?


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Tuesday, August 14, 2012 7:40 PM
  • That's part of my automated test suite, and goes through a series of operations that I would expect to occur within the program.   The crucial fact here is that the call that triggers the exception works fine if I don't use the DbMigrator object earlier in the process.  So it appears that the use of DbMigrator changes some internal state of the world in some way so that subsequent calls that you would expect to work fail instead.
    Friday, August 17, 2012 1:27 PM
  • And does it work it you comment those tests in case it would ne some kind of interaction between those tests and the migration steps ?

    I'm runnign out of ideas to diagnose this. Hopefully someone else will help...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Friday, August 17, 2012 6:54 PM
  • What version of EF are you using, try to update to the latest version to see whether the exception has disappeared.
    Wednesday, August 22, 2012 8:54 AM