Can EF Code First work with LocalDB in a ClickOnce application?

Question

So, I'm trying out EF Code First, so that I can have the code drive updates to the database. I'm working on a ClickOnce app using LocalDB, so figured this may be the best solution for me, since otherwise changes to the MDF file will cause it to be overwritten on the client when deployed, thus losing everything entered before.

However, I'm now having my fair share of all new problems around Code First Migrations. I've followed through a Code First Migrations on MSDN, and I've managed to get the initial Configuration created, as well as the initial database creation.

The problems begin when I try to make my first actual migration. I added one single field to one of my models, and tried to make an explicit migration to handle that schema change for the next time I publish. Well...

PM> Add-Migration AddIsPercentField

Unable to generate an explicit migration because the following explicit migrations are pending: [201601052011180_InitialCreate]. Apply the pending explicit migrations before attempting to generate a new explicit migration.

Ok... I'll run update and try again:

PM> Update-Database

Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201601052011180_InitialCreate].
Applying explicit migration: 201601052011180_InitialCreate.
Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.
You can use the Add-Migration command to write the pending model changes to a code-based migration.

PM> Add-Migration AddIsPercentField

Unable to generate an explicit migration because the following explicit migrations are pending: [201601052011180_InitialCreate]. Apply the pending explicit migrations before attempting to generate a new explicit migration.

That's familiar, as that's the error (blatant lie?) it just told me earlier. Well, maybe if I undo my changes and update again, it will move to a valid state:

PM> Update-Database

Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201601052011180_InitialCreate].
Applying explicit migration: 201601052011180_InitialCreate.
Running Seed method.

Ok, no warning this time. Should be golden. Field added back, project rebuilt. Here we go:

PM> Add-Migration AddIsPercentField

Unable to generate an explicit migration because the following explicit migrations are pending: [201601052011180_InitialCreate]. Apply the pending explicit migrations before attempting to generate a new explicit migration.

So... is there actually a working way to generate explicit migrations for any changes beyond the first?

EDIT: I made some forward progress on this, I believe. I did notice that the __MigrationHistory table was not generated in my .mdf after running Update-Database, even though it said everything completed just fine. I believe the issue is actually around how the local database works within the application. The connection string references AttachDbFilename=|DataDirectory|. What I think is going on is that it is deploying the .mdf temporarily, updating that temporary deployment, thus ultimately not committing the changes.

I'm working on a solution I have in mind, which is to have migrations work against a copy of the blank .mdf put in a static location, so that the static .mdf will be used to track and determine changes, while the blank .mdf will be what goes out to clients with the deployment.

Answer 1

I found that the root of my problem was that the console commands were not actually able to make changes, thus track migrations, to my data file. This was due to the connection string to the data file referencing a deployed location, so the file being updated was merely temporary.

This was in part a good thing, because the whole point of using Code First Migrations in my project was to avoid a hash signature change to my .mdf (which should have simply remained blank, as a placeholder) when publishing, so that the data from previous versions would never be overridden and discarded. However, that also introduced the obvious (in retrospect) problem that EF could not track changes due to there never being a __MigrationHistory table.

The solution upon which I arrived was to have two .mdf files. The blank one, for deployments, and a second one, to which I would interact with Code First Migrations. So, I have the initial MyData.mdf of Build Action Content, and a second MyDataDesignTime.mdf of Build Action None. (The "Design Time" migration database shouldn't be deployed.)

Using this approach, I found that I could now work successfully with migrations, calling Update-Database and Add-Migration, making sure to pass the -ConnectionString parameter with AttachDbFilename pointed to the full path to my design time database.

Later, getting lazy to supply a long -ConnectionString parameter on every migration command, I added the design time path to my config connection strings, and updated my DbContext so that it uses the design time path initially, but which I would change at the beginning of run-time to use my actual target data file:

public partial class MyData : DbContext
{
    public const string DesignTimeConnection = "MyDataConnectionStringDesignTime";

    public static string ConnectionName { get; set; } = DesignTimeConnection;

    public MyData()
        : base("name=" + ConnectionName)
    {
    }

    ...
}

And at application initialization:

MyData.ConnectionName = "MyDataConnectionString";

This works, and it makes things simpler on me. However, the one minor issue I'm left with is that I have a full static path which applies only to my environment left in the app.config file. Not currently an issue, as I'm the only dev on this project, but it's a code smell that I'm not happy with. Is there some path variable that I can use, such that it still points to the actual design time data (not any temporary, deployed file), but does so relative to the active, open project?