Error when Update-Database using Code-First: "There is already an object named 'something' in the database."

Question

I'm doing database migration using Code-First in Visual Studio 2015. Along the way, i've done the migration steps up till Add-Migration .

After Add-Migration, i've added this line of code

Database.SetInitializer(New MigrateDatabaseToLatestVersion(Of DbContext1, Migrations.Configuration))

in my DbContext constructor to set up Database Initializer since i missed this step previously. After that, i executed

"Add-Migration initial -Force"

in the Package Manager Console because i fear that this part is needed in the Add-Migration process. Then, i directly executed

"Update-Database"

The problem is after i did this, an error comes out

This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection.

ADDED

After i restarted my computer, the above error didn't come out anymore when i executed 'Update-Database'. Instead, another error came out:

There is already an object named 'something' in the database.

I saw an answer thread suggesting to execute

Add-Migration Initial -IgnoreChanges

followed by

Update-Database -verbose

I've tried both but it still shows the same error.

Answer 1

To be able to fix your problems you should understand how EF deals with connection strings and how migrations work.

How EF deals with connection strings: Usually your DbContext has a parameterless constructor, which calls its base class constructor with a hard-coded connection string name. Your project app.config or web.config file should contain a connectionStrings section which defines a connection string with that name. This is the default connection string used in your project when you don't explicitly provide a connection string parameter to the Package Manager Console commands.

Some example code with a connection string name MyConnectionStringName:

public class MyDbContext : DbContext
{
    public MyDbContext() : base("MyConnectionStringName") { ... }
    ...
}

And in your .config file:

<configuration>
  ...
  <connectionStrings>
    <add name="MyConnectionStringName" connectionString="..." />
  </connectionStrings>
</configuration>

If you don't use that approach you still can manually provide the right connection string as a parameter to Update-Database in the Package Manager Console like this:

Update-Database -ConnectionString <your connection string here> -ConnectionProviderName System.Data.SqlClient

You can also use any connection string name that you have defined in your .config file:

Update-Database -ConnectionStringName MyConnectionStringName

And now about how migrations work: Migrations are code files. Every time you run Add-Migration a code file is generated/updated, usually in a folder called Migrations inside your project. The name of a migration file is composed with a timestamp of its generation concatenated with the name used when running Add-Migration. You can check the contents of those files and see the effects of running Add-Migration. You can also modify them once generated, and add your own code, though you should not need to do that by the moment.

Migrations are intended to be incremental. You start with an Initial migration, and every time you change your model code you generate a new migration file. The database contains a table named __MigrationsHistory that keeps trace of which migrations have been run in your database.

Every single migration has a method Up and a method Down. When you run Update-Database there are always two implicit parameters: SourceMigration and TargetMigration. EF incrementally applies the Up methods of all the migrations between SourceMigration and TargetMigration (or the Down methods if you are downgrading your database). The default scenario when you don't specify the SourceMigration and TargetMigration parameters is that SourceMigration is the last migration applied to the database and TargetMigration is the last of the pending ones. EF determines those parameters by querying the __MigrationsHistory table of the default database of your project, so if that database is not in a consistent state your migrations can be generated incorrectly. I think this is what is causing your problems.

So every time you run Update-Database EF looks into the __MigrationsHistory table to know which migrations must be run, depending on the state of your database, and after executing the SQL of the migrations a new record is inserted in that table for each applied migration.

It seems that at some point your database __MigrationsHistory contents got messed up. It happens when running Update-Database and Add-Migrations without following the right order and using the -force parameter.

My advice to fix your problems: Start from scratch: delete your database, delete your migration files, generate a new clean Initial migration with Add-Migration Initial, run it only once with Update-Database. From that point on, every time you change your model code you generate a new incremental migration with Add Migration YourNewMigrationName, using different names each time, and apply the new migration by running Update-Database once.

Note: Instead of the incremental migrations, if you have a good enough knowledge about how migrations work you can also use only one Initial migration and update it whenever your model code changes, by executing Add-Migrations Initial -force. The -force parameter makes sure that instead of generating a new migration file your existing Initial migration file will be overwritten. This approach comes handy at development phase, but in production usually it is not a good approach, as you probably want to run your database updates incrementally every time you deploy a new version of your code (you will probably not be able to drop your database and create it again, and also you will need to maintain your data and make sure you don't have data loss when updating your database).

Migrations are code files which are generated by EF to create and update your database. When you run Update-Database the migrations are translated to SQL which is executed against your database. If you want to see the exact SQL being generated for a specific migration you can run Update-Database -Script -SourceMigration SomeMigration -TargetMigration SomeOtherMigration. This command does not modify the database, just generates and shows the SQL that would be applied in the real Update-Database execution.

More info about generating and running migrations can be found here.

Good luck!

Update: This answer applies to EF6. Be aware that things have changed a little after EF6, with EF Core. The basic concepts explained in this answer are the same, but the Package Manager Console commands have changed, and also the way EF deals with database context snapshots and migrations.