清理旧的 Entity Framework Core 迁移的推荐方法 [英] Recommended way to clean old Entity Framework Core migrations

问题描述

在开发我们的应用程序一段时间后，我们已经积累了相当多的 EFCore 数据库迁移.由于 EFCore 为每次迁移添加了整个 db 模型的快照，因此这段代码加起来相当多.经过分析，我们大约 80% 的编译时间都花在了迁移上(编译 + Roslyn 分析器).

After developing our application for a while we've accumulated quite a bit of EFCore database migrations. Since EFCore adds a snapshot of the entire db model to every migration, this code adds up quite a bit. After analysis about 80% of our compile time is spend on the migrations (compiling + Roslyn analyzers).

所以是时候清理一些旧的迁移了！但最好的方法是什么?似乎没有任何官方指导...

So it's time to clean up some old migrations! But what's the best way to do this? There doesn't seem to be any official guidance on it...

我们不需要任何回滚(我们只向前滚)，所以这让事情变得更简单.我们确实需要支持从头开始创建数据库，并从最近几次迁移中更新数据库.

We don't need any rollbacks (we only roll forward), so that makes things more simple. We do need to support creating a database from scratch, and updating a database from the last few migrations.

我尝试过的:

1. 核选项似乎是删除所有迁移和模型快照，并创建一个新的初始迁移.虽然这很好，但似乎有点危险.使用这种方法，我们需要非常小心地确保数据库架构的每个部分都是代码模型的一部分.例如，我们遇到的一种边缘情况是 EFCore 尚不支持检查约束.所以我们在迁移中添加了一个检查约束，而不是在代码模型中.因此，在创建新的初始迁移时，检查约束不是其中的一部分.

1. The nuclear option seems to be to delete all migrations and the model snapshot, and creating a new initial migration. While this is fine, it seems a bit dangerous. With this approach we need to be very careful that every part of the database schema is part of the code model. One edge case we for example ran into is that EFCore doesn't support checked constraints yet. So we added a checked constraint in a migration, but not in the code model. So when creating a new initial migration, the checked constraint was not part of it.

作为一项实验，我尝试从所有旧迁移中删除模型快照，因为快照是导致编译时间过长的代码的 90%.我发现，EFCore 仅使用快照作为比较工具来进行新的迁移.删除快照后，旧的迁移在新数据库上运行时不再执行.

As an experiment, I've tried to delete the model snapshot from all old migrations, since the snapshots are 90% of the code which cause the long compile time. I figured out, that EFCore only uses the snapshot as a compare tool to make a new migration. After deleting the snapshot, the old migrations were however no longer executed when they ran on a fresh database.

那么有没有更好的方法来完成我想要的?

So is there any better way to accomplish what I want?

推荐答案

好的，自从提出这个问题我已经尝试了很多.

Okay, since asking this question I've experimented quite a bit with this.

目前看来，实现此目的的最佳方法是选项 1.选项 2 会好得多，但直到 此 EFCore 功能 已实现，对于我的用例而言，这并不是真正可行的(支持现有数据库并对其进行迁移，并支持空数据库).

It seems for now, the best way to accomplish this is option 1. Option 2 would be much better, but until this EFCore feature is implemented, it's not really doable for my use case (supporting existing dbs with migrations on them, and supporting empty dbs).

选项 1 也有一些我偶然发现的陷阱(可能更多是我没有发现的).所以我就是这样做的:

Option 1 also has a few pitfalls which I stumbled upon (maybe even more that I haven't stumbled upon). So this is how I did it:

创建一个新的初始迁移:

Create a new initial migration:

1. 确保您现有的所有迁移都已应用于您的数据库.我们将创建一个新的初始迁移，因此尚未应用的迁移将会丢失.
2. 删除旧的 EFCore 迁移文件和数据库快照文件.
3. 从数据库的当前状态创建一个新的初始迁移.(例如通过 dotnet ef 迁移添加 Initial-PostCleanup.)

此新迁移仅与新数据库兼容，因为它将创建所有表(如果任何表、约束等已经存在，则失败).所以现在我们要使这个迁移与现有数据库兼容:

This new migration is only compatible with new databases, since it will create all tables (and fail if any of the tables, constraints, etc. already exist). So now we're going to make this migration compatible with the existing database:

1. 通过 dotnet ef migrations script -o script.sql 为新的初始迁移创建 SQL 脚本.
2. 删除第一个事务(直到第一个 GO)，它创建了 __EFMigrationsHistory 表:

1. Create a SQL script for the new initial migration via dotnet ef migrations script -o script.sql.
2. Remove the first transaction (until the first GO), which creates the __EFMigrationsHistory table:

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;

GO

1. 删除在 __EFMigrationsHistory 表中插入新条目的最后一个事务:

1. Remove the last transaction, that inserts the new entry in the __EFMigrationsHistory table:

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20190704144924_Initial-PostCleanup', N'2.2.4-servicing-10062');

GO

1. 删除 GO 命令，因为我们将创建脚本放在 IF 语句中:
   将 GO 替换为空.
2. 现在打开您的迁移文件(C# 文件，而不是 sql 文件)并将 Up 方法替换为以下内容:

1. Remove GO commands, since we will put the create script in an IF statement:
   Replace GO with nothing.
2. Now open up your migration file (the C# file, not the sql file) and replace the Up method with the following:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
DECLARE @migrationsCount INT = (SELECT COUNT(*) FROM [dbo].[__EFMigrationsHistory])
IF @migrationsCount = 0
BEGIN
    % PASTE YOUR EDITED SQL SCRIPT HERE %
END
");
}

完成！现在一切正常！

一定要比较数据库架构和新数据库前后的数据.如果您的 EF 代码模型不属于新数据库的一部分，则不属于该部分的所有内容.

Be sure to compare the database schema, and data before and after for the new database. Everything that's not part if your EF Code model is not part of the new database.

这篇关于清理旧的 Entity Framework Core 迁移的推荐方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！