Interceptorwat.cs

public abstract class BaseContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.AddInterceptors();
        base.OnConfiguring(optionsBuilder);
    }
}

public class TestInterceptorSettings
{
public int MaxReadsForWarning { get; set; }
public int MaxTimeForWarningMillis { get; set; }
}

public interface IDbEventNotifier
{
    void LargeQuery(DbCommand command, DataReaderClosingEventData eventData, InterceptionResult result);
    void LongQuery(DbCommand command, CommandExecutedEventData eventData);
    void LongQuery(DbCommand command, DataReaderClosingEventData eventData, InterceptionResult result);
}

public record LoggingDbEventNotifierOptions(bool LogTagOncePerLifetime = true, bool LogNonTagged = false)
{
}

public static class EFCoreIQueryableExtensions
{
    public static IQueryable<T> Needful<T>(IQueryable<T> query, [CallerFilePathAttribute]string? path = null, [CallerLineNumber]int? caller = null, [CallerMemberName]string? memberName = null)
    {
        return query.TagWith($"""
                              CallerTag
                              P-{path}
                              C-{caller}
                              M-{memberName}
                              """);
    }
}

public sealed record CallerTagInfo(string Path, string Caller, string MemberName)
{
    
    public static IReadOnlyList<CallerTagInfo> TryGetValues(string sqlText)
    {
        List<CallerTagInfo> retSet = new();

        var sp = sqlText.AsSpan();
        int st = sp.StartsWith("--") ? 1 : sp.StartsWith(Environment.NewLine) ? 2 : 0;
        while (st > 0)
        {
            if (st == 2)
            {
                sp = sp.Slice(Environment.NewLine.Length);
            }
            else 
            {
                if (sp.StartsWith("-- CallerTag"))
                {
                    sp = sp.Slice(sp.IndexOf(Environment.NewLine));
                    string p = "";
                    string c = "";
                    string mn = "";
                    int ist = sp.StartsWith(Environment.NewLine) ? 0 : 1;

                    bool TryGetTag(ref ReadOnlySpan<char> span, string tag, ref string result)
                    {
                        if (span.StartsWith(tag))
                        {
                            var idx = span.IndexOf(Environment.NewLine);
                            var len = tag.Length;
                            result = span.Slice(len, idx - len).ToString();
                            span = span.Slice(idx);
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }

                    while (true)
                    {
                        if (TryGetTag(ref sp, "-- P-", ref p))
                        {
                        }
                        else if (TryGetTag(ref sp, "-- C-", ref c))
                        {
                        }
                        else if (TryGetTag(ref sp, "-- M-", ref mn))
                        {
                        }
                        else if (sp.StartsWith(Environment.NewLine) || sp.StartsWith("--") == false)
                        {
                            //End of Tags, dump it.
                            retSet.Add(new CallerTagInfo(p, c, mn));
                            sp = sp.Slice(sp.IndexOf(Environment.NewLine));
                            break;
                        }
                        else
                        {
                            if (sp.Length < Environment.NewLine.Length)
                            {
                                break;
                            }

                            sp = sp.Slice(sp.IndexOf(Environment.NewLine));
                        }
                    }
                }
                else
                {
                    var nlIdx = sp.IndexOf(Environment.NewLine);
                    if (nlIdx > 0)
                    {
                        sp = sp.Slice(nlIdx);
                    }
                    else
                    {
                        st = 0;
                    }
                }
            }
            st = sp.StartsWith("--") ? 1 : sp.StartsWith(Environment.NewLine) ? 2 : 0;
        }

        return retSet;
    }
}

public class LoggingDbEventNotifier : IDbEventNotifier
{
    private readonly ILogger<LoggingDbEventNotifier> _logger;
    private readonly LoggingDbEventNotifierOptions _options;
    private readonly ConcurrentDictionary<string, object>? _tagHolder;

    public LoggingDbEventNotifier(ILogger<LoggingDbEventNotifier> logger, LoggingDbEventNotifierOptions options)
    {
        _logger = logger;
        _options = options;
        if (_options.LogTagOncePerLifetime)
        {
            _tagHolder = new ConcurrentDictionary<string, object>();
        }
    }

    private static readonly char[] _indexSet = new[] { '\r', '\n' };
    public void LargeQuery(DbCommand command, DataReaderClosingEventData eventData, InterceptionResult result)
    {
        if (command.CommandText.StartsWith("--"))
        {
            // Haz tag, maybe
            var newLine = command.CommandText.IndexOfAny(_indexSet);
            var tag = command.CommandText.AsSpan(0, newLine).Slice("-- ".Length).ToString();
            if (_tagHolder == null || _tagHolder.TryGetValue(tag, out _))
            {
                var tagSet = CallerTagInfo.TryGetValues(command.CommandText);
                if (tagSet.Count > 0)
                {
                    _logger.LogWarning("Large Query Detected, Records Read:{ReadCount}, TotalReadTime: {TotalReadTime} Found Tags {TagSet}",
                        eventData.ReadCount, Math.Round((eventData.StartTime - DateTimeOffset.UtcNow).TotalSeconds,4 ),tagSet);
                }
                else if (_options.LogNonTagged)
                {
                    _logger.LogWarning(
                        "Large Query Detected: Records Read: {ReadCount}, TotalReadTime: {TotalReadTime},  No tags Found!", 
                        eventData.ReadCount, 
                        (eventData.StartTime - DateTimeOffset.UtcNow).TotalSeconds);
                }
            }
        }
        else if (_options.LogNonTagged)
        {
            _logger.LogWarning(
                "Large Query Detected: Records Read: {ReadCount}, TotalReadTime: {TotalReadTime},  No tags Found!", 
                eventData.ReadCount, 
                (eventData.StartTime - DateTimeOffset.UtcNow).TotalSeconds);
        }
    }

    public void LongQuery(DbCommand command, CommandExecutedEventData eventData)
    {
        if (command.CommandText.StartsWith("--"))
        {
            // Haz tag, maybe
            var newLine = command.CommandText.IndexOfAny(_indexSet);
            var tag = command.CommandText.AsSpan(0, newLine).Slice("-- ".Length).ToString();
            if (_tagHolder == null || _tagHolder.TryGetValue(tag, out _))
            {
                var tagSet = CallerTagInfo.TryGetValues(command.CommandText);
                if (tagSet.Count > 0)
                {
                    _logger.LogWarning("Long Query Detected, TotalReadTime: {TotalReadTime} Found Tags {TagSet}",
                        Math.Round((eventData.StartTime - DateTimeOffset.UtcNow).TotalSeconds, 4), tagSet);
                }
                else
                {
                    _logger.LogWarning(
                        "Long Query Detected: TotalReadTime: {TotalReadTime},  No tags Found!",
                        (eventData.StartTime - DateTimeOffset.UtcNow).TotalSeconds);
                }
            }
        }
        else
        {
            _logger.LogWarning(
                "Long Query Detected: TotalReadTime: {TotalReadTime},  No tags Found!",
                (eventData.StartTime - DateTimeOffset.UtcNow).TotalSeconds);
        }
    }
    
    public void LongQuery(DbCommand command, DataReaderClosingEventData eventData, InterceptionResult result)
    {
        if (command.CommandText.StartsWith("--"))
        {
            // Haz tag, maybe
            var newLine = command.CommandText.IndexOfAny(_indexSet);
            var tag = command.CommandText.AsSpan(0, newLine).Slice("-- ".Length).ToString();
            if (_tagHolder == null || _tagHolder.TryGetValue(tag, out _))
            {
                var tagSet = CallerTagInfo.TryGetValues(command.CommandText);
                if (tagSet.Count > 0)
                {
                    _logger.LogWarning("Long Query Detected, Records Read:{ReadCount}, TotalReadTime: {TotalReadTime} Found Tags {TagSet}",
                        eventData, Math.Round((eventData.StartTime - DateTimeOffset.UtcNow).TotalSeconds,4 ),tagSet);
                }
                else
                {
                    _logger.LogWarning(
                        "Long Query Detected: Records Read: {ReadCount}, TotalReadTime: {TotalReadTime},  No tags Found!", 
                        eventData.ReadCount, 
                        (eventData.StartTime - DateTimeOffset.UtcNow).TotalSeconds);
                }
            }
        }
        else
        {
            _logger.LogWarning(
                "Long Query Detected: Records Read: {ReadCount}, TotalReadTime: {TotalReadTime},  No tags Found!", 
                eventData.ReadCount, 
                (eventData.StartTime - DateTimeOffset.UtcNow).TotalSeconds);
        }
    }
}
public class TestInterceptor : DbCommandInterceptor
{
    private readonly TestInterceptorSettings _settings;
    private readonly IDbEventNotifier _notifier;

    public TestInterceptor(TestInterceptorSettings settings, IDbEventNotifier notifier)
    {
        _settings = settings;
        _notifier = notifier;
    }
    public override InterceptionResult DataReaderClosing(DbCommand command, DataReaderClosingEventData eventData,
        InterceptionResult result)
    {
        if (eventData.ReadCount > _settings.MaxReadsForWarning)
        {
            _notifier.LargeQuery(command,eventData,result);
        }
        else if (eventData.StartTime.AddMilliseconds(_settings.MaxTimeForWarningMillis) <= DateTimeOffset.UtcNow)
        {
            _notifier.LongQuery(command,eventData,result);
        }
        return base.DataReaderClosing(command, eventData, result);
    }

    public override ValueTask<InterceptionResult> DataReaderClosingAsync(DbCommand command, DataReaderClosingEventData eventData, InterceptionResult result)
    {
        return ValueTask.FromResult<InterceptionResult>(DataReaderClosing(command,eventData,result));
    }

    public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
    {
        if (eventData.Duration.TotalMilliseconds >= _settings.MaxTimeForWarningMillis)
        {
            _notifier.LongQuery(command,eventData);
        }
        return base.ReaderExecuted(command, eventData, result);
    }

    public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result,
        CancellationToken cancellationToken = default)
    {
        return ValueTask.FromResult(ReaderExecuted(command, eventData, result));
    }
}