vb.net 使用代码优先更新数据库时出错：“数据库中已经有一个名为‘某物’的对象。”

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

提问by Nurul

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 .

我正在使用 Visual Studio 2015 中的 Code-First 进行数据库迁移。一路上，我已经完成了直到 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

在我的 DbContext 构造函数中设置数据库初始值设定项，因为我之前错过了这一步。在那之后，我执行了

"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.

此操作需要连接到“master”数据库。无法创建到“主”数据库的连接，因为原始数据库连接已打开并且凭据已从连接字符串中删除。提供一个未打开的连接。

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.

数据库中已经有一个名为“something”的对象。

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.

我已经尝试了两者，但它仍然显示相同的错误。

回答by Diana

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

为了能够解决您的问题，您应该了解 EF 如何处理连接字符串以及迁移如何工作。

How EF deals with connection strings: Usually your DbContexthas a parameterless constructor, which calls its base class constructor with a hard-coded connection string name. Your project app.configor web.configfile should contain a connectionStringssection which defines a connection string with that name. This is the default connection string used in your projectwhen you don't explicitly provide a connection string parameter to the Package Manager Console commands.

EF 如何处理连接字符串：通常您DbContext有一个无参数构造函数，它使用硬编码的连接字符串名称调用其基类构造函数。您的项目app.config或web.config文件应包含connectionStrings定义具有该名称的连接字符串的部分。当您没有向包管理器控制台命令明确提供连接字符串参数时，这是项目中使用的默认连接字符串。

Some example code with a connection string name MyConnectionStringName:

一些带有连接字符串名称的示例代码MyConnectionStringName：

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

And in your .configfile:

在你的.config文件中：

<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-Databasein the Package Manager Console like this:

如果您不使用这种方法，您仍然可以手动提供正确的连接字符串作为Update-Database包管理器控制台中的参数，如下所示：

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 .configfile:

您还可以使用您在.config文件中定义的任何连接字符串名称：

Update-Database -ConnectionStringName MyConnectionStringName

And now about how migrations work: Migrations are code files. Every time you run Add-Migrationa code file is generated/updated, usually in a folder called Migrationsinside 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.

现在关于迁移的工作原理：迁移是代码文件。每次运行时Add-Migration都会生成/更新代码文件，通常在Migrations项目内部调用的文件夹中。迁移文件的名称由其生成的时间戳和运行时使用的名称组成Add-Migration。您可以检查这些文件的内容并查看运行Add-Migration. 您还可以在生成后修改它们，并添加您自己的代码，但目前您不需要这样做。

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

迁移旨在是增量的。您从Initial迁移开始，每次更改模型代码时都会生成一个新的迁移文件。数据库包含一个名为的表__MigrationsHistory，用于跟踪数据库中已运行的迁移。

Every single migration has a method Upand a method Down. When you run Update-Databasethere are always two implicit parameters: SourceMigrationand TargetMigration. EF incrementally applies the Upmethods of all the migrations between SourceMigrationand TargetMigration(or the Downmethods if you are downgrading your database). The default scenario when you don't specify the SourceMigrationand TargetMigrationparameters is that SourceMigrationis the last migration applied to the database and TargetMigrationis the last of the pending ones. EF determines those parameters by querying the __MigrationsHistorytable 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.

每个迁移都有一个方法Up和方法Down。当您运行时Update-Database，总是有两个隐式参数：SourceMigration和TargetMigration。EF 逐步应用和Up之间所有迁移的方法（或如果您要降级数据库的方法）。未指定和参数时的默认情况是，这是应用于数据库的最后一次迁移，并且是未决迁移中的最后一次。EF 通过查询项目默认数据库的表来确定这些参数，因此如果该数据库处于不一致状态，则可能会错误地生成迁移。我认为这就是导致您出现问题的原因。SourceMigrationTargetMigrationDownSourceMigrationTargetMigrationSourceMigrationTargetMigration__MigrationsHistory

So every time you run Update-DatabaseEF looks into the __MigrationsHistorytable 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.

因此，每次运行Update-DatabaseEF 时，都会查看__MigrationsHistory表以了解必须运行哪些迁移，具体取决于数据库的状态，并且在执行迁移的 SQL 后，将为每个应用的迁移在该表中插入一条新记录。

It seems that at some point your database __MigrationsHistorycontents got messed up. It happens when running Update-Databaseand Add-Migrationswithout following the right order and using the -forceparameter.

似乎在某些时候您的数据库__MigrationsHistory内容搞砸了。它发生在运行Update-Database并且Add-Migrations没有遵循正确的顺序和使用-force参数时。

My advice to fix your problems: Start from scratch: delete your database, delete your migration files, generate a new clean Initialmigration 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-Databaseonce.

我解决问题的建议：从头开始：删除数据库，删除迁移文件，使用 生成新的干净Initial迁移，使用Add-Migration Initial仅运行一次Update-Database。从那时起，每次更改模型代码时，都会生成一个新的增量迁移Add Migration YourNewMigrationName，每次使用不同的名称，并通过运行Update-Database一次来应用新迁移。

Note: Instead of the incremental migrations, if you have a good enough knowledge about how migrations work you can also use only one Initialmigration and update it whenever your model code changes, by executing Add-Migrations Initial -force. The -forceparameter makes sure that instead of generating a new migration file your existing Initialmigration 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).

注意：除了增量迁移，如果您对迁移的工作原理有足够的了解，您也可以只使用一个Initial迁移，并在模型代码更改时通过执行Add-Migrations Initial -force. 该-force参数确保您现有的Initial迁移文件将被覆盖，而不是生成新的迁移文件。这种方法在开发阶段很方便，但在生产中通常不是一个好方法，因为您可能希望每次部署新版本的代码时增量运行数据库更新（您可能无法删除数据库并再次创建它，并且您还需要维护您的数据并确保在更新数据库时不会丢失数据）。

Migrations are code files which are generated by EF to create and update your database. When you run Update-Databasethe 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-Databaseexecution.

迁移是由 EF 生成的用于创建和更新数据库的代码文件。当您运行时Update-Database，迁移将转换为针对您的数据库执行的 SQL。如果您想查看为特定迁移生成的确切 SQL，您可以运行Update-Database -Script -SourceMigration SomeMigration -TargetMigration SomeOtherMigration. 此命令不会修改数据库，只是生成并显示将在实际Update-Database执行中应用的 SQL 。

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

可以在此处找到有关生成和运行迁移的更多信息。

Good luck!

祝你好运！