如何启用EF Core DbContext中Dapper查询的日志及ApplicationInsights SQL依赖日志

一、启用Dapper查询日志 #

Dapper本身没有内置日志能力，我们可以通过包装DbConnection的方式拦截Dapper执行操作，结合.NET日志系统实现SQL执行日志：

1. 创建日志包装类 #

实现继承自DbConnection的包装类，在命令执行前后记录日志：

public class LoggedDbConnection : DbConnection
{
    private readonly DbConnection _innerConnection;
    private readonly ILogger<LoggedDbConnection> _logger;

    public LoggedDbConnection(DbConnection innerConnection, ILogger<LoggedDbConnection> logger)
    {
        _innerConnection = innerConnection;
        _logger = logger;
    }

    // 重写DbConnection所有抽象成员，直接委托给内部连接
    public override string ConnectionString
    {
        get => _innerConnection.ConnectionString;
        set => _innerConnection.ConnectionString = value;
    }

    public override string Database => _innerConnection.Database;
    public override string DataSource => _innerConnection.DataSource;
    public override string ServerVersion => _innerConnection.ServerVersion;
    public override ConnectionState State => _innerConnection.State;

    public override void ChangeDatabase(string databaseName) => _innerConnection.ChangeDatabase(databaseName);
    public override void Close() => _innerConnection.Close();
    public override void Open() => _innerConnection.Open();
    public override Task OpenAsync(CancellationToken cancellationToken) => _innerConnection.OpenAsync(cancellationToken);

    // 拦截Command创建，返回带日志的Command包装类
    public override DbCommand CreateCommand()
    {
        var command = _innerConnection.CreateCommand();
        return new LoggedDbCommand(command, _logger);
    }
}

public class LoggedDbCommand : DbCommand
{
    private readonly DbCommand _innerCommand;
    private readonly ILogger<LoggedDbConnection> _logger;

    public LoggedDbCommand(DbCommand innerCommand, ILogger<LoggedDbConnection> logger)
    {
        _innerCommand = innerCommand;
        _logger = logger;
    }

    // 重写DbCommand所有抽象成员，委托给内部命令
    public override string CommandText
    {
        get => _innerCommand.CommandText;
        set => _innerCommand.CommandText = value;
    }

    public override int CommandTimeout
    {
        get => _innerCommand.CommandTimeout;
        set => _innerCommand.CommandTimeout = value;
    }

    public override CommandType CommandType
    {
        get => _innerCommand.CommandType;
        set => _innerCommand.CommandType = value;
    }

    public override UpdateRowSource UpdatedRowSource
    {
        get => _innerCommand.UpdatedRowSource;
        set => _innerCommand.UpdatedRowSource = value;
    }

    protected override DbConnection DbConnection
    {
        get => _innerCommand.Connection;
        set => _innerCommand.Connection = value;
    }

    protected override DbParameterCollection DbParameterCollection => _innerCommand.Parameters;

    protected override DbTransaction DbTransaction
    {
        get => _innerCommand.Transaction;
        set => _innerCommand.Transaction = value;
    }

    public override bool DesignTimeVisible
    {
        get => _innerCommand.DesignTimeVisible;
        set => _innerCommand.DesignTimeVisible = value;
    }

    public override void Cancel() => _innerCommand.Cancel();
    public override int ExecuteNonQuery() => ExecuteAndLog(() => _innerCommand.ExecuteNonQuery());
    public override Task<int> ExecuteNonQueryAsync(CancellationToken cancellationToken) => ExecuteAndLogAsync(() => _innerCommand.ExecuteNonQueryAsync(cancellationToken));
    public override object ExecuteScalar() => ExecuteAndLog(() => _innerCommand.ExecuteScalar());
    public override Task<object> ExecuteScalarAsync(CancellationToken cancellationToken) => ExecuteAndLogAsync(() => _innerCommand.ExecuteScalarAsync(cancellationToken));
    protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) => ExecuteAndLog(() => _innerCommand.ExecuteReader(behavior));
    protected override Task<DbDataReader> ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) => ExecuteAndLogAsync(() => _innerCommand.ExecuteReaderAsync(behavior, cancellationToken));
    public override void Prepare() => _innerCommand.Prepare();
    public override DbParameter CreateParameter() => _innerCommand.CreateParameter();

    // 核心日志逻辑
    private T ExecuteAndLog<T>(Func<T> executeFunc)
    {
        var stopwatch = Stopwatch.StartNew();
        try
        {
            var result = executeFunc();
            stopwatch.Stop();
            _logger.LogInformation("SQL执行成功 | 耗时 {ElapsedMs}ms | SQL: {Sql} | 参数: {Params}",
                stopwatch.ElapsedMilliseconds,
                CommandText,
                string.Join(", ", Parameters.Cast<DbParameter>().Select(p => $"{p.ParameterName}={p.Value}")));
            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            _logger.LogError(ex, "SQL执行失败 | 耗时 {ElapsedMs}ms | SQL: {Sql} | 参数: {Params}",
                stopwatch.ElapsedMilliseconds,
                CommandText,
                string.Join(", ", Parameters.Cast<DbParameter>().Select(p => $"{p.ParameterName}={p.Value}")));
            throw;
        }
    }

    private async Task<T> ExecuteAndLogAsync<T>(Func<Task<T>> executeFunc)
    {
        var stopwatch = Stopwatch.StartNew();
        try
        {
            var result = await executeFunc();
            stopwatch.Stop();
            _logger.LogInformation("异步SQL执行成功 | 耗时 {ElapsedMs}ms | SQL: {Sql} | 参数: {Params}",
                stopwatch.ElapsedMilliseconds,
                CommandText,
                string.Join(", ", Parameters.Cast<DbParameter>().Select(p => $"{p.ParameterName}={p.Value}")));
            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            _logger.LogError(ex, "异步SQL执行失败 | 耗时 {ElapsedMs}ms | SQL: {Sql} | 参数: {Params}",
                stopwatch.ElapsedMilliseconds,
                CommandText,
                string.Join(", ", Parameters.Cast<DbParameter>().Select(p => $"{p.ParameterName}={p.Value}")));
            throw;
        }
    }
}

2. 替换EF Core获取的连接 #

在业务代码中用包装类包裹原始连接：

private readonly YourDbContext _context;
private readonly ILogger<LoggedDbConnection> _logger;

public YourService(YourDbContext context, ILogger<LoggedDbConnection> logger)
{
    _context = context;
    _logger = logger;
}

// 使用时
var originalConn = _context.GetConnection();
var loggedConn = new LoggedDbConnection(originalConn, _logger);
await loggedConn.QueryAsync<Guid>(query, parameters);

3. 配置日志级别 #

在appsettings.json中开启对应日志级别：

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "YourNamespace.LoggedDbConnection": "Information"
    }
  }
}

二、将SQL依赖记录到ApplicationInsights #

1. 基础配置 #

先安装Microsoft.ApplicationInsights.AspNetCore包，然后在Program.cs中配置：

var builder = WebApplication.CreateBuilder(args);
// 配置ApplicationInsights连接字符串
builder.Services.AddApplicationInsightsTelemetry(builder.Configuration["ApplicationInsights:ConnectionString"]);

2. 自动跟踪SQL依赖 #

ApplicationInsights的DependencyTrackingTelemetryModule默认会拦截ADO.NET操作，若使用自定义连接包装类，需手动注册类型：

builder.Services.ConfigureTelemetryModule<DependencyTrackingTelemetryModule>((module, _) =>
{
    module.RegisterAdditionalSqlClientTypes(typeof(LoggedDbConnection));
});

3. 手动记录依赖（可选） #

若自动跟踪失效，可在日志包装类中手动创建依赖项：

// 在LoggedDbConnection构造函数中注入TelemetryClient
private readonly TelemetryClient _telemetryClient;

public LoggedDbConnection(DbConnection innerConnection, ILogger<LoggedDbConnection> logger, TelemetryClient telemetryClient)
{
    _innerConnection = innerConnection;
    _logger = logger;
    _telemetryClient = telemetryClient;
}

// 修改ExecuteAndLog方法，添加手动跟踪逻辑
private T ExecuteAndLog<T>(Func<T> executeFunc)
{
    var stopwatch = Stopwatch.StartNew();
    var telemetry = new DependencyTelemetry
    {
        Type = "SQL",
        Name = $"SQL执行: {CommandText.Substring(0, Math.Min(CommandText.Length, 100))}",
        Target = _innerConnection.DataSource,
        Data = CommandText,
        StartTime = DateTimeOffset.Now
    };

    try
    {
        var result = executeFunc();
        stopwatch.Stop();
        telemetry.Duration = stopwatch.Elapsed;
        telemetry.Success = true;
        _telemetryClient.TrackDependency(telemetry);
        
        _logger.LogInformation("SQL执行成功 | 耗时 {ElapsedMs}ms | SQL: {Sql} | 参数: {Params}",
            stopwatch.ElapsedMilliseconds,
            CommandText,
            string.Join(", ", Parameters.Cast<DbParameter>().Select(p => $"{p.ParameterName}={p.Value}")));
        return result;
    }
    catch (Exception ex)
    {
        stopwatch.Stop();
        telemetry.Duration = stopwatch.Elapsed;
        telemetry.Success = false;
        telemetry.Properties["Exception"] = ex.Message;
        _telemetryClient.TrackDependency(telemetry);
        
        _logger.LogError(ex, "SQL执行失败 | 耗时 {ElapsedMs}ms | SQL: {Sql} | 参数: {Params}",
            stopwatch.ElapsedMilliseconds,
            CommandText,
            string.Join(", ", Parameters.Cast<DbParameter>().Select(p => $"{p.ParameterName}={p.Value}")));
        throw;
    }
}

注意事项 #

• 日志记录参数时，需对敏感信息（如密码）做脱敏处理，避免数据泄露。
• 确保在请求范围内执行查询，ApplicationInsights会自动关联请求上下文，让依赖项与对应请求绑定。
• 若EF Core返回的连接已打开，包装类不要重复执行打开/关闭操作。

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