如何让EF Core日志生成的SQL可直接在SQL Server执行（Mac环境）

方法一：修改EF Core配置，输出带实际参数值的SQL #

通过自定义DbCommandInterceptor拦截数据库命令，在执行前将SQL语句中的参数替换为实际值，直接输出可执行的完整SQL：

public class SqlLoggingInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        var sql = command.CommandText;
        foreach (DbParameter param in command.Parameters)
        {
            string value;
            if (param.DbType == DbType.String || param.DbType == DbType.DateTime)
            {
                value = $"'{param.Value.ToString().Replace("'", "''")}'";
            }
            else if (param.DbType == DbType.Boolean)
            {
                value = (bool)param.Value ? "CAST(1 AS bit)" : "CAST(0 AS bit)";
            }
            else
            {
                value = param.Value.ToString();
            }
            sql = sql.Replace(param.ParameterName, value);
        }
        Console.WriteLine($"Executable SQL:\n{sql}");
        return base.ReaderExecuting(command, eventData, result);
    }
}

在DbContext配置中注册该拦截器：

public static void Configure(DbContextOptionsBuilder<LegalRegTechDbContext> builder, string connectionString, int commandTimeout)
{
    builder.UseSqlServer(connectionString)
           .AddInterceptors(new SqlLoggingInterceptor());
}

控制台将直接输出可复制粘贴执行的SQL，无需手动处理参数。

方法二：使用Mac兼容的SQL Server工具执行参数化查询 #

Mac上可使用以下工具直接处理带参数的SQL：

• Azure Data Studio：微软官方跨平台工具，支持SQL Server。先声明参数再执行日志中的SQL：

  DECLARE @__ef_filter__p_0 bit = 1;
  DECLARE @__ef_filter__p_1 bit = 1;
  
  -- 粘贴EF Core日志中的SELECT语句
  SELECT TOP(1) [c].[Id], [c].[Arguments], [c].[ClientTemplateTenantId], [c].[CreationTime], [c].[CreatorUserId], [c].[DeleterUserId], [c].[DeletionTime], [c].[IsDeleted], [c].[JobStatus], [c].[LastModificationTime], [c].[LastModifierUserId], [c].[TenantId]
  FROM [CopyClientTemplateBackgroundJobLog] AS [c]
  WHERE (@__ef_filter__p_0 = CAST(1 AS bit) OR [c].[IsDeleted] = CAST(0 AS bit)) AND @__ef_filter__p_1 = CAST(1 AS bit) AND [c].[JobStatus] = 4
  ORDER BY [c].[CreationTime]
  

• DBeaver：开源跨平台数据库管理工具，支持SQL Server。它支持可视化添加参数，直接运行参数化查询，无需手动声明变量。

方法三：在代码中直接验证SQL逻辑 #

如果只需验证SQL执行结果，可在代码中用FromSqlRaw执行日志SQL并传入参数：

using (var context = new LegalRegTechDbContext(options))
{
    var p0 = true;
    var p1 = true;
    var result = context.CopyClientTemplateBackgroundJobLogs
        .FromSqlRaw(@"
            SELECT TOP(1) [c].[Id], [c].[Arguments], [c].[ClientTemplateTenantId], [c].[CreationTime], [c].[CreatorUserId], [c].[DeleterUserId], [c].[DeletionTime], [c].[IsDeleted], [c].[JobStatus], [c].[LastModificationTime], [c].[LastModifierUserId], [c].[TenantId]
            FROM [CopyClientTemplateBackgroundJobLog] AS [c]
            WHERE (@__ef_filter__p_0 = CAST(1 AS bit) OR [c].[IsDeleted] = CAST(0 AS bit)) AND @__ef_filter__p_1 = CAST(1 AS bit) AND [c].[JobStatus] = 4
            ORDER BY [c].[CreationTime]",
            new SqlParameter("@__ef_filter__p_0", p0),
            new SqlParameter("@__ef_filter__p_1", p1)
        )
        .FirstOrDefault();
}

无需切换到外部工具，直接在代码中验证SQL逻辑。

内容的提问来源于stack exchange，提问作者San Jaisy