Intercept and alter sql query generated by the Application

Question

I have a situation where a plugin is querying and getting some data, I cannot change the query in plugin as its a DLL. I have checked with the SQL profiler what query it is making and as per our requirement we have changed the database schema in that area, hence breaking that plugin query.

Is there any way to intercept the query and alter it?

Just like how we do in JS framework like Angular that we have interceptor to receive each call and add token in the header, do we have something like that to intercept all outgoing SQL calls and alter it?

Maybe middleware can work here as I am in .NET-Core or some kind of handler?

Answer 1

The query could be changed in a interceptor.

EF 6

Implement IDbCommandInterceptor, for example:

class EFCommandInterceptor: IDbCommandInterceptor
{

    public void NonQueryExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        // Manipulate the command text, etc. here...
        command.CommandText += " OPTION (OPTIMIZE FOR UNKNOWN)";
    }

    ...

Register:

public class FE6CodeConfig : DbConfiguration
{
    public FE6CodeConfig()
    {
        this.AddInterceptor(new EFCommandInterceptor());
    }
}

See more details here

EF Core

Also EF Core has interceptors nowadays. You need EF Core 3 or later.

While EF Core 3 needs .NET Standard 2.1 (so . NET Core 3 and later), EF Core 3.1 supports .NET Standard 2.0, so .NET Core 2 and . NET Framework 4.6.1+

Inherit of DbCommandInterceptor, e.g.

public class HintCommandInterceptor : DbCommandInterceptor
{
    public override InterceptionResult ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult result)
    {
        // Manipulate the command text, etc. here...
        command.CommandText += " OPTION (OPTIMIZE FOR UNKNOWN)";
        return result;
    }
}

Register:

services.AddDbContext(b => b
    .UseSqlServer(connectionString)
    .AddInterceptors(new HintCommandInterceptor())