使用EF Core记录查询持续时间

Question

在尝试记录ASP.NET Core Web应用程序中SQL Server查询所花费的时间时，我一直在使用以下代码。在某个中间件中订阅了所有DiagnosticListeners并使用下面的Observable。 我不确定这是性能最优的解决方案，想知道是否有更好的方法，可以通过直接从EFCore捕获详细的日志对象来利用ASP.NET Core Logging API？ 理想情况下，我希望保持请求期间执行的所有查询的持续时间总和，并在请求结束时将总时间以毫秒为单位提供给中间件使用。

public class QueryTimingObserver : IObserver<DiagnosticListener>
{
    private readonly List<IDisposable> subscriptions = new List<IDisposable>();
    private readonly AsyncLocal<Stopwatch> stopwatch = new AsyncLocal<Stopwatch>();
    private double milliseconds = 0;

    void IObserver<DiagnosticListener>.OnNext(DiagnosticListener diagnosticListener)
    {
        if (diagnosticListener.Name == "SqlClientDiagnosticListener")
        {
            IDisposable subscription = diagnosticListener.SubscribeWithAdapter(this);
            subscriptions.Add(subscription);
        }
    }

    void IObserver<DiagnosticListener>.OnError(Exception error)
    {
    }

    void IObserver<DiagnosticListener>.OnCompleted()
    {
        subscriptions.ForEach(x => x.Dispose());
        subscriptions.Clear();
    }

    [DiagnosticName("System.Data.SqlClient.WriteCommandBefore")]
    public void OnCommandBefore()
    {
        stopwatch.Value = Stopwatch.StartNew();
    }

    [DiagnosticName("System.Data.SqlClient.WriteCommandAfter")]
    public void OnCommandAfter(DbCommand command)
    {
        stopwatch.Value.Stop();
        milliseconds += stopwatch.Value.Elapsed.TotalMilliseconds;
    }

    public double Milliseconds
    {
        get
        {
            return milliseconds;
        }
    }
}

- JLo

Answer 1

我会在我的项目中分享我所做的事情。

1. 为每个传入的请求创建 ApiRequest 对象。
2. 从请求中收集必要的信息，例如 IP 地址、用户代理、用户 ID、引荐来源、UriPath、搜索类型等。
3. 现在，我可以在控制器/服务层中获取 ApiRequest 对象的引用，并启动/停止计时器并计算每个步骤（数据库调用）的时间。
4. 在响应之前将 ApiRequest 对象保存到首选日志记录数据库。

ApiRequest 类：

public class ApiRequest : IDisposable
{
    [BsonId]
    public string Id { get; set; }
    // public string RequestId { get; set; }
    public string ClientSessionId { get; set; }
    public DateTime RequestStartTime { get; set; }
    public string LogLevel { get; set; }
    public string AccessProfile { get; set; }
    public string ApiClientIpAddress { get; set; }
    public GeoData ApiClientGeoData { get; set; }
    public string OriginatingIpAddress { get; set; }
    public GeoData OriginatingGeoData { get; set; }
    public int StatusCode { get; set; }
    public bool IsError { get; set; }
    // public string ErrorMessage { get; set; }
    public ConcurrentBag<string> Errors { get; set; }
    public ConcurrentBag<string> Warnings { get; set; }
    public long TotalExecutionTimeInMs { get; set; }
    public long? ResponseSizeInBytes { get; set; }
    public long TotalMySqlSpCalls { get; set; }
    public long DbConnectionTimeInMs { get; set; }
    public long DbCallTimeInMs { get; set; }
    public long OverheadTimeInMs { get; set; }
    public string UriHost { get; set; }
    public string UriPath { get; set; }
    public string SearchRequest { get; set; }
    public string Referrer { get; set; }
    public string UserAgent { get; set; }
    public string SearchType { get; set; }
    public ConcurrentQueue<RequestExecutionStatistics> ExecutionHistory { get; set; }
    public DateTime CreatedDate { get; set; }
    public string CreatedBy { get; set; }

    [BsonIgnore]
    public Stopwatch Timer { get; set; }

    public ApiRequest(Stopwatch stopwatch)
    {
        Id = Guid.NewGuid().ToString();
        // RequestId = Guid.NewGuid().ToString();
        Timer = stopwatch;
        RequestStartTime = DateTime.Now.Subtract(Timer.Elapsed);
        ExecutionHistory = new ConcurrentQueue<RequestExecutionStatistics>();
        ExecutionHistory.Enqueue(new RequestExecutionStatistics
        {
            Description = "HTTP Request Start",
            Status = RequestExecutionStatus.Started,
            Index = 1,
            StartTime = Timer.ElapsedMilliseconds,
            ExecutionTimeInMs = 0
        });
        Errors = new ConcurrentBag<string>();
        Warnings = new ConcurrentBag<string>();
    }

    public Task AddExecutionTimeStats(string description, RequestExecutionStatus status, long startTime, long executionTimeInMs)
    {
        int count = ExecutionHistory.Count;

        return Task.Run(() =>
            ExecutionHistory.Enqueue(new RequestExecutionStatistics
            {
                Description = description,
                Status = status,
                Index = count + 1,
                StartTime = startTime,
                ExecutionTimeInMs = executionTimeInMs
            }));
    }

    public Task UpdateExecutionTimeStats(string description, long executionTimeInMs)
    {
        return Task.Run(() =>
        {
            var stats = ExecutionHistory.FirstOrDefault(e => e.Description.ToLower() == description.ToLower());
            if (stats != null) stats.ExecutionTimeInMs = executionTimeInMs;
        });
    }

    #region IDisposable implementation

    private bool _disposed;

    // Public implementation of Dispose pattern callable by consumers.
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    // Protected implementation of Dispose pattern.
    protected virtual void Dispose(bool disposing)
    {
        if (_disposed) { return; }

        if (disposing)
        {
            // Free managed objects here.
            Timer.Stop(); // Probably doesn't matter, but anyways...
        }

        // Free any unmanaged objects here.


        // Mark this object as disposed.
        _disposed = true;
    }

    #endregion
}

示例控制器：

[Route("api/[controller]")]
[Authorize(Policy = Constants.Policies.CanAccessSampleSearch)]
public class SampleSearchController : Controller
{
    private readonly AppSettings _appSettings;
    private readonly ISampleSearchService _sampleService;

    public SampleSearchController(IOptions<AppSettings> appSettings, ISampleSearchService sampleSearchService)
    {
        _appSettings = appSettings.Value;
        _sampleService = sampleSearchService;
    }

    [HttpPost]
    public async Task<Response> PostAsync([FromBody]PersonRequest request)
    {
        var apiRequest = HttpContext.Items[Constants.CacheKeys.SampleApiRequest] as ApiRequest;
        await apiRequest.AddExecutionTimeStats("Sample Search Controller", RequestExecutionStatus.Started,
            apiRequest.Timer.ElapsedMilliseconds, 0);

        var result = new Response();

        if (!ModelState.IsValid)
        {
            apiRequest.StatusCode = (int)HttpStatusCode.BadRequest;
            apiRequest.IsError = true;

            foreach (var modelState in ModelState.Values)
            {
                foreach (var error in modelState.Errors)
                {
                    apiRequest.Errors.Add(error.ErrorMessage);
                }
            }
        }
        else
        {
            result = await _sampleService.Search(request);
        }

        await apiRequest.AddExecutionTimeStats("Sample Search Controller", RequestExecutionStatus.Complted, 0,
            apiRequest.Timer.ElapsedMilliseconds);

        return result;
    }
}

样例服务：

public class SampleSearchService : ISampleSearchService
{
    #region Variables

    private readonly AppSettings _appSettings;
    private readonly IStateService _stateService;
    private readonly IDistrictService _districtService;
    private readonly IHttpContextAccessor _httpContextAccessor;

    #endregion Variables

    public SampleSearchService(IOptions<AppSettings> appSettings, IStateService stateService,
        IDistrictService districtService, IHttpContextAccessor httpContextAccessor)
    {
        _appSettings = appSettings.Value;
        _stateService = stateService;
        _districtService = districtService;
        _httpContextAccessor = httpContextAccessor;
    }

    public async Task<Response> Search(PersonRequest request)
    {
        var apiRequest =
            _httpContextAccessor.HttpContext.Items[Constants.CacheKeys.SampleApiRequest] as ApiRequest;

        await apiRequest.AddExecutionTimeStats("Sample Search Service", RequestExecutionStatus.Started,
            apiRequest.Timer.ElapsedMilliseconds, 0);

        var response = new Response();

        using (var db = new ApplicationDb(_appSettings.ConnectionStrings.MyContext))
        {
            var dbConnectionStartTime = apiRequest.Timer.ElapsedMilliseconds;
            await db.Connection.OpenAsync();
            apiRequest.DbConnectionTimeInMs = apiRequest.Timer.ElapsedMilliseconds - dbConnectionStartTime;

            await apiRequest.AddExecutionTimeStats("DB Connection", RequestExecutionStatus.Complted,
                dbConnectionStartTime, apiRequest.DbConnectionTimeInMs);

            using (var command = db.Command(_appSettings.StoredProcedures.GetSampleByCriteria,
                _appSettings.Timeouts.GetSampleByCriteriaTimeout, CommandType.StoredProcedure, db.Connection))
            {
                command.Parameters.Add(new MySqlParameter
                {
                    ParameterName = "p_Id",
                    DbType = DbType.Int64,
                    Value = request.Id
                });                    

                try
                {
                    await apiRequest.AddExecutionTimeStats("Sample DB Call", RequestExecutionStatus.Started,
                        apiRequest.Timer.ElapsedMilliseconds, 0);

                    response = await ReadAllAsync(await command.ExecuteReaderAsync());

                    await apiRequest.AddExecutionTimeStats("Sample DB Call", RequestExecutionStatus.Complted, 0,
                        apiRequest.Timer.ElapsedMilliseconds);
                }
                catch (Exception e)
                {
                    apiRequest.StatusCode = (int)HttpStatusCode.InternalServerError;
                    apiRequest.IsError = true;
                    apiRequest.Errors.Add(e.Message);
                }
            }
        }

        apiRequest.SearchRequest = JsonConvert.SerializeObject(request);
        await apiRequest.AddExecutionTimeStats("Sample Search Service", RequestExecutionStatus.Complted, 0,
            apiRequest.Timer.ElapsedMilliseconds);
        return response;
    }

    private async Task<Response> ReadAllAsync(DbDataReader reader)
    {
        var SampleResponse = new Response();

        using (reader)
        {
            while (await reader.ReadAsync())
            {
                SampleResponse.AvailableCount = Convert.ToInt32(await reader.GetFieldValueAsync<long>(0));

                if (reader.NextResult())
                {
                    while (await reader.ReadAsync())
                    {
                        var SampleRecord = new Record()
                        {                                
                            District = !await reader.IsDBNullAsync(1) ? await reader.GetFieldValueAsync<string>(6) : null,
                            State = !await reader.IsDBNullAsync(2) ? await reader.GetFieldValueAsync<string>(7) : null
                        };

                        SampleResponse.Records.Add(SampleRecord);
                    }
                }
            }
        }

        return SampleResponse;
    }
}

ApiRequestsMiddleware:

public class ApiRequestsMiddleware
{
    private readonly RequestDelegate _next;
    private readonly IApiRequestService _apiRequestService;

    public ApiRequestsMiddleware(RequestDelegate next, IApiRequestService apiRequestService)
    {
        _next = next;
        _apiRequestService = apiRequestService;
    }

    public async Task InvokeAsync(HttpContext context)
    {
        var stopwatch = Stopwatch.StartNew();
        var accessProfileName = context.User.Claims != null && context.User.Claims.Any()
            ? context.User.Claims.FirstOrDefault(c => c.Type == ClaimTypes.Name)?.Value
            : null;

        try
        {
            var request = new ApiRequest(stopwatch)
            {
                AccessProfile = accessProfileName,
                ClientSessionId = ContextHelper.GetHeadersValue(context, Constants.Headers.ClientSessionId),
                ApiClientIpAddress = context.Connection.RemoteIpAddress.ToString()
            };

            var originatingIpAddress = ContextHelper.GetHeadersValue(context, Constants.Headers.OriginatingIpAddress);
            request.OriginatingIpAddress = !string.IsNullOrWhiteSpace(originatingIpAddress)
                ? originatingIpAddress
                : context.Connection.RemoteIpAddress.ToString();

            request.UriHost = context.Request.Host.ToString();
            request.UriPath = context.Request.Path;

            var referrer = ContextHelper.GetHeadersValue(context, Constants.Headers.Referrer);
            request.Referrer = !string.IsNullOrWhiteSpace(referrer) ? referrer : null;

            var userAgent = ContextHelper.GetHeadersValue(context, Constants.Headers.UserAgent);
            request.UserAgent = !string.IsNullOrWhiteSpace(userAgent) ? userAgent : null;

            request.SearchType = SearchHelper.GetSearchType(request.UriPath).ToString();

            context.Items.Add(Constants.CacheKeys.SampleApiRequest, request);

            await _next(context);
            stopwatch.Stop();

            request.StatusCode = context.Response.StatusCode;
            request.LogLevel = LogLevel.Information.ToString();
            request.TotalExecutionTimeInMs = stopwatch.ElapsedMilliseconds;

            if (request.IsError)
                request.LogLevel = LogLevel.Error.ToString();

            if (_apiRequestService != null)
                Task.Run(() => _apiRequestService.Create(request));
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }
}

public static class ApiRequestsMiddlewareExtensions
{
    public static IApplicationBuilder UseApiRequests(this IApplicationBuilder builder)
    {
        return builder.UseMiddleware<ApiRequestsMiddleware>();
    }
}

ApiRequestService（向数据库插入日志条目）：

public class ApiRequestService : IApiRequestService
{
    private readonly IMongoDbContext _context;
    private readonly IIpLocatorService _ipLocatorService;

    public ApiRequestService(IMongoDbContext context, IIpLocatorService ipLocatorService)
    {
        _context = context;
        _ipLocatorService = ipLocatorService;
    }

    public async Task<IEnumerable<ApiRequest>> Get()
    {
        return await _context.ApiRequests.Find(_ => true).ToListAsync();
    }

    public async Task<ApiRequest> Get(string id)
    {
        var filter = Builders<ApiRequest>.Filter.Eq("Id", id);

        try
        {
            return await _context.ApiRequests.Find(filter).FirstOrDefaultAsync();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }

    public async Task Create(ApiRequest request)
    {
        try
        {
            request.OriginatingGeoData = null; // await _ipLocatorService.Get(request.OriginatingIpAddress);

            var finalHistory = new ConcurrentQueue<RequestExecutionStatistics>();

            foreach (var history in request.ExecutionHistory.ToList())
            {
                if (history.Status == RequestExecutionStatus.Started)
                {
                    var findPartner = request.ExecutionHistory.FirstOrDefault(e =>
                        e.Description.ToLower() == history.Description.ToLower() &&
                        e.Status == RequestExecutionStatus.Complted);

                    if (findPartner != null)
                    {
                        var temp = history.Clone();
                        temp.Status = RequestExecutionStatus.Complted;
                        temp.ExecutionTimeInMs = findPartner.ExecutionTimeInMs - history.StartTime;
                        finalHistory.Enqueue(temp);
                    }
                    else
                        finalHistory.Enqueue(history);
                }
                else if (history.Status == RequestExecutionStatus.Complted)
                {
                    var findPartner = request.ExecutionHistory.FirstOrDefault(e =>
                        e.Description.ToLower() == history.Description.ToLower() &&
                        e.Status == RequestExecutionStatus.Started);

                    if (findPartner == null)
                        finalHistory.Enqueue(history);
                }
            }

            request.ExecutionHistory = finalHistory;
            request.CreatedDate = DateTime.Now;
            request.CreatedBy = Environment.MachineName;
            await _context.ApiRequests.InsertOneAsync(request);
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }
}

在Configure方法中注册：

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {
        // Other code        

        app.UseApiRequests();
        app.UseResponseWrapper();
        app.UseMvc();
    }

样本日志输出：

{
"_id" : "75a90cc9-5d80-4eb8-aae1-70a3611fe8ff",
"RequestId" : "98be1d85-9941-4a17-a73a-5f0a38bd7703",
"ClientSessionId" : null,
"RequestStartTime" : ISODate("2019-09-11T15:22:05.802-07:00"),
"LogLevel" : "Information",
"AccessProfile" : "Sample",
"ApiClientIpAddress" : "0.0.0.0",
"ApiClientGeoData" : null,
"OriginatingIpAddress" : "0.0.0.0",
"OriginatingGeoData" : null,
"StatusCode" : 200,
"IsError" : false,
"Errors" : [],
"Warnings" : [],
"TotalExecutionTimeInMs" : NumberLong(115),
"ResponseSizeInBytes" : NumberLong(0),
"TotalMySqlSpCalss" : NumberLong(0),
"DbConnectionTimeInMs" : NumberLong(3),
"DbCallTimeInMs" : NumberLong(0),
"OverheadTimeInMs" : NumberLong(0),
"UriHost" : "www.sampleapi.com",
"UriPath" : "/api/Samples",
"SearchRequest" : null,
"Referrer" : null,
"UserAgent" : null,
"SearchType" : "Sample",
"ExecutionHistory" : [ 
    {
        "Description" : "HTTP Request Start",
        "Index" : 1,
        "StartTime" : NumberLong(0),
        "ExecutionTimeInMs" : NumberLong(0)
    }, 
    {
        "Description" : "Sample Search Controller",
        "Index" : 2,
        "StartTime" : NumberLong(0),
        "ExecutionTimeInMs" : NumberLong(115)
    }, 
    {
        "Description" : "Sample Search Service",
        "Index" : 3,
        "StartTime" : NumberLong(0),
        "ExecutionTimeInMs" : NumberLong(115)
    }, 
    {
        "Description" : "DB Connection",
        "Index" : 4,
        "StartTime" : NumberLong(0),
        "ExecutionTimeInMs" : NumberLong(3)
    }, 
    {
        "Description" : "Sample DB Call",
        "Index" : 5,
        "StartTime" : NumberLong(3),
        "ExecutionTimeInMs" : NumberLong(112)
    }
],
"CreatedDate" : ISODate("2019-09-11T15:22:05.918-07:00"),
"CreatedBy" : "Server"}

- Nishith Shah

Answer 2

public class QueryInterceptor
    {
        private readonly ILogger<QueryInterceptor> _logger;
        private string _query;
        private DateTimeOffset _startTime;
        public QueryInterceptor(ILogger<QueryInterceptor> logger)
        {
            _logger = logger;
        }

        [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuting")]
        public void OnCommandExecuting(DbCommand command, DbCommandMethod executeMethod, Guid commandId, Guid connectionId, bool async, DateTimeOffset startTime)
        {
            _query = command.CommandText;
            _startTime = startTime;
        }

        [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuted")]
        public void OnCommandExecuted(object result, bool async)
        {
            var endTime = DateTimeOffset.Now;
            var queryTiming = (endTime - _startTime).TotalSeconds;
            _logger.LogInformation("\n" + "Executes " + "\n" + _query + "\n" + "in " + queryTiming + " seconds\n");
        }

        [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandError")]
        public void OnCommandError(Exception exception, bool async)
        {
            _logger.LogError(exception.Message);
        }
    }

在 Program.cs 中

var loggerQueryInterceptor = services.GetService<ILogger<QueryInterceptor>>();
var listener = context.GetService<DiagnosticSource>();
(listener as DiagnosticListener).SubscribeWithAdapter(new QueryInterceptor(loggerQueryInterceptor));

- Nicola

Answer 3

衡量性能的方法有很多种，但是当涉及到计时查询时，我通常会从数据库服务器工具（如SQL Profiler）开始。对于SQL Server而言，SQL Server Profiler和SQL Server Management Studio工具提供了无穷无尽的细节。

现在，我不能详细评论你的代码，但我可以给你一些值得考虑的建议。如果我想在代码中记录查询执行时间，我通常会采取以下两种做法之一。

1）经过试验的秒表从来不会出错。就像你正在做的那样。

2）现在，我最喜欢的工具。如果可以的话，我会在我的项目中使用MiniProfiler。它来自Stack Exchange，这个网站由我们正在讨论分析功能的人管理。它显示SQL查询并警告您有关常见错误的信息。您可以将其与EF Core一起使用，并且它有一个漂亮的小工具，您可以轻松地将其集成到网站页面上，以查看任何时候发生的情况。

- Dennis VW

Answer 4

查询性能应该在两个层面上进行，即计时控制器/服务操作和在数据库层面上进行分析。性能问题出现的原因很多，可能仅在控制器或数据库中检测到，也可能同时存在于两者之间。在代码级别记录性能指标应该始终可配置，以便轻松地打开和关闭，因为任何性能捕获都代表着性能损失，还需要资源空间来记录结果。

冒着有点跑题的风险，我概述了我遇到的典型性能陷阱以及如何测量/检测它们。目的只是为了概述为什么 SQL 侧面分析与基于代码的计时器一起检测潜在的性能问题很有价值，以及确定解决这些问题的步骤。

1. 流氓的懒加载调用。通过SQL分析检测到。Web请求查询数据时，初始查询完成得很快，Web请求在调试器中似乎很快结束，但客户端却需要一段时间才能收到响应。在初始查询和请求完成后，分析器跟踪了许多“按ID”查询。这些是由序列化程序触发的惰性加载调用。原因：将实体传递给视图/消费者的反模式。解决方案：使用投影填充视图模型。
2. 昂贵的查询。通过代码计时器和SQL分析检测到。这些查询需要大量时间才能运行，可以通过调试检测到。这些查询还显示了可检测的执行时间在分析器结果中。解决方案包括检查执行查询以进行执行计划改进（缺少/可改进的索引），以确定查询是否可以简化或在代码中分解。
3. 间歇性缓慢的查询。通过代码计时器和SQL分析检测到。这些可以通过检查分析结果提前捕获。特征迹象是涉及显着大的行接触计数的查询。在低负载场景下运行时，这些查询的执行时间可能非常合理。在高负载时间，执行时间可能会急剧上升。涉及大量行的查询可能会因需要避免脏读而相互绑定行锁。锁定会导致延迟和潜在死锁。解决方案包括：优化查询以仅拉回它们需要利用索引更好的数据，并针对报告和搜索的扩展查询，在较大的系统中考虑访问只读副本。
4. EF“主义”。通过代码计时器检测，而不是分析。有些EF查询在投影实体图时可能会变得有点奇怪。底层查询运行快速，不会产生其他查询，但EF查询会在返回之前停滞不前。有时这些结果可能来自于复杂的Linq表达式，导致一个看起来合理的查询，但它似乎就是停在那里。有时原因可能相当明显，例如长时间存活的DbContext最终跟踪了整个数据库的一大部分。其他时候，解决方案需要逐个案例进行分析。它们通常涉及使用/修改投影来简化从EF返回的数据，或将查询分解为更简单的部分，即使这意味着两个查询而不是一个。

- Steve Py

Answer 5

这是一个演示，没有在生产环境中进行测试

将记录查询时间大于10秒的日志

public class SlowQueryLogger : RelationalCommandDiagnosticsLogger
{
    public SlowQueryLogger(ILoggerFactory loggerFactory, ILoggingOptions loggingOptions, DiagnosticSource diagnosticSource, LoggingDefinitions loggingDefinitions, IDbContextLogger contextLogger, IDbContextOptions contextOptions, IInterceptors? interceptors = null) : base(loggerFactory, loggingOptions, diagnosticSource, loggingDefinitions, contextLogger, contextOptions, interceptors)
    {
    }

    public override ValueTask<DbDataReader> CommandReaderExecutedAsync(IRelationalConnection connection, DbCommand command, DbContext? context, Guid commandId, Guid connectionId, DbDataReader methodResult, DateTimeOffset startTime, TimeSpan duration, CommandSource commandSource, CancellationToken cancellationToken = default)
    {
        if (duration > TimeSpan.FromSeconds(10))
        {
#pragma warning disable EF1001 // Internal EF Core API usage.
            ((RelationalLoggingDefinitions)Definitions).LogExecutedCommand =
     new EventDefinition<string, string, CommandType, int, string, string>(
         Options,
         RelationalEventId.CommandExecuted,
         LogLevel.Warning,
         "RelationalEventId.CommandExecuted",
         (LogLevel level) =>
         LoggerMessage.Define<string, string, CommandType, int, string, string>(level,
         RelationalEventId.CommandExecuted,
         "Executed DbCommand ({elapsed}ms) [Parameters=[{parameters}], CommandType='{commandType}', CommandTimeout='{commandTimeout}']{newLine}{commandText}"));
        }
        else
        {
            ((RelationalLoggingDefinitions)Definitions).LogExecutedCommand =
new EventDefinition<string, string, CommandType, int, string, string>(
Options,
RelationalEventId.CommandExecuted,
LogLevel.Information,
"RelationalEventId.CommandExecuted",
(LogLevel level) =>
LoggerMessage.Define<string, string, CommandType, int, string, string>(level,
RelationalEventId.CommandExecuted,
"Executed DbCommand ({elapsed}ms) [Parameters=[{parameters}], CommandType='{commandType}', CommandTimeout='{commandTimeout}']{newLine}{commandText}"));
        }
        return base.CommandReaderExecutedAsync(connection, command, context, commandId, connectionId, methodResult, startTime, duration, commandSource, cancellationToken);
#pragma warning restore EF1001 // Internal EF Core API usage.
    }
}

In program.cs

services.AddDbContextPool<ContextBase>((Action<DbContextOptionsBuilder>)(option =>
{
xxxx...
    option.ReplaceService<IRelationalCommandDiagnosticsLogger, SlowQueryLogger>();
}));

- aisy