Query Interception for Performance Tuning

In this lesson, you will learn how to inject database-specific performance hints into your LINQ queries by intercepting the SQL generation pipeline.

The Problem: When LINQ Isn't Enough

Sometimes, EF Core generates logically correct SQL that performs poorly. For example, a complex query might benefit from a NOLOCK hint in SQL Server to prevent blocking, or a specific INDEX hint to force the query optimizer to take a more efficient path. Since LINQ doesn't have built-in keywords for these engine-specific directives, we use Query Interception to modify the SQL command string just before it is sent to the database.

Implementing an Interceptor

To achieve this, we implement the ICommandInterceptor interface. This allows us to hook into the ReaderExecuting method, where we can inspect the Command object and append our performance directives to the CommandText.

public class QueryHintInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result)
    {
        // Check if our specific query is being executed
        if (command.CommandText.Contains("SELECT [p].[Id], [p].[Name] FROM [Products]"))
        {
            // Inject the SQL Server NOLOCK hint
            command.CommandText = command.CommandText.Replace(
                "[Products]", 
                "[Products] WITH (NOLOCK)");
        }

        return base.ReaderExecuting(command, eventData, result);
    }
}

Registration

Once your interceptor is defined, you must register it when configuring your DbContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(connectionString)
        .AddInterceptors(new QueryHintInterceptor());
}

Why Use This Sparingly?

While this provides immense control, it effectively "hard-codes" SQL implementation details into your C# application. Because you are performing string manipulation on generated SQL, ensure your logic is precise to avoid accidental corruption of other queries. This technique should be reserved for critical performance bottlenecks where standard query optimization has failed.

Key Takeaway: Query Interception allows you to manually inject database-specific optimizer directives by transforming the generated SQL command string before execution.