Migrated database going to Common7/IDE folder

Question

Hi,

I've got a SQL Server Compact Edition 4.0 database file that I'm using in my program.  It was created when I was using EF 4.1.

I've updated the VS 2010 project so that I have EF Version 4.4.0.0 installed.

I enabled the project to do automatic migrations, and created an initial migration using the -IgnoreChanges flag.  Instead of modifying the existing database, which is in the same directory as the executable, it's creating a database in the "Program Files/Microsoft Visual Studio 10.0/Common7/IDE" directory.  I want it to update the working database, so that when I send out new versions of the program, it will automatically update the clients' databases with the new tables.

How do get the migrations process to work on the live .sdf file that's in the executable directory?

Thanks,
Dan

---

Dan Solis

Answer 1

Hi Dan,

Welcome to the MSDN forum.

I am not sure what -IgnoreChanges flag is. Could you please let me know some specific information?

Here is the auto migration guide: http://msdn.microsoft.com/en-US/data/jj554735

Good day.

---

Alexander Sun [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Alexander,

I followed the process you listed, and it creates and modifies the database in the "Program Files/Microsoft Visual Studio 10.0/Common7/IDE" directory."  I want it to work on the liveSQL Server CE 4.0 database file (.sdf) that's in the executable's working directory.  Why won't it do that?

I can trigger the process by doing the following:

1.  Add two new class objects to the DbContext-derived class.

2. Then in the Package Manager Console do the following:

---------------------------------------------------------------------------------------------------------

PM> enable-migrations -EnableAutomaticMigrations
Code First Migrations enabled for project VirtualDrill.
PM> Update-Database
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
No pending code-based migrations.
Applying automatic migration: 201208271736171_AutomaticMigration.
Running Seed method.
PM> 

---------------------------------------------------------------------------------------------------------

When I do this, a new, EMPTY version of the database file (.sdf) appears in the ".../Common7/IDE" directory.  This is useless.

Thanks,

Dan

---

Dan Solis

Answer 3

Hi,

As far as I know, migration is for Code First. Could you please share more specific steps to do migrations?

Answer 4

Summary:

I'll start with the summary and then follow with the details. Automatic migration is not happening on the live .sdf database file in the working directory.  It's as if that .sdf file didn't exist.  Instead, all configuration action is taken on a new, empty database file which it creates on in the ".../Common7/IDE" directory.  How do I get it to work on my live file in the working directory?

Details:

The project uses Code First EF in Visual Studion 2010 Ultimate.  The project was started with EF 4.1.  I just updated the project to EF 4.4.

The database file is a SQL Server Compact Edition 4.0 file, which is located in the same directory as the executable (i.e., the working directory).  Everything has been working fine for some time now, but I want to add two DbSets to the DbContext, and hence two new tables to the database file.  When I deploy the new version of the program to customers, I want the program to seamlessly just add the two new tables to the database, and continue on as if nothing had happened, leaving untouched all the extant data in the other tables.

Attempt 1
Per the process described in http://msdn.microsoft.com/en-US/data/jj554735:

I added to two new DbSets to the DbContext-derived class.  When I attempted to run the program, I got the following exception:  "The model backing the 'DrillContext' context has changed since the database was created. Consider using Code First Migrations to update the database".  This is what I expected to happen, since I hadn't yet enabled migrations.

In the Package Management Console I then issued the command:

    Enable-Migrations -EnableAutomaticMigrations.

The system Created a Migrations directory with a Configuration.cs file in it.

I then ran the "Update-Database" command, and got the message "No pending code-based migrations".  I checked the live database file in the working directory, and it was unchanged.  There was, however, a new database file with the same name in the "Program Files/Microsoft Visual Studio 10.0/Common7/IDE" directory that has the same name and is an empty database with all the appropriate tables, including the two tables corresponding to the two new DbSets in the DbContext-derived class, and a new table named _MigrationHistory, which has a single entry in it.  All the other tables are empty, whereas in the live database file, all those tables are populated.


Attempt 2, which uses an "InitialMigration":
Per http://www.ladislavmrnka.com/2012/03/ef-4-3-migrations-and-existing-database/

To prepare for the second attempt I restored the program's original state (i.e., before I had attempted the migrations) by doing 3 things.  I removed the 2 new DbSets from the DbContext-derived class.  I deleted the Migrations directory (and the enclosed Configuration.cs file), and deleted the useless database file (.sdf file) in the ".../Common7/IDE" directory.

- I then executed "Enable-Migrations -EnableAutomaticMigrations", which created the Migrations directory and the Configurations.cs file.

- I then executed the "Add-Migration InitialMigration -IgnoreChanges" command, which added an "201208281905525_InitialMigration.cs" file to the Migrations directory.

- Then, per the directions on the cited web page, I modified the Up method in the file so that the InitialMigration class became the following:

    public partial class InitialMigration : DbMigration
    {
        public override void Up()
        {
            Sql("DROP TABLE EdmMetadata");
        }

        public override void Down()
        {
        }
    }

- I then executed the "Update-Database" command, and got the message "The specified table does not exist."  I checked the live database in the working directory, and found that there is indeed an EdmMetadata table in the database.  I then checked the ".../Common7/IDE" directory and found that the new database that had been created there did NOT have the table.

So all "migration" activity is occurring on the ".../Common7/IDE" version of the file but not on the live database file in the working directory.

SOMEBODY in the EF team must know what's going on here.  How do I get it to work on the existing, live database file?

Thanks,

        

---

Dan Solis

Answer 5

Hi Dan,

If you add -verbose to the command(s) what connection string, etc., does the output indicate?

http://odetocode.com/Blogs/scott/archive/2012/08/14/a-troubleshooting-guide-for-entity-framework-connections-amp-migrations.aspx

Thanks,

Cathy Miller

Answer 6

Hi Cathy,

It produces the following lines:

    ...

    Target database is: 'VirtualDrillDB.sdf' (DataSource: VirtualDrillDB.sdf, Provider: System.Data.SqlServerCe.4.0, Origin: Configuration).
    No pending code-based migrations.

    ...

The DBContext-derived class looks like the following: 

public class DrillContext : DbContext
    {
        // Data about the setup
        public DbSet<DataWellJob>               WellJobs                    { get; set; }
        public DbSet<DataWellPath>              WellPaths                   { get; set; }
        ...

        // New classes
        public DbSet<DataGravity> DataGravity { get; set; }
        public DbSet<DataMagnetic> DataMagnetic { get; set; }

        public DrillContext()
            : base("name=DrillContext")
        {
        }
    }

Thanks,

Dan

Answer 7

Hi,

Could you post this issue to our Connect site: http://connect.microsoft.com?

In the meantime, if you specify |DataDirectory| in the connection string as follows, it will use the project folder for its appdomain root.

<add name="BlogContext" providerName="System.Data.SqlServerCe.4.0" connectionString="Data Source=|DataDirectory|\..\..\MyData.sdf"/>

Thanks,

Cathy