Entity Framework Code-First: How to manually update the database?

Question

I've build a little WPF demo app which uses EF Code-First to save its data in a SQL CE 4.0 DB. It works fine unless I remove a property from a model object. For example, if I remove "HosteBy" from this class.....

public class Dinner {     public int DinnerID { get; set; }     public string Title { get; set; }        public DateTime EventDate { get; set; }     public string Address { get; set; }     public string HostedBy { get; set; }      public virtual ICollection<RSVP> RSVPs { get; set; } } 

...it throws this exception:

The model backing the 'NerdDinners' context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.

The error persists even after removing the field "HosteBy" manually from the database. What am I missing here? Do I have to delete/truncate the db or is there another solution?

Answer 1

In the first scenario where you changed the Code First Model, before you went and modified the database manually, the answer is to open the (Nuget) Package Manager Console and type:

update-database -verbose 

Except - because in this case you are removing a column this will report that it's about to delete something, and it won't delete anything without you explicitly saying that's OK. So you type:

update-database -f -verbose 

Now this will delete the column you had in your Model. -verbose says to show you the SQL it runs. If you're scared of just letting it delete things and rather inspect the SQL before it runs, use:

update-database -f -script

That will instead dump the SQL out to a script you can look over, and run manually yourself. It includes a SQL line that updates EF Code First's own understanding of the database.

In the case where you went on and deleted the column in the database manually, you now have a more complex scenario on your hands. There is a table in the database created by Entity Framework, called EdmMetadata in older versions and _MigrationHistory in newer versions. That table contains a hash of the entire database that now does not match the database itself. Running migrations (update-database) is now going to break over and over because of the mismatch until you resolve it. The hash isn't human-friendly and won't help you. You can resolve it in multiple ways:

1. You can add a Manual Migration with add-migration <name>. In this case I might name it with add-migration DeletePurchaseColumn or whatever the name of the column was. In the generated C# Schema Migrations code, in the Up() method, comment out the DeleteColumn line handling the column you already deleted. When you next run update-database, the code will: look at the hash, look at the list of Manual Migrations, see there's a newer Manual Migration than the state of the database, run Automatic Migrations up to the generated hash stored in the Manual Migration C# code, run the Manual Migration, then update the hash in the database. If it doesn't get any SQL errors, anyway. This is the most common way I resolve this problem.

2. You can run manual SQL to return the DB to the way Entity Framework expects (the way it was before you manually modified it, which brings it back in line with the hash) by inspecting what you had before and what your db currently looks like. In this case, you'd add the column back, so EF can be the one to delete it.

3. If you're very far away from what the hash is and what the database actually looks like, you should consider starting Migrations over by just wiping out any Manual Migrations in code and the EF version history table in the database, and starting over with enable-migrations.

Trouble

You can still run into trouble, for example if you have an arrangement in the db schema that is hard for EF to recognize, represent or match. Self-referencing tables can cause trouble, as can anything even slightly interesting about Primary Keys - for example sharing a key between tables requires careful C# work to match the code to the schema.

The simplest way out is to back up the database, try a series of add-migration Manual Migrations, and zen the results. For example, if there's a column in the database that's bugging EF like crazy, for example with a data type that isn't well-supported or a complicated key-sharing arrangement, but, it just doesn't matter much because you won't be accessing it anyway, you can get it into the hash and then stop thinking about it by generating a Manual Migration with add-migration, commenting out the AddColumn code (or Rename or Update) for that column, and moving on. The hash generated in the Manual Migration will capture EF's good-enough impression of the column, and you'll be able to get on with your work. If you ever do access that column from code though, there's a reasonable chance EF will explode. So, you should add a comment to the column with that warning.

If none of the above works, you are now in the ugliest part of Entity Framework Code First. You need to eliminate the hash table and reverse engineer the db into code files.

The good news on the second step, reverse engineering the db into code, is that Microsoft has released a tool into beta that will do this for you.

Walk-through of reverse-engineering a db, and EF Power Tools

You can skip many of the first steps there since they're just setting up a DB and adding some nonsense to it so they can demonstrate what you need to do: Reverse Engineer a db.