public sealed class QueryAuditContext
{
    public string EndpointName { get; set; } = string.Empty;
    public List<QueryExecutionRecord> Executions { get; } = new();
    public int SlowQueryCount { get; private set; }
    public int NPlusOneCount { get; private set; }

    public void RecordExecution(string sql, TimeSpan duration, bool isSlow)
    {
        Executions.Add(new QueryExecutionRecord(sql, duration, isSlow));
        if (isSlow) SlowQueryCount++;
    }

    public void FlagNPlusOne(string fingerprint, int occurrences)
    {
        NPlusOneCount++;
        // Store for later reporting
    }
}

public record QueryExecutionRecord(string Sql, TimeSpan Duration, bool IsSlow);

public sealed class EfCoreQueryAuditor : ICommandInterceptor
{
    private readonly QueryAuditContext _context;
    private readonly DiagnosticOptions _options;

    public EfCoreQueryAuditor(QueryAuditContext context, DiagnosticOptions options)
    {
        _context = context;
        _options = options;
    }

    public DbCommand ReaderExecuting(DbCommand command, CommandEventData eventData, DbCommand result)
    {
        command.StartTime = DateTime.UtcNow;
        return result;
    }

    public DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
    {
        var duration = DateTime.UtcNow - command.StartTime;
        var isSlow = duration.TotalMilliseconds >= _options.SlowQueryThresholdMs;
        
        _context.RecordExecution(command.CommandText, duration, isSlow);
        return result;
    }

    public async ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken)
    {
        var duration = DateTime.UtcNow - command.StartTime;
        var isSlow = duration.TotalMilliseconds >= _options.SlowQueryThresholdMs;
        
        _context.RecordExecution(command.CommandText, duration, isSlow);
        return result;
    }
}

// Extension to attach timing without modifying DbCommand
public static class DbCommandExtensions
{
    private static readonly AsyncLocal<DateTime?> _startTime = new();
    public static DateTime StartTime 
    { 
        get => _startTime.Value ?? DateTime.MinValue; 
        set => _startTime.Value = value; 
    }
}

public sealed class QueryDiagnosticMiddleware
{
    private readonly RequestDelegate

_next; private readonly DiagnosticOptions _options; private readonly ILogger<QueryDiagnosticMiddleware> _logger;

public QueryDiagnosticMiddleware(RequestDelegate next, DiagnosticOptions options, ILogger<QueryDiagnosticMiddleware> logger)
{
    _next = next;
    _options = options;
    _logger = logger;
}

public async Task InvokeAsync(HttpContext context, QueryAuditContext auditContext)
{
    auditContext.EndpointName = context.GetEndpoint()?.DisplayName ?? "Unknown";
    
    await _next(context);

    AnalyzeAndReport(auditContext);
}

private void AnalyzeAndReport(QueryAuditContext context)
{
    var fingerprints = context.Executions
        .GroupBy(e => NormalizeFingerprint(e.Sql))
        .Where(g => g.Count() > 1)
        .ToList();

    foreach (var group in fingerprints)
    {
        if (group.Count() >= _options.NPlusOneThreshold)
        {
            _logger.LogWarning(
                "⚠️ N+1 pattern detected at {Endpoint}. Query repeated {Count}x. Sample: {Sql}",
                context.EndpointName, group.Count(), group.First().Sql[..Math.Min(80, group.First().Sql.Length)]);
        }
    }

    var slowQueries = context.Executions.Where(e => e.IsSlow);
    foreach (var query in slowQueries)
    {
        _logger.LogWarning(
            "⚠️ Slow query at {Endpoint}. Duration: {Duration}ms. SQL: {Sql}",
            context.EndpointName, query.Duration.TotalMilliseconds, query.Sql[..Math.Min(80, query.Sql.Length)]);
    }
}

private static string NormalizeFingerprint(string sql)
{
    // Strip parameters, collapse whitespace, uppercase keywords
    return Regex.Replace(sql, @"@[\w]+", "@p")
                .Replace("\r", " ").Replace("\n", " ")
                .ToUpperInvariant();
}

}

### Step 4: Wire into DI and Middleware
Registration ties the scoped context, interceptor, and middleware together.

```csharp
public static class DiagnosticServiceCollectionExtensions
{
    public static IServiceCollection AddQueryDiagnostics(this IServiceCollection services, Action<DiagnosticOptions>? configure = null)
    {
        var options = new DiagnosticOptions();
        configure?.Invoke(options);
        services.AddSingleton(options);
        services.AddScoped<QueryAuditContext>();
        services.AddScoped<EfCoreQueryAuditor>();
        return services;
    }
}

public static class DiagnosticApplicationBuilderExtensions
{
    public static IApplicationBuilder UseQueryDiagnostics(this IApplicationBuilder app)
    {
        return app.UseMiddleware<QueryDiagnosticMiddleware>();
    }
}

public record DiagnosticOptions
{
    public int SlowQueryThresholdMs { get; init; } = 500;
    public int NPlusOneThreshold { get; init; } = 3;
}

Architecture Rationale

• Request-Scoped Context: Prevents metric bleeding between concurrent requests. AsyncLocal is avoided for the main context to maintain explicit DI lifecycle control.
• Interceptor Over Middleware: EF Core's command pipeline is the only reliable place to capture actual SQL execution timing. Middleware alone cannot measure database round-trip duration accurately.
• Fingerprint Normalization: Parameterized queries differ by value but share structure. Normalizing @id1, @id2 to @p enables accurate N+1 detection without false negatives.
• Deferred Reporting: Metrics are collected during execution and reported after await _next(context). This ensures the full request lifecycle is captured before analysis.

Pitfall Guide

1. Interceptor Lifecycle Mismatch

Explanation: Registering the interceptor as a singleton while the audit context is scoped causes cross-request state corruption. The interceptor will write to a stale or shared context. Fix: Always register the interceptor as Scoped or resolve it via serviceProvider.GetRequiredService<T>() during AddDbContext configuration. Match the context lifecycle exactly.

2. Threshold Calibration Neglect

Explanation: The default 500ms threshold assumes a local development database. In cloud environments or high-latency networks, legitimate queries may exceed this, generating noise. Conversely, strict thresholds may miss subtle N+1 cascades. Fix: Baseline your database latency under realistic load. Set SlowQueryThresholdMs to 1.5x your p95 network round-trip time. Use environment-specific configuration to adjust thresholds per deployment stage.

3. Parameterized Query Masking

Explanation: N+1 detection fails if the fingerprinting logic doesn't normalize parameters. Queries like WHERE Id = @id1 and WHERE Id = @id2 appear distinct, bypassing repetition detection. Fix: Implement robust SQL normalization that strips parameter names, collapses whitespace, and standardizes casing. Consider using a lightweight SQL parser for complex queries, or rely on EF Core's CommandText which already parameterizes values.

4. Cross-Request State Leakage via AsyncLocal

Explanation: Using AsyncLocal<T> for the audit context without explicit cleanup can leak state across request boundaries in high-throughput scenarios, especially when thread pool recycling occurs. Fix: Prefer DI-scoped services over AsyncLocal for request state. If AsyncLocal is unavoidable, implement IDisposable on the middleware to explicitly clear the context after request completion.

5. Production Log Noise

Explanation: Leaving diagnostic logging enabled in production floods log aggregators with warnings, increases I/O overhead, and obscures genuine errors. Fix: Gate diagnostic output behind a configuration flag or environment check. Implement sampling strategies (e.g., log only 10% of requests) or disable entirely in production, relying on APM for post-deployment visibility.

6. Ignoring Bulk Operation Legitimacy

Explanation: Batch inserts or bulk updates naturally execute multiple commands. Flagging them as N+1 creates false positives and erodes developer trust in the tool. Fix: Add a whitelist mechanism for known bulk operation patterns. Alternatively, track command types (INSERT, UPDATE, DELETE) and only flag SELECT repetition as N+1 candidates.

7. Connection Pool Starvation Blindness

Explanation: The interceptor measures query count and duration but doesn't correlate with connection pool metrics. An N+1 pattern might appear "fast" locally but exhaust pool connections under concurrency. Fix: Integrate with Microsoft.Extensions.Diagnostics.ResourceMonitoring or emit custom metrics to OpenTelemetry. Correlate query counts with SqlConnectionPool wait times to surface pool exhaustion risks early.

Production Bundle

Action Checklist

• Register QueryAuditContext and EfCoreQueryAuditor as scoped services in Program.cs
• Attach the interceptor to DbContextOptions using AddInterceptors()
• Place UseQueryDiagnostics() early in the middleware pipeline, after routing but before authentication
• Configure environment-specific thresholds via appsettings.{Environment}.json
• Implement SQL fingerprint normalization that handles parameterized queries
• Add conditional logging guards to disable diagnostics in production builds
• Validate interceptor lifecycle matches context scope to prevent state leakage
• Correlate diagnostic output with OpenTelemetry traces using Activity.Current?.Id

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Local Development	Full request-scoped interception with console warnings	Immediate feedback, zero infrastructure, catches N+1 before commit	None
Staging/CI Pipeline	Interceptor enabled with threshold lowered to 200ms + JSON log output	Automated regression detection, integrates with test runners	Minimal CI overhead
Production (High Traffic)	Disable interceptor, rely on APM sampling + connection pool metrics	Eliminates per-request overhead, prevents log noise	Zero runtime cost
Production (Debugging)	Enable interceptor with 5% sampling rate + correlation IDs	Targeted diagnostics without full overhead	Low I/O cost
Microservices Architecture	Per-service interceptor + centralized log aggregation	Isolates ORM behavior per bounded context, avoids cross-service tracing complexity	Moderate logging cost

Configuration Template

{
  "QueryDiagnostics": {
    "Enabled": true,
    "SlowQueryThresholdMs": 500,
    "NPlusOneThreshold": 3,
    "LogOutputFormat": "Console",
    "ProductionSamplingRate": 0.0,
    "WhitelistedEndpoints": [
      "/health",
      "/metrics"
    ]
  }
}

// Program.cs integration
var diagnosticsConfig = builder.Configuration.GetSection("QueryDiagnostics");
builder.Services.AddQueryDiagnostics(options =>
{
    options.SlowQueryThresholdMs = diagnosticsConfig.GetValue<int>("SlowQueryThresholdMs");
    options.NPlusOneThreshold = diagnosticsConfig.GetValue<int>("NPlusOneThreshold");
});

builder.Services.AddDbContext<AppDbContext>((sp, opts) =>
{
    opts.UseSqlServer(builder.Configuration.GetConnectionString("Default"));
    opts.AddInterceptors(sp.GetRequiredService<EfCoreQueryAuditor>());
});

var app = builder.Build();
app.UseRouting();
app.UseQueryDiagnostics(); // Place after routing for accurate endpoint names
app.UseAuthentication();
app.UseAuthorization();
app.MapControllers();
app.Run();

Quick Start Guide

1. Install Dependencies: Ensure your project targets .NET 8 or later. EF Core 8+ is required for ICommandInterceptor support.
2. Register Services: Call AddQueryDiagnostics() in Program.cs, then attach the interceptor to your DbContext configuration using AddInterceptors().
3. Add Middleware: Insert app.UseQueryDiagnostics() after UseRouting() to ensure endpoint metadata is available for accurate logging.
4. Configure Thresholds: Adjust SlowQueryThresholdMs and NPlusOneThreshold in appsettings.json to match your database latency profile.
5. Validate: Run the application, trigger an endpoint with navigation properties, and observe console warnings for repeated queries or duration breaches. Add .Include() or .Select() projections to resolve flagged patterns.