Thursday, April 14, 2016

.Net: Getting an Existing Code-First Solution Running Against a Rebuilt Database

Most of my posts are linux related, since I run that at home and encounter problems to solve due to being a relative newcomer. Occasionally, though, I run into something at work worth posting about (I'm a .Net developer).

I ran into a problem pointing an existing Code-First solution to a Dev database instance that I had restored from backup. I spent quite a bit of time working through all of the various issues that this situation caused, so hopefully the solutions I found will help someone else down the road. You may find that you only have some of these issues, depending on how the solution you're dealing with is configured.

The main problem I had was, after I had switched the connection strings in the project over to my new DB instance, the site threw an error: "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." In order to fix this, though, it turned out I needed to do a few things first - the dreaded Yak Shaving. Down the rabbit-hole we go!

So the goal is to get your database synchronized. In my case, I had a very recent production backup, so I knew there were very few, if any, actual differences between the database and the existing model in the project. To do this, you'll need to be able to run some entity framework commands in the package manger console. You might be getting an error trying to run "update-database -script" as I was; or you might have already discovered that isn't the first command you need to run and have been getting an error running the "add-migration" or "enable-migration" commands. The error that I received was: "The term 'Update-Database' is not recognized as the name of a cmdlet, function, script file, or operable program." The root cause turned out to be an error in packages.config; one of the files was listed twice. Here are the steps I used to fix it:

  1. Open packages.config. Simply use CTRL + , and type packages into the prompt that comes up. It'll find the file immediately. (This is equivalent to CTRL + SHIFT + r in eclipse).
  2. Comment out any duplicate entries (CTRL + k + c) and save your changes.
  3. Open the package manager console. If you don't see it as one of the minimized tabs in VS, use View->Other Windows->Package Manager Console.
  4. Type enable-migrations and hit enter.
  5. If you get a message saying migrations are enabled or migrations are already enabled, you're good. If you still get the "not recognized as cmdlet" message, keep going.
    1. Check the version of entity framework listed in packages.config. If it is not listed, check the version of the .dll in your references folder.
    2. In the package manager console, type "install-package EntityFramework -version 5.0.0.0" (or whatever version you need) and hit enter.
    3. You should get a message that either the package has been installed successfully or that it was already installed.
    4. Restart Visual Studio.
Now when I ran the commands, I got a different error. I always chalk that up as a win. I find that as long as I keep getting new errors, I'm probably getting closer to the final solution. In this case, it was "Could not load file or assembly EntityFramework .. or one of its dependencies". It took some real digging to find the solution to this one. In this case, it is to copy the contents of the EntityFramework NuGet package into an alternate location where Visual Studio 2015 expects it.
  1. In your project, open your references node and right click on EntityFramework. Select Properties.
  2. Copy the Path item, excluding the EntityFramework.dll portion.
  3. Open windows explorer and paste the path into the location bar. No location bar? Paste the path into IE instead, it'll bring up windows explorer to the correct location.
  4. Use the location bar or back directory arrow to go all the way back to the packages folder.
  5. Right click and copy the EntityFramework folder.
  6. Open a windows explorer window to C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\CommonExtensions\Microsoft\EFTools
  7. Create a new folder in this location called NuGet Packages.
  8. Inside the NuGet Packages folder you just created, paste the EntityFramework folder.

Once I'd done that, the entity framework commands were available to me in package manager, and they actually worked! Now I needed to actually do the work of synchronizing the database. This turns out to be a relatively straightforward process:
  1. Open the package manager console if it isn't already open. See step 3 above if you don't know where to find it.
  2. If you're not sure if migrations are enabled, run the command: "enable-migrations".
  3. Assuming that was successful or the project was already set that way, you'll want to add a migration. You'll need to give it a name, say ProjectName001. Run the command: "add-migration ProjectName001".
  4. This should have created a .cs file containing what your project thinks the differences are.
  5. Now, rather than letting the machine try to make the migration automatically, you can create a SQL script that you can adjust by hand. If you have experience with SQL, I recommend this route.
  6. Run the command: "update-database -script". This will generate a SQL script that will then be displayed in Visual Studio.
  7. Save the SQL script, and look through it and see if it's doing anything dumb. In my case, the script wanted to add several columns that already existed, as well as drop a number of useful indexes and foreign keys. I commented this code out, which ended up leaving just one line, the one that starts with "INSERT INTO [__MigrationHistory]"
  8. Run your adjusted SQL script against your database instance. I used SQL server management studio, because old habits are hard to break, but you can run SQL inside Visual Studio if you want.
    1. Click the connect icon (server icon thing) at the top of the window and enter the name of your database instance. Click the connect button on the dialog.
    2. Execute your script (CTRL + SHIFT + E)
The key thing is that now I had a new record in the __MigrationHistory table that will allow the Code First solution to run! Since my database sufficiently matched, at this point my solution ran just fine.

Hopefully this helps someone else, I know this cost me significant time to research and fix.

Useful links that helped me construct this guide:
http://stackoverflow.com/questions/9674983/the-term-update-database-is-not-recognized-as-the-name-of-a-cmdlet
https://github.com/NuGet/NuGetGallery/issues/2592
http://stackoverflow.com/questions/20968520/entity-framework-code-first-migration-fails-with-update-database-forces-unnecc
https://msdn.microsoft.com/en-us/data/dn579398.aspx#option1