如何从Entity Framework噩梦中恢复-数据库已经具有名称相同的表 [英] How to recover from Entity Framework nightmare - database already has tables with the same name
问题描述
当更新数据库返回以下消息时，如何使EF恢复与代码同步，而不会丢失数据
How do you get EF back in Sync with code without losing data when Update-database returns the following message
错误消息:System.Data.SqlClient.SqlException(0x80131904):出现 在数据库中已经是名为"的对象.
Error Message: System.Data.SqlClient.SqlException (0x80131904): There is already an object named '' in the database.
推荐答案
我最初将此问题写成是一个自我回答的问题，因为我和一些问题一样挣扎了一段时间，但不幸的是，我的回答是已删除，我无法恢复.
I originally wrote this as a self-answering question as I had struggled with the proble for some time, as had a few colleagues, but unfortunately, my answer was deleted and I can't recover it.
由于我怀疑这种情况在人们尝试清理"旧迁移时可能会发生多次，所以我想我会按逐步说明对其进行记录.
Since it's a situation that I suspect can happen several times as people try to "clean up" old migrations, I thought I'd document it with step by step instructions.
我们所处情况的描述: 因为初始化脚本不完整，所以我们无法创建新的本地数据库；由于迁移脚本创建的表已经存在，因此我们无法将更新应用于生产数据库.而且，我们不想删除生产数据.
Description of the situation we found ourselves in: We couldn't can't create a new local database because the init script was incomplete, and couldn't apply updates to the production database because the migration scripts create tables that already exist. And, we didn't want to delete production data.
症状:无法运行 Update-Database ，因为它正在尝试运行创建脚本，并且数据库中已有具有相同表的表名称.
Symptom: Can't run Update-Database because it's trying to run the creation script and the database already has tables with the same name.
错误消息:System.Data.SqlClient.SqlException(0x80131904):出现 在数据库中已经是名为"的对象.
Error Message: System.Data.SqlClient.SqlException (0x80131904): There is already an object named '' in the database.
问题背景: 为了更详细地了解这一点，我建议您观看此处引用的两个视频: https://msdn.microsoft.com/en -us/library/dn481501(v = vs.113).aspx
Problem Background: To understand this in more detail, I'd recommend watching both videos referenced here: https://msdn.microsoft.com/en-us/library/dn481501(v=vs.113).aspx
总而言之，EF基于数据库中名为dbo .__ MigrationHistory的表来了解当前数据库与代码所处的位置.当查看迁移脚本时，它将尝试重新协调脚本的最后位置.如果不能，它只是尝试按顺序应用它们.这意味着，它可以返回到初始创建脚本，如果您查看UP命令的第一部分，它将是发生错误的表的CreeateTable.
To summarise, EF understands where the current database is at compared to where the code is at based on a table in the database called dbo.__MigrationHistory. When it looks at the Migration Scripts, it tries to reconsile where it was last at with the scripts. If it can't, it just tries to apply them in order. This means, it goes back to the initial creation script and if you look at the very first part in the UP command, it'll be the CreeateTable for the table that the error was occurring on.
解决方案:我们需要做的是诱使EF认为当前数据库是最新的，而由于生产数据库已经存在，因此不"应用这些CreateTable命令.设置生产数据库后，我们仍然还需要能够创建本地数据库.
Solution: What we need to do is to trick EF into thinking that the current database is up to date, while "not" applying these CreateTable commands since the production database already exists. Once production DB is set, we still need to be able to create local databases as well.
第1步:清理生产数据库 首先，对您的生产数据库进行备份.在SSMS中，右键单击数据库，选择任务>导出数据层应用程序..."，然后按照提示进行操作. 打开生产数据库并删除/删除dbo .__ MigrationHistory表.
Step 1: Production DB clean First, make a backup of your production db. In SSMS, Right-Click on the database, Select "Tasks > Export Data-tier application..." and follow the prompts. Open your production database and delete/drop the dbo.__MigrationHistory table.
第2步:清理本地环境 打开您的迁移文件夹并将其删除.我假设您可以根据需要从git中全部收回.
Step 2: Local environment clean Open your migrations folder and delete it. I'm assuming you can get this all back from git if necessary.
第3步:重新创建初始字母 在程序包管理器中，运行"Enable-Migrations"(如果您有多个上下文，EF将提示您使用-ContextTypeName). 运行添加迁移初始-详细".这将创建初始脚本，以基于当前代码从头开始创建数据库. 如果您在以前的Configuration.cs中进行了任何种子操作，则将其复制.
Step 3: Recreate Initial In the Package Manager, run "Enable-Migrations" (EF will prompt you to use -ContextTypeName if you have multiple contexts). Run "Add-Migration Initial -verbose". This will Create the initial script to create the database from scratch based on the current code. If you had any seed operations in the previous Configuration.cs, then copy that across.
第4步:欺骗EF 此时，如果我们运行 Update-Database ，我们将得到原始错误.因此，我们需要欺骗EF以使其认为是最新的，而无需运行这些命令.因此，进入刚刚创建的初始"迁移中的Up方法，并全部注释掉.
Step 4: Trick EF At this point, if we ran Update-Database, we'd be getting the original error. So, we need to trick EF into thinking that it's up to date, without running these commands. So, go into the Up method in the Initial migration you just created and comment it all out.
第5步:更新数据库 由于没有代码要在Up进程上执行，因此EF将使用正确的条目创建dbo .__ MigrationHistory表，以表明它正确运行了该脚本.去看看是否喜欢. 现在，取消注释该代码并保存. 如果要检查EF是否认为它是最新的，可以再次运行 更新数据库 .它不会使用所有CreateTable命令运行向上"步骤，因为它认为它已经完成了此操作.
Step 5: Update-Database With no code to execute on the Up process, EF will create the dbo.__MigrationHistory table with the correct entry to say that it ran this script correctly. Go and check it out if you like. Now, uncomment that code and save. You can run Update-Database again if you want to check that EF thinks its up to date. It won't run the Up step with all of the CreateTable commands because it thinks it's already done this.
第6步:确认EF实际上是最新的 如果您有尚未应用迁移的代码，这就是我所做的...
Step 6: Confirm EF is ACTUALLY up to date If you had code that hadn't yet had migrations applied to it, this is what I did...
运行添加迁移缺少的迁移" 这实际上将创建一个空脚本.因为代码已经存在，所以实际上在初始迁移脚本中有正确的命令可以创建这些表，因此我只是将CreateTable和等效的drop命令剪切为Up和Down方法.
Run "Add-Migration MissingMigrations" This will create practically an empty script. Because the code was there already, there was actually the correct commands to create these tables in the initial migration script, so I just cut the CreateTable and equivalent drop commands into the Up and Down methods.
现在，再次运行 Update-Database ，并观察它执行新的迁移脚本，并在数据库中创建适当的表.
Now, run Update-Database again and watch it execute your new migration script, creating the appropriate tables in the database.
第7步:重新确认并确认. 构建，测试，运行.确保一切都在运行，然后提交更改.
Step 7: Re-confirm and commit. Build, test, run. Ensure that everything is running then commit the changes.
第8步:让团队其他成员知道如何进行. 当下一个人员更新时，由于之前运行的脚本不存在，EF将不知道它受到了什么打击.但是，假设可以销毁并重新创建本地数据库，那么一切都很好.他们将需要删除其本地数据库，然后再次从EF添加创建数据库.如果他们有本地更改并且有待迁移，我建议他们在master上再次创建数据库，切换到功能分支，然后从头开始重新创建那些迁移脚本.
Step 8: Let the rest of your team know how to proceed. When the next person updates, EF won't know what hit it given that the scripts it had run before don't exist. But, assuming that local databases can be blown away and re-created, this is all good. They will need to drop their local database and add create it from EF again. If they had local changes and pending migrations, I'd recommend they create their DB again on master, switch to their feature branch and re-create those migration scripts from scratch.
这篇关于如何从Entity Framework噩梦中恢复-数据库已经具有名称相同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
