Add IDbCommandInterceptor or similar to allow injecting custom query execution service

[kosinsky]

Sometime application needs more control on executing build SQL statement in the database to add custom retry logic, circuit breakers, CPU usage logging and throttling, injecting session-wide information to support Row-Level-Security etc. That requires injecting custom code that will get SQL Command from the EF Core (or just text + list of parameters), executing it and returning IDataReader back to the EF Core.

EF6 supported that approach by deriving from DbCommandInterceptor and overriding ReaderExecuting and ScalarExecuting.

This issue is a detailed case for #626

[tstone84]

"injecting session-wide information to support Row-Level-Security" is a critical feature preventing us from moving to EF Core. Currently, in EF6 we use DbCommandInterceptor and DbConnectionInterceptor to inject SQL to handle setting session context for SQL Server Row Level Security. Is there an existing way to do this in EF Core?

[ajcvickers]

@kosinsky PR: #16113 Does this look like it covers what you need?

[kosinsky]

@kosinsky PR: #16113 Does this look like it covers what you need?

@ajcvickers Partially. Looks like when implementing IDbCommandInterceptor we don't have a way to access original DbContext or service provider (and then get DbContext scoped service that we could use for command execution). Am I missing something?
In my use case, we attaching our custom SqlResourceComponent (it's responsible for query execution, RLS, circuit breakers etc.) to DbContext and then in IDbCommandIntercepor we have something like:

public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            var context = interceptionContext.DbContexts.FirstOrDefault() as IComponentContext;
            if (context != null)
            {
                if (context.Component != null)
                {
                    var result = context.Component.ExecuteContextCommand(command as SqlCommand);
                    interceptionContext.Result = result;
                    return;
                }
                    base.ReaderExecuting(command, interceptionContext);
                    return;
            }
        }

[nphmuller]

@kosinsky @ajcvickers Injecting the DbContext in the interceptor's constructor would probably work, right? Or are only Singleton interceptors supported?

[ajcvickers]

@kosinsky See #16159 and PR #16162

[nphmuller]

@ajcvickers Great! Just curious, but is there any reason the DbContext couldn't by injected in the interceptor via its constructor? Or would that give a circular reference somehow? Or is there perhaps another benefit I'm missing?

[ajcvickers]

@nphmuller Interceptors are currently resolved only from the internal service provider. So having one depend on DbContext will usually not work, since it's not usually registered in the internal service provider. The interceptor might be able to depend on ICurrentDbContext to achieve the same thing, but that wouldn't help when an app is using an interceptor that is not registered in D.I., which is what I expect will be the most common usage.

[nphmuller]

@ajcvickers I didn't think about the non DI scenario, since DI seems so much like the normal route in ASP.NET Core. Thanks for the clear explanation!

I learned about ICurrentDbContext when I looked at these PRs and it seems like an easy way to access the current DbContext in extension points.