LongQueryInterceptor.cs

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

namespace PostgresExtensions;

/*
Usage:

services.AddDbContextPool<DBContext>((serviceProvider, o) =>
                                    {
                                       o.UseNpgsql("connection string here");
                                       o.AddLongQueryInterceptor(serviceProvider, TimeSpan.FromSeconds(1));
                                    });
*/

sealed class LongQueryInterceptor(ILogger logger, TimeSpan threshold) : DbCommandInterceptor
{
    public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken = default)
    {
        if (eventData.Duration > threshold)
            log(command, eventData);
        
        return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
    }
    
    public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
    {
        if (eventData.Duration > threshold)
            log(command, eventData);
        
        return base.ReaderExecuted(command, eventData, result);
    }
    
    void log(DbCommand command, CommandExecutedEventData eventData)
    {
        var parms = formatParameters(command.Parameters);
        
        logger.LogWarning("Long query [{0} ms with threshold {1} ms]: {2}{3}",
                          (int) eventData.Duration.TotalMilliseconds,
                          (int) threshold.TotalMilliseconds,
                          command.CommandText,
                          parms);
    }
    
    string formatParameters(DbParameterCollection parameters)
    {
        if (parameters.Count == 0)
            return "";
        
        var sb = new StringBuilder();
        sb.Append(Environment.NewLine);
        sb.Append("Parameters: ");
        var counter = 0;
        foreach (DbParameter p in parameters)
        {
            if (counter > 0)
                sb.Append(", ");
            
            sb.Append(p.ParameterName);
            sb.Append(" = ");
            
            if (p.Value == null) sb.Append("NULL");
            else
                sb.Append(p.Value switch
                          {
                              int[] ints       => $"[{string.Join(", ", ints)}]",
                              string[] strings => $"[{string.Join(", ", strings.Select(c => $"'{c}'"))}]",
                              string s         => $"'{s}'",
                              _                => p.Value
                          });
            counter++;
        }
        
        return sb.ToString();
    }
}

public static class LongQueryInterceptorExtensions
{
    public static DbContextOptionsBuilder AddLongQueryInterceptor(this DbContextOptionsBuilder o, IServiceProvider sp, TimeSpan threshold) =>
        o.AddInterceptors(new LongQueryInterceptor(sp.GetRequiredService<ILogger<LongQueryInterceptor>>(), threshold));
}