在 Entity Framework Core 中 DbCommandInterceptor 可以用來攔截和修改程式往返資料庫的 SQL 指令、回應資訊等,我們可以使用攔截器檢查回應的時間是否超過指定的毫秒,如果超過就在 Logger 中顯示警告訊息:
註冊服務:
參考資料:
Microsoft.Learn - DbCommandInterceptor Class
using System.Data.Common;
using Microsoft.EntityFrameworkCore.Diagnostics;
public class QueryPerformanceInterceptor : DbCommandInterceptor
{
private readonly ILogger<QueryPerformanceInterceptor> _logger;
public static readonly int ThresholdMilliseconds = 2_000;
public QueryPerformanceInterceptor(ILogger<QueryPerformanceInterceptor> logger)
{
_logger = logger;
_logger.LogInformation("QueryPerformanceInterceptor initialized");
}
public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData,
DbDataReader result)
{
LogCommand(eventData);
return base.ReaderExecuted(command, eventData, result);
}
public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData,
DbDataReader result,
CancellationToken cancellationToken = new CancellationToken())
{
LogCommand(eventData);
return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
}
private void LogCommand(CommandExecutedEventData eventData)
{
var elapsedMilliseconds = eventData.Duration.TotalMilliseconds;
if (!(elapsedMilliseconds > ThresholdMilliseconds)) return;
_logger.LogWarning(
"Slow query: {ElapsedMilliseconds}ms\nCommand: {Command}",
elapsedMilliseconds,
eventData.Command.CommandText
);
}
}
註冊服務:
builder.Services.AddSingleton<QueryPerformanceInterceptor>();
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ApplicationDbContext>((serviceProvider, options) =>
{
var queryPerformanceInterceptor = serviceProvider.GetRequiredService<QueryPerformanceInterceptor>();
options.UseSqlServer(connectionString)
.AddInterceptors(queryPerformanceInterceptor);
});
var app = builder.Build();
參考資料:
Microsoft.Learn - DbCommandInterceptor Class
留言
張貼留言
如果有任何問題、建議、想說的話或文章題目推薦,都歡迎留言或來信: a@ruyut.com