EF Core拦截器无法定位应用查询来源的问题求助

我们的应用中由EF Core生成的部分查询效率低下，这些查询虽会出现在Azure的慢查询列表中，但几乎无法确定部分查询的来源。尝试过添加注释（被剥离）、添加未使用参数（被移除）两种方案均无效，需求是执行查询时记录唯一ID并传入App Insights，结合其他日志确定查询在应用中的来源。

可行方案 #

方案1：利用EF Core的CommandTag传递查询ID #

EF Core的CommandEventData自带CommandTag属性，可附加自定义标识，该标识会被EF Core保留并传递到SQL Server，能直接关联日志与Query Store中的查询。

修改后的拦截器代码：

public class MyCommandInterceptor<T> : DbCommandInterceptor
    where T : DbContext
{
    private readonly ILogger<T> _logger;

    public MyCommandInterceptor(ILogger<T> logger)
    {
        _logger = logger;
    }

    private void TrackQuery(DbCommand command, CommandEventData eventData, string commandType)
    {
        var queryId = Guid.NewGuid();
        // 将QueryID附加到CommandTag
        eventData.CommandTag = $"QUERYID:{queryId}";
        
        // 开启日志范围，将QueryID传入App Insights
        using var scope = _logger.BeginScope(new Dictionary<string, object>
        {
            ["QueryId"] = queryId,
            ["CommandType"] = commandType
        });
        _logger.LogInformation("Executing query with ID: {QueryId}", queryId);
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        TrackQuery(command, eventData, "Reader");
        return base.ReaderExecuting(command, eventData, result);
    }

    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        TrackQuery(command, eventData, "Scalar");
        return base.ScalarExecuting(command, eventData, result);
    }

    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        TrackQuery(command, eventData, "NonQuery");
        return base.NonQueryExecuting(command, eventData, result);
    }

    public override async Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
    {
        TrackQuery(command, eventData, "Reader");
        return await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override async Task<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
    {
        TrackQuery(command, eventData, "Scalar");
        return await base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override async Task<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
    {
        TrackQuery(command, eventData, "NonQuery");
        return await base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
    }
}

关联步骤

1. 在Azure SQL的Query Store中找到慢查询，查看其执行属性中的CommandTag值
2. 在App Insights中通过QueryId筛选日志，获取该查询的执行上下文（如调用栈、请求ID等）

方案2：嵌入强制保留的注释到查询文本 #

SQL Server可能会剥离开头注释，但将注释嵌入到查询关键字（SELECT/INSERT/UPDATE/DELETE）后，会被完整保留在Query Store中。

修改LogExecuting方法：

private void LogExecuting(DbCommand command, string commandType)
{
    var queryId = Guid.NewGuid();
    var commandText = command.CommandText.TrimStart();
    
    // 将注释嵌入到查询关键字后
    if (commandText.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
    {
        command.CommandText = $"SELECT /* QUERYID: {queryId} */ {commandText.Substring(6)}";
    }
    else if (commandText.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase))
    {
        command.CommandText = $"INSERT /* QUERYID: {queryId} */ {commandText.Substring(6)}";
    }
    else if (commandText.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase))
    {
        command.CommandText = $"UPDATE /* QUERYID: {queryId} */ {commandText.Substring(6)}";
    }
    else if (commandText.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase))
    {
        command.CommandText = $"DELETE /* QUERYID: {queryId} */ {commandText.Substring(6)}";
    }
    
    _logger.LogInformation("RunningQuery - {CommandType} - {QueryId}", commandType, queryId);
}

关联步骤

1. 在Query Store中搜索包含QUERYID:的慢查询，提取对应的ID
2. 在App Insights中用该ID筛选日志，定位查询来源

方案3：通过.NET Activity关联请求与查询 #

利用.NET的Activity类，将查询ID与当前请求的追踪上下文绑定，App Insights会自动关联请求与查询日志。

修改追踪逻辑：

private void TrackQuery(DbCommand command, string commandType)
{
    var queryId = Guid.NewGuid();
    var currentActivity = Activity.Current;
    if (currentActivity != null)
    {
        // 将QueryID添加到当前请求的Activity标签中
        currentActivity.AddTag("QueryId", queryId.ToString());
    }
    
    // 日志会自动关联当前请求的Trace ID
    _logger.LogInformation("RunningQuery - {CommandType} - {QueryId}", commandType, queryId);
}

使用前提

确保应用已启用分布式追踪（ASP.NET Core集成App Insights时默认启用）。

关联步骤

在App Insights中，通过请求的Trace ID查看该请求下所有执行的查询，或直接通过QueryId筛选日志。

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