DBContext pooling isn't working with Interceptors and healthchecks

Question

I had the need to optimze some sql queries with "OPTION (OPTIMIZE FOR UNKNOWN)".

I therefore added a custom Interceptor like this:

public class QueryHintInterceptor : DbCommandInterceptor
{
    private DbCommand GetCommandWithQueryHint(DbCommand command)
    {
        if (command.CommandText.StartsWith($"-- {Const.TagWith.OptimizeForUnknown}") && !command.CommandText.EndsWith(Const.TagWith.OptimizeForUnknown))
            command.CommandText += $" {Const.TagWith.OptimizeForUnknown}";

        return command;
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        return base.ReaderExecuting(GetCommandWithQueryHint(command), eventData, result);
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
    {
        return base.ReaderExecutingAsync(GetCommandWithQueryHint(command), eventData, result, cancellationToken);
    }
}

I added an extension method:

    public static IQueryable<TEntity> OptimizeForUnknown<TEntity>(this IQueryable<TEntity> dbset) where TEntity : ImportEntity
    {
        return dbset.TagWith(Const.TagWith.OptimizeForUnknown); //we set this tag and detect it on execution to inject the desired sql
    }

I registered it in OnConfiguring as described by microsoft itself:

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder.AddInterceptors(new QueryHintInterceptor()); 
        }
    }

So I can use it like this:

var query = icrmContext.Set<TEntity>()
    .AsNoTracking()
    .OptimizeForUnknown();

This works well for my intended query, but unfortunately healthchecks start to fail now:

{
    "status": "Unhealthy",
    "results": {
        "MyDbContext": {
            "status": "Unhealthy",
            "description": "'OnConfiguring' cannot be used to modify DbContextOptions when DbContext pooling is enabled.",
            "responseTime": "861,8354",
            "data": {}
        }
    }
}

Okay, I use DbContext pooling with AddDbContextPool, let's configure it there, I thought:

        services.AddDbContextPool<MyDbContext>((sp, options) =>
        {
            options
                .UseSqlServer(sqlConnectionString, opts =>
                {
                    opts.CommandTimeout(commandTimeoutInSeconds);
                    opts.EnableRetryOnFailure();
                })
                .AddInterceptors(ServiceProviderExtensions.GetRequiredService<QueryHintInterceptor>(sp));
        });

After these changes healthchecks work again, but my Interceptor isn't working anymore (no breakpoints in QueryHintInterceptor get hit).

Here is my Healthcheck-configuration:

var healthChecksBuilder = services.AddHealthChecks()
    .AddDbContextCheck<MyDbContext>();

If this was a general problem, why would my query work as expected? On the other hand why does my health check fail?

Answer 1

I tried reproducing this with my interceptor using both the DbContextPool and DbContextHealthCheck in case there was some potential conflict and they worked fine. When you debug the interceptor, is your breakpoint on the first line of "GetCommandWithQueryHint"? The only other thing I can think of is that somehow your dependency injection might not be resolving the DbContext instance through the registered pool provider. (Though your health check seems to indicate it is)

The implementation of the interceptors I use can be found here: https://github.com/StevePy/Ag.Isle.EF.Core/tree/main/Ag.Isle.EF.Core/Interceptors Feel free to have a look as this implementation works for appending multiple Options interceptors via extension methods so it handles multiple tags and composing the Options section.

The code I initialized this with included:

       builder.Services.AddHealthChecks()
          .AddDbContextCheck<TestDbContext>();
       builder.Services.AddDbContextPool<TestDbContext>((sp, options) =>
       {
           options.UseSqlServer(connectionString)
               .AddInterceptors(new RecompileInterceptor());
       });

and that worked just fine, the RecompileInterceptor was called when I added it to a simple test run:

    public IActionResult Index()
    {
        var searches = _context.Searches
            .WithRecompile()
            .ToList();
        return View();
    }

Breakpoint in the interceptor was hit and the option was added.

-- {{RECOMPILE}}

SELECT [s].[SearchId], [s].[AppId], [s].[CreatedAt], [s].[CreatedBy], [s].[Json], [s].[LastModAt], [s].[LastModBy], [s].[Name], [s].[RowVersion], [s].[TypeName], [s].[UserId]
FROM [PSS].[Searches] AS [s] OPTION (RECOMPILE)