The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

如何在 EF Core 3.1 的 Code First 進行資料庫移轉時指定資料庫定序

📅 2020/08/08 01:38 📁 .NET Core, Entity Framework

最近在我的台灣 .NET 技術愛好者俱樂部臉書社團有人問到如何在 EF Core 3.1 的 Code First 進行資料庫移轉時指定資料庫定序 (Database Collation)。我自己也嘗試了一下搜尋，網路上確實都找不到相關資料，但我覺得「理論上」應該很容易辦到，因此自己實驗了一下，也研究出方法，這篇文章將分享我的實際作法。

認識定序 (Collations)

關於「定序」的說明，建議可以參考 Microsoft Docs 官網上的 定序和區分大小寫 這篇文章，上面已經寫得非常清楚，因此本文將不再贅述，請大家先看過這篇文章再回到本文繼續接著看。

注意：EF Core 5.0 才會正式加入指定資料庫定序功能。

實作過程詳細步驟

  • 建立新專案

    dotnet new console -n efdemo1
cd efdemo1

  • 安裝/更新 dotnet ef 全域工具

    dotnet tool update --global dotnet-ef

  • 加入 EF Core 必要套件

    dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

  • 建立 Models 資料夾

    mkdir Models

  • 安裝/更新 dotnet ignore 全域工具

    dotnet tool update --global dotnet-ignore

  • 下載 .gitignore 的 VisualStudio 範本

    dotnet ignore get -n VisualStudio

  • 初始化 Git 本地儲存庫

    git init && git add . && git commit -m "Initial commit"

  • 建立實體資料模型類別 (Models\Todo.cs)

    public class Todo
{
    public int Id { get; set; }
    public string Item { get; set; }
}

  • 建立 DbCommandInterceptor 類別 (Models\CreateDatabaseCollationInterceptor.cs)

    這邊我們會實作一個 CreateDatabaseCollationInterceptor 類別，主要用來攔截所有透過 EF Core 產生的 T-SQL 命令 (DB Commands)，並且藉此調整為符合專案需求的命令！

    using System.Data.Common;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore.Diagnostics;

public class CreateDatabaseCollationInterceptor : DbCommandInterceptor
{
    private readonly string _collation;

    public CreateDatabaseCollationInterceptor(string collation)
    {
        _collation = collation;
    }

    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        var pattern = @"^CREATE DATABASE (\[.*\])(.*)$";
        if (Regex.IsMatch(command.CommandText, pattern))
        {
            command.CommandText = Regex.Replace(command.CommandText, pattern, $"CREATE DATABASE $1 COLLATE {_collation} $2");
        }

        return result;
    }

    public override Task<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
    {
        var pattern = @"^CREATE DATABASE (\[.*\])(.*)$";
        if (Regex.IsMatch(command.CommandText, pattern))
        {
            command.CommandText = Regex.Replace(command.CommandText, pattern, $"CREATE DATABASE $1 COLLATE {_collation} $2");
        }

        return Task.FromResult(result);
    }
}

  • 建立實體內容類別 (Models\TodoContext.cs)

    using Microsoft.EntityFrameworkCore;

namespace efdemo1.Models
{
    public class TodoContext : DbContext
    {
        public TodoContext() { }
        public TodoContext(DbContextOptions<TodoContext> options) : base(options) { }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // 加入自訂的 DbCommandInterceptor 實作
            optionsBuilder.AddInterceptors(new CreateDatabaseCollationInterceptor("Chinese_Taiwan_Stroke_CI_AS"));

            // 設定預設連接字串
            optionsBuilder.UseSqlServer("Server=localhost;Initial Catalog=Todo1;User Id=sa;Password=Ver7CompleXPW");
        }

        public virtual DbSet<Todo> Todos { get; set; }
    }
}

  • 建立實體內容工廠類別 (Models\TodoContextFactory.cs)

    using Microsoft.EntityFrameworkCore.Design;

namespace efdemo1.Models
{
    public class TodoContextFactory : IDesignTimeDbContextFactory<TodoContext>
    {
        public TodoContext CreateDbContext(string[] args)
        {
            return new TodoContext();
        }
    }
}

    關於為何只有 主控台應用程式 (Console) 才需要建立 ___ContextFactory 實體內容工廠類別，可以參閱 Design-time DbContext Creation 文件說明。

  • 建立初始化資料庫移轉設定

    dotnet ef migrations add init

  • 更新資料庫（如果沒有資料庫，將會自動建立全新資料庫）

    這裡請務必設定 -v 參數，顯示執行過程中所有對資料庫執行的 T-SQL 命令：

    dotnet ef database update -v

    以下我特別列出所有執行記錄：

    G:\efdemo1>dotnet ef database update -v
Using project 'G:\efdemo1\efdemo1.csproj'.
Using startup project 'G:\efdemo1\efdemo1.csproj'.
Writing 'G:\efdemo1\obj\efdemo1.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\wakau\AppData\Local\Temp\tmp5E6B.tmp /verbosity:quiet /nologo G:\efdemo1\efdemo1.csproj
Writing 'G:\efdemo1\obj\efdemo1.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\wakau\AppData\Local\Temp\tmp6041.tmp /verbosity:quiet /nologo G:\efdemo1\efdemo1.csproj
Build started...
dotnet build G:\efdemo1\efdemo1.csproj /verbosity:quiet /nologo

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:00.88
Build succeeded.
dotnet exec --depsfile G:\efdemo1\bin\Debug\netcoreapp3.1\efdemo1.deps.json --additionalprobingpath C:\Users\wakau\.nuget\packages --runtimeconfig G:\efdemo1\bin\Debug\netcoreapp3.1\efdemo1.runtimeconfig.json C:\Users\wakau\.dotnet\tools\.store\dotnet-ef\3.1.6\dotnet-ef\3.1.6\tools\netcoreapp3.1\any\tools\netcoreapp2.0\any\ef.dll database update --assembly G:\efdemo1\bin\Debug\netcoreapp3.1\efdemo1.dll --startup-assembly G:\efdemo1\bin\Debug\netcoreapp3.1\efdemo1.dll --project-dir G:\efdemo1\ --language C# --working-dir G:\efdemo1 --verbose --root-namespace efdemo1
Using assembly 'efdemo1'.
Using startup assembly 'efdemo1'.
Using application base 'G:\efdemo1\bin\Debug\netcoreapp3.1'.
Using working directory 'G:\efdemo1'.
Using root namespace 'efdemo1'.
Using project directory 'G:\efdemo1\'.
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Found IDesignTimeDbContextFactory implementation 'TodoContextFactory'.
Found DbContext 'TodoContext'.
Finding application service provider...
Finding Microsoft.Extensions.Hosting service provider...
No static method 'CreateHostBuilder(string[])' was found on class 'Program'.
No application service provider was found.
Finding DbContext classes in the project...
Using DbContext factory 'TodoContextFactory'.
Using context 'TodoContext'.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Finding design-time services referenced by assembly 'efdemo1'.
No referenced design-time services were found.
Finding IDesignTimeServices implementations in assembly 'efdemo1'...
No design-time services were found.
Migrating using database 'Todo1' on server 'localhost'.
Opening connection to database 'Todo1' on server 'localhost'.
An error occurred using the connection to database 'Todo1' on server 'localhost'.
Opening connection to database 'Todo1' on server 'localhost'.
An error occurred using the connection to database 'Todo1' on server 'localhost'.
Opening connection to database 'master' on server 'localhost'.
Opened connection to database 'master' on server 'localhost'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (13ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='60']
CREATE DATABASE [Todo1];
Executed DbCommand (577ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
CREATE DATABASE [Todo1] COLLATE Chinese_Taiwan_Stroke_CI_AS ;
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='60']
IF SERVERPROPERTY('EngineEdition') <> 5
BEGIN
    ALTER DATABASE [Todo1] SET READ_COMMITTED_SNAPSHOT ON;
END;
Executed DbCommand (195ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
IF SERVERPROPERTY('EngineEdition') <> 5
BEGIN
    ALTER DATABASE [Todo1] SET READ_COMMITTED_SNAPSHOT ON;
END;
Closing connection to database 'master' on server 'localhost'.
Closed connection to database 'master' on server 'localhost'.
Opening connection to database 'Todo1' on server 'localhost'.
Opened connection to database 'Todo1' on server 'localhost'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Closing connection to database 'Todo1' on server 'localhost'.
Closed connection to database 'Todo1' on server 'localhost'.
DetectChanges starting for 'TodoContext'.
DetectChanges completed for 'TodoContext'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Opening connection to database 'Todo1' on server 'localhost'.
Opened connection to database 'Todo1' on server 'localhost'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [__EFMigrationsHistory] (
    [MigrationId] nvarchar(150) NOT NULL,
    [ProductVersion] nvarchar(32) NOT NULL,
    CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [__EFMigrationsHistory] (
    [MigrationId] nvarchar(150) NOT NULL,
    [ProductVersion] nvarchar(32) NOT NULL,
    CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
Closing connection to database 'Todo1' on server 'localhost'.
Closed connection to database 'Todo1' on server 'localhost'.
Opening connection to database 'Todo1' on server 'localhost'.
Opened connection to database 'Todo1' on server 'localhost'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT 1
Closing connection to database 'Todo1' on server 'localhost'.
Closed connection to database 'Todo1' on server 'localhost'.
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (0ms).
Opening connection to database 'Todo1' on server 'localhost'.
Opened connection to database 'Todo1' on server 'localhost'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Closing connection to database 'Todo1' on server 'localhost'.
Closed connection to database 'Todo1' on server 'localhost'.
Creating DbCommand for 'ExecuteReader'.
Created DbCommand for 'ExecuteReader' (0ms).
Opening connection to database 'Todo1' on server 'localhost'.
Opened connection to database 'Todo1' on server 'localhost'.
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
Executed DbCommand (15ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [MigrationId], [ProductVersion]
FROM [__EFMigrationsHistory]
ORDER BY [MigrationId];
A data reader was disposed.
Closing connection to database 'Todo1' on server 'localhost'.
Closed connection to database 'Todo1' on server 'localhost'.
Applying migration '20200807164016_init'.
Opening connection to database 'Todo1' on server 'localhost'.
Opened connection to database 'Todo1' on server 'localhost'.
Beginning transaction with isolation level 'Unspecified'.
Began transaction with isolation level 'ReadCommitted'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (11ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Todos] (
    [Id] int NOT NULL IDENTITY,
    [Item] nvarchar(max) NULL,
    CONSTRAINT [PK_Todos] PRIMARY KEY ([Id])
);
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [Todos] (
    [Id] int NOT NULL IDENTITY,
    [Item] nvarchar(max) NULL,
    CONSTRAINT [PK_Todos] PRIMARY KEY ([Id])
);
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20200807164016_init', N'3.1.6');
Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20200807164016_init', N'3.1.6');
Committing transaction.
Committing transaction.
Disposing transaction.
Closing connection to database 'Todo1' on server 'localhost'.
Closed connection to database 'Todo1' on server 'localhost'.
'TodoContext' disposed.
Done.

    你可以從 Opened connection to database 'master' on server 'localhost'. 這行開始看資料庫建立的過程，你應該可以看到 Executing DbCommand 這行下方，會先顯示出 EF Core 內建產生的 T-SQL 命令，然後過程中會被我 CreateDatabaseCollationInterceptor 類別中的 NonQueryExecuting() 方法微調過，所以你會在 Executed DbCommand 這行下方看到動態調整過的 T-SQL 語法，而實際傳到 SQL Server 執行的命令，就是這一段！我就是透過這個開發技巧，動態調整 T-SQL 命令的！

    Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (13ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='60']
CREATE DATABASE [Todo1];
Executed DbCommand (577ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
CREATE DATABASE [Todo1] COLLATE Chinese_Taiwan_Stroke_CI_AS ;

  • 檢查資料庫定序是否設定正確

    SELECT name, collation_name FROM sys.databases

    SELECT name, collation_name FROM sys.databases

    這裡特別需要知道的地方就是，SQL Server LocalDB 的執行個體定序是設定為 SQL_Latin1_General_CP1_CI_AS，而且無法變更！即便你用 CREATE DATABASE [Todo1] COLLATE Chinese_Taiwan_Stroke_CI_AS ; 這種語法去建立資料庫，該資料庫也不會真的套用 Chinese_Taiwan_Stroke_CI_AS 定序。如果你想實驗出結果，請使用其他 SQL Server 版本，或使用 Docker 執行 SQL Server on Linux 容器。

實作 DbCommandInterceptor 類別其實是相當進階的用法，如果程式有 Bug 的話，會影響整個應用程式的 EF Core 運作。也因為這種作法是 EF Core 內建的功能，因此這個技巧並不是只能套用在 SQL Server 而已，你想套用在 Oracle, MySQL, PostgreSQL, ... 都可以，非常強大！

關於資料庫移轉

有些人一定會想到，透過 dotnet ef migrations add init 命令產生的 Migrations\20200807164016_init.cs 檔案，不是可以自己客製 Up(MigrationBuilder migrationBuilder) 方法的內容嗎？

如果你試著在 Up() 方法的第一行加入以下命令程式：

migrationBuilder.Sql("ALTER DATABASE CURRENT COLLATE Chinese_Taiwan_Stroke_CI_AS");

注意：這裡的 ALTER DATABASE CURRENT 是 SQL Server 2012 之後才支援的語法，可以直接用 CURRENT 代表目前連線所指定的預設資料庫，不用在 T-SQL 裡面明確指定資料庫名稱！(相關討論)

你會在執行 dotnet ef database update -v 時遇到以下錯誤：

ALTER DATABASE statement not allowed within multi-statement transaction.

因為 ALTER DATABASE 陳述式是無法套用「交易處理」的！

不過，還好 migrationBuilder.Sql 方法有第二個 suppressTransaction 參數可用，可以將此 SQL 命令執行在預設的交易範圍之外，那就可以順利執行了！

migrationBuilder.Sql("ALTER DATABASE CURRENT COLLATE Chinese_Taiwan_Stroke_CI_AS", true);

這一招也是可以的，而且比實作 DbCommandInterceptor 還簡單，不過，魔鬼總在細節裡，這樣的寫法其實不是永遠都會成功！

我們來調整一下程式碼：

  1. 修改實體資料模型類別 (Models\Todo.cs):

    public class Todo
{
    public int Id { get; set; }
    public string Item { get; set; }
    // 新增一個 IsDeleted 屬性
    public bool IsDeleted { get; set; }
}

  2. 建立新的資料庫移轉 (Models\Todo.cs):

    dotnet ef migrations add Add_IsDeleted

  3. 更新資料庫

    dotnet ef database update -v

到目前為止，都不會有任何錯誤發生喔！

但是，我們現在將資料庫移除，並重新執行一次 dotnet ef database update -v 命令，這時你就會發現執行的過程會出現以下錯誤：

Resetting the connection results in a different state than the initial login. The login fails.
Login failed for user 'sa'.
Cannot continue the execution because the session is in the kill state.
A severe error occurred on the current command.  The results, if any, should be discarded.

是的，因為執行 ALTER DATABASE CURRENT COLLATE Chinese_Taiwan_Stroke_CI_AS 命令調整資料庫定序的關係，所有既有連線都會被強迫中斷，這會導致資料庫會有短暫的時間不能使用，因此當 DB Migration 進行到第二個資料庫移轉命令時，就會出現 Resetting the connection results in a different state than the initial login. The login fails. 這樣的錯誤出現。

其實這個問題並不大，因為第一份資料庫移轉已經套用成功，所以你只要重新再執行一次 dotnet ef database update -v 命令，就可以順利套用完成所有的資料庫移轉步驟！

範例程式參考

本文所有程式碼都放在以下 GitHub Repo 之中：

相關連結

留言評論