Identifying and debugging performance bottlenecks in your database queries is essential for keeping high-traffic applications responsive. Entity Framework Core provides two primary mechanisms to monitor query execution: Built-in Logging (LogTo) for capturing all database traffic, and Custom Command Interceptors (DbCommandInterceptor) for programmatically catching slow queries that exceed a specific duration threshold.

Method 1: Built-in EF Core Logging (LogTo)

EF Core makes it easy to log generated SQL statements, parameters, and execution durations directly through Microsoft.Extensions.Logging.

Configuration in Program.cs

You can hook LogTo into your DbContext options. This routes all database commands directly to your application's logging pipeline (or console):

C#

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString)
           .LogTo(consoleLoggerFactory.CreateLogger<AppDbContext>(), LogLevel.Information)
           .EnableSensitiveDataLogging(); // Optional: Includes parameter values (use with caution in production!)
});

When a query runs, your logs will output execution details including elapsed time:

Plaintext

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (42ms) [Parameters=[@__id_0='5'], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [u].[Id], [u].[Name] FROM [Users] AS [u] WHERE [u].[Id] = @__id_0

Method 2: Custom Slow Query Interceptor (DbCommandInterceptor)

While built-in logging shows execution times, parsing text logs manually in production is inefficient. A Custom Command Interceptor allows you to automatically detect queries that exceed a specific threshold (e.g., greater than 500ms) and emit a dedicated warning or metric.

Step 1: Create the Interceptor Class

Inherit from DbCommandInterceptor and override the *Executed methods, which expose the CommandExecutedEventData containing the exact Duration of the query.

C#

using System.Data.Common;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;

public class SlowQueryInterceptor : DbCommandInterceptor
{
    private readonly ILogger<SlowQueryInterceptor> _logger;
    private readonly TimeSpan _threshold = TimeSpan.FromMilliseconds(500); // Set your slow query threshold (e.g., 500ms)

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

    public override DbDataReader ReaderExecuted(
        DbCommand command,
        CommandExecutedEventData eventData,
        DbDataReader result)
    {
        CheckDuration(command, eventData.Duration);
        return base.ReaderExecuted(command, eventData, result);
    }

    public override int NonQueryExecuted(
        DbCommand command,
        CommandExecutedEventData eventData,
        int result)
    {
        CheckDuration(command, eventData.Duration);
        return base.NonQueryExecuted(command, eventData, result);
    }

    public override object? ScalarExecuted(
        DbCommand command,
        CommandExecutedEventData eventData,
        object? result)
    {
        CheckDuration(command, eventData.Duration);
        return base.ScalarExecuted(command, eventData, result);
    }

    private void CheckDuration(DbCommand command, TimeSpan duration)
    {
        if (duration > _threshold)
        {
            _logger.LogWarning(
                "SLOW QUERY DETECTED: Execution took {ElapsedMilliseconds}ms (Threshold: {ThresholdMs}ms).\nSQL Command:\n{CommandText}",
                duration.TotalMilliseconds,
                _threshold.TotalMilliseconds,
                command.CommandText);
        }
    }
}

Step 2: Register the Interceptor in Program.cs

Register your custom interceptor as a singleton or scoped service, and chain it into your DbContext options using .AddInterceptors():

C#

// Register the interceptor
builder.Services.AddSingleton<SlowQueryInterceptor>();

builder.Services.AddDbContext<AppDbContext>((sp, options) =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))
           .AddInterceptors(sp.GetRequiredService<SlowQueryInterceptor>());
});

Best Practices for Production Monitoring

  1. Avoid Sensitive Data Logging in Production: Enabling .EnableSensitiveDataLogging() logs plaintext parameter values (e.g., user passwords or personal data) into your logs. Keep it restricted to Development environments.

  2. Integrate with APM Tools: Instead of just logging warnings to the console, you can easily push slow query alerts from your interceptor into Application Performance Monitoring tools like Application Insights, Datadog, or OpenTelemetry for tracking performance over time.