C#: EFCommandInterceptor - Intercepts EF commands and tags them with the class.method(parameters).linenumber. Even action queries, while .TagWithCallSite() only does Selects

EFCommandInterceptor.cs

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


namespace ~~~~~;

public class EFCommandInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        var msg = GetLineInfo();
        FormatMessage(command, msg);
        return base.NonQueryExecuting(command, eventData, result);
    }
    public override async ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
    {
        var msg = GetLineInfo();
        FormatMessage(command, msg);
        return await base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        var msg = GetLineInfo();
        FormatMessage(command, msg);
        return base.ReaderExecuting(command, eventData, result);
    }
    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
    {
        var msg = GetLineInfo();
        FormatMessage(command, msg);
        return await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        var msg = GetLineInfo();
        FormatMessage(command, msg);
        return base.ScalarExecuting(command, eventData, result);
    }
    public override async ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
    {
        var msg = GetLineInfo();
        FormatMessage(command, msg);
        return await base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }

    protected string GetLineInfo(bool includeLineNumber = true)
    {
        StackTrace stack = new StackTrace(true);
        var frames = stack.GetFrames();
        var frame = frames
            .Skip(2)
            .SkipWhile(f =>
        {
            //var method = f.GetMethod();
            return f.GetFileName() is null;
        }).FirstOrDefault();

        if (frame is null) { return string.Empty; }
        var methodInfo = frame.GetMethod();
        var parameters = string.Join(", ", methodInfo.GetParameters().Select(p => $"{p.ParameterType.Name} {p.Name}"));
        var lineNumber = includeLineNumber ? $" Line: {frame.GetFileLineNumber()}" : "";
        var message = $"{methodInfo.ReflectedType.FullName}.{methodInfo.Name}({parameters}){lineNumber}";
        Trace.WriteLine($"{this.GetType().Assembly.GetName().Name}: {message}");
        return message;
    }

    private static void FormatMessage(DbCommand command, string msg)
    {
        if (string.IsNullOrWhiteSpace(msg)) { msg = "No Line Info"; }
        command.CommandText = $"/*{msg}*/\r\n\r\n{command.CommandText}";
    }
}

ExampleStartup.cs

// in the program startup.cs:

services
  .AddDbContextFactory<MyDbContext>(options =>
      options
        .UseSqlServer(configuration.GetConnectionString(connectionStringName))
        .AddInterceptors(new EFCommandInterceptor()),
    ServiceLifetime.Transient);

ExampleStartupOptionalInterceptor.cs

  services
    .AddDbContextFactory<MyDbContext>(options => 
    {
      options.UseSqlServer(configuration.GetConnectionString(defaultConnectionStringName));
      if (configuration.GetValue<bool>("Logging:LogEFCommands"))
      {
        options.AddInterceptors(new EFCommandInterceptor());
      }
    }, ServiceLifetime.Transient);

ExampleUsage.cs

public partial class MyDbContext : DbContext
{
	private readonly IDbCommandInterceptor _eFCommandInterceptor;

	public MyDbContext(DbContextOptions<MyDbContext> options, IServiceProvider provider) : base(options)
	{
		_eFCommandInterceptor = provider.GetService<IDbCommandInterceptor>();
	}

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    base.OnConfiguring(optionsBuilder);
    if (_eFCommandInterceptor != null)
    {
      optionsBuilder.AddInterceptors(_eFCommandInterceptor);
    }
  }
}
  
// Startup.cs:
if (configuration.GetValue<bool>("Logging:LogEFCommands"))
{
	services.AddScoped<IDbCommandInterceptor, EFCommandInterceptor>(); 
}

It would be preferable to use .TagWithCallSite() as it is injected at compile time, while this interceptor is at run time and uses reflection. Which will cause a performance penalty. I have not measured how much as of yet.

Benefits:

1. The tag of the query is global. Makes it easier to tag all of your queries.
2. The tag will work for action queries as well. While .TagWithCallSite() only works for Selects atm
3. You can use it to write the information to AppInsights, or any other system. (Additional time cost)
4. Instead of just the file name, you get the class name and the method name.
5. Works with older versions of EF where .TagWithCallSite() is not available

Cons:

1. Additional time cost of using reflection to inspect the StackTrace.
2. Line numbers may not match up because of compiler inlining.
3. Depends upon MSFT and other nuget packages being built with pdb:none and your in house libraries being built with at least pdb:portable (which is the default build option).

One thought to mitigate this performance cost is to add a configuration flag so that you can turn on and off the interceptor and use it only when needed.