EF Core：如何在新数据库连接打开时执行SQL脚本

下面提供几种简洁的实现方式，帮你在每次新连接创建时执行指定SQL语句：

方案1：使用连接拦截器（推荐） #

EF Core提供了IDbConnectionInterceptor接口，能精准拦截连接打开的事件，这是最可靠的方式：

1. 定义拦截器类：

public class ConnectionCollationInterceptor : IDbConnectionInterceptor
{
    public async ValueTask<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection, 
        ConnectionEventData eventData, 
        InterceptionResult result, 
        CancellationToken cancellationToken = default)
    {
        if (connection.State != ConnectionState.Open)
        {
            await connection.OpenAsync(cancellationToken);
            using var initCommand = connection.CreateCommand();
            initCommand.CommandText = "set collation_connection = 'utf8mb4_unicode_ci'";
            await initCommand.ExecuteNonQueryAsync(cancellationToken);
        }
        return result;
    }

    public InterceptionResult ConnectionOpening(
        DbConnection connection, 
        ConnectionEventData eventData, 
        InterceptionResult result)
    {
        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
            using var initCommand = connection.CreateCommand();
            initCommand.CommandText = "set collation_connection = 'utf8mb4_unicode_ci'";
            initCommand.ExecuteNonQuery();
        }
        return result;
    }
}

2. 在DbContext的OnConfiguring中注册拦截器：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseMySQL("你的数据库连接字符串") // 替换为实际使用的数据库提供器（如UseSqlServer）
        .AddInterceptors(new ConnectionCollationInterceptor());
}

方案2：在DbContext中手动检查连接状态 #

如果只针对查询或保存操作触发初始化，可以在重写的SaveChanges或查询方法中添加逻辑，但这种方式覆盖场景有限：

public class YourDbContext : DbContext
{
    private bool _hasInitializedConnection;

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        await InitializeConnection(cancellationToken);
        return await base.SaveChangesAsync(cancellationToken);
    }

    public override int SaveChanges()
    {
        InitializeConnection();
        return base.SaveChanges();
    }

    private async Task InitializeConnection(CancellationToken cancellationToken)
    {
        if (_hasInitializedConnection) return;
        
        var connection = Database.GetDbConnection();
        if (connection.State != ConnectionState.Open)
        {
            await connection.OpenAsync(cancellationToken);
        }
        using var command = connection.CreateCommand();
        command.CommandText = "set collation_connection = 'utf8mb4_unicode_ci'";
        await command.ExecuteNonQueryAsync(cancellationToken);
        _hasInitializedConnection = true;
    }

    private void InitializeConnection()
    {
        if (_hasInitializedConnection) return;
        
        var connection = Database.GetDbConnection();
        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }
        using var command = connection.CreateCommand();
        command.CommandText = "set collation_connection = 'utf8mb4_unicode_ci'";
        command.ExecuteNonQuery();
        _hasInitializedConnection = true;
    }
}

方案3：通过连接字符串指定（仅部分数据库支持） #

部分数据库允许在连接字符串中直接配置排序规则，比如MySQL可以尝试添加collation=utf8mb4_unicode_ci参数，但这种方式的兼容性依赖数据库提供器，不一定能覆盖所有连接场景。

内容的提问来源于stack exchange，提问作者Jacob Stamm