# EF Core Query interceptor

Query interceptor in EF core allows developer to intercept the query before or after the execution. This provides the ability of interception, modification, or suppression of the query execution.

### How to use Query Interceptor into code

EF core exposes `DbCommandInterceptor`  base class, this base class is abstract class and has overridable methods which could be hooked into a class which is inherited `DbCommandInterceptor` class. Since Entity framework internally uses [ADO.NET](file:///C:/Users/deejain/AppData/Local/Temp/msohtmlclip1/01/clip_filelist.xml) for database operations,  `DbCommandInterceptor` class provides lifecycle methods for a query.

### Code example

**Create a class that inherits** `DbCommandInterceptor`

```csharp
public class QueryInterceptor :DbCommandInterceptor {
}
```

**Override the lifecycle method which you are interested in**

```csharp
public class QueryInterceptor :DbCommandInterceptor {
	// runs before a query is executed
	public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command,CommandEventData eventData, InterceptionResult<DbDataReader> result){
		Console.WriteLine($"Before Query execution. Query : {command.CommandText}");
		return result;
	}
	// runs after a query is excuted
	public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result) {
		Console.WriteLine($"After Query execution. Query : {command.CommandText}");
		return result;
	}        
}
```

`DbCommandInterceptor` provides async version of these methods to support asynchronous operations

```csharp
public class QueryInterceptor :DbCommandInterceptor {
	public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default){
		return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
	}
	public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken = default){
		return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
	}
}
```

**Register the interceptor with DbContext:**

An interceptor could be added to context using two ways :

* register while adding DbContext to services
    
    ```csharp
    services.AddDbContext<ExampleContext>(opt => 
      opt.UseNpgsql(cfg.GetConnectionString("DefaultConnectionString"))
         .AddInterceptors (new QueryInterceptor()));
    ```
    

* register inside `OnConfiguring` method of DbContext class
    
    ```csharp
    public class ExampleContext : DbContext{
    	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder){
    		base.OnConfiguring(optionsBuilder);
    		optionsBuilder.AddInterceptors(new QueryInterceptor())
    	}
    }
    ```
    

### List of lifecycle methods available

| Method Name | **Description** |
| --- | --- |
| [CommandCanceled](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.commandcanceled?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-commandcanceled(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandendeventdata)) , [CommandCanceledAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.commandcanceledasync?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-commandcanceledasync(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandendeventdata-system-threading-cancellationtoken)) | called when a command is cancelled. |
| [CommandCreated](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.commandcreated?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-commandcreated(microsoft-entityframeworkcore-diagnostics-commandendeventdata-system-data-common-dbcommand)) | After command creation but before execution. |
| [CommandCreating](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.commandcreating?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-commandcreating(microsoft-entityframeworkcore-diagnostics-commandcorrelatedeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult((system-data-common-dbcommand)))) | Before a command is created |
| [CommandFailed](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.commandfailed?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-commandfailed(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commanderroreventdata)), [CommandFailedAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.commandfailedasync?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-commandfailedasync(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commanderroreventdata-system-threading-cancellationtoken)) | Called when execution of a command has failed with an exception. |
| [CommandInitialized](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.commandinitialized?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-commandinitialized(microsoft-entityframeworkcore-diagnostics-commandendeventdata-system-data-common-dbcommand)) | Called after EF has initialized [CommandText](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.commandtext#system-data-common-dbcommand-commandtext) and other command configuration. |
| [DataReaderClosing](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.datareaderclosing?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-datareaderclosing(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-datareaderclosingeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult)), [DataReaderClosingAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.datareaderclosingasync?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-datareaderclosingasync(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-datareaderclosingeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult)) | Called just before EF intends to call [Close()](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbdatareader.close#system-data-common-dbdatareader-close). |
| [DataReaderDisposing](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.datareaderdisposing?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-datareaderdisposing(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-datareaderdisposingeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult)) | Called when execution of a [DbDataReader](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbdatareader) is about to be disposed. |
| [NonQueryExecuted](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.nonqueryexecuted?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-nonqueryexecuted(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandexecutedeventdata-system-int32)), [NonQueryExecutedAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.nonqueryexecutedasync?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-nonqueryexecutedasync(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandexecutedeventdata-system-int32-system-threading-cancellationtoken)) | Called immediately after EF calls [ExecuteNonQuery()](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executenonquery#system-data-common-dbcommand-executenonquery). |
| [NonQueryExecuting](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.nonqueryexecuting?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-nonqueryexecuting(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult((system-int32)))), [NonQueryExecutingAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.nonqueryexecutingasync?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-nonqueryexecutingasync(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult((system-int32))-system-threading-cancellationtoken)) | Called just before EF intends to call [ExecuteNonQuery()](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executenonquery#system-data-common-dbcommand-executenonquery). |
| [ReaderExecuted](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.readerexecuted?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-readerexecuted(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandexecutedeventdata-system-data-common-dbdatareader)), [ReaderExecutedAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.readerexecutedasync?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-readerexecutedasync(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandexecutedeventdata-system-data-common-dbdatareader-system-threading-cancellationtoken)) | Called immediately after EF calls [ExecuteReader()](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executereader#system-data-common-dbcommand-executereader). |
| [ReaderExecuting](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.readerexecuting?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-readerexecuting(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult((system-data-common-dbdatareader)))), [ReaderExecutingAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.readerexecutingasync?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-readerexecutingasync(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult((system-data-common-dbdatareader))-system-threading-cancellationtoken)) | Called just before EF intends to call [ExecuteReader()](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executereader#system-data-common-dbcommand-executereader). |
| [ScalarExecuted](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.scalarexecuted?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-scalarexecuted(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandexecutedeventdata-system-object)), [ScalarExecutedAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.scalarexecutedasync?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-scalarexecutedasync(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandexecutedeventdata-system-object-system-threading-cancellationtoken)) | Called immediately after EF calls [ExecuteScalar()](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executescalar#system-data-common-dbcommand-executescalar). |
| [ScalarExecuting](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.scalarexecuting?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-scalarexecuting(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult((system-object)))), [ScalarExecutingAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.diagnostics.idbcommandinterceptor.scalarexecutingasync?view=efcore-7.0#microsoft-entityframeworkcore-diagnostics-idbcommandinterceptor-scalarexecutingasync(system-data-common-dbcommand-microsoft-entityframeworkcore-diagnostics-commandeventdata-microsoft-entityframeworkcore-diagnostics-interceptionresult((system-object))-system-threading-cancellationtoken)) | Called just before EF intends to call [ExecuteScalar()](https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executescalar#system-data-common-dbcommand-executescalar). |

### Resources

* [Query interception in Entity Framework Core - What’s keeping Lizzy busy? (lizzy-gallagher.github.io)](https://lizzy-gallagher.github.io/query-interception-entity-framework/)
    
* [Interceptors - EF Core | Microsoft Learn](https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors)