Creating interceptors to log and process Entity Framework commands and results

Sometimes you want to inspect or log the exact SQL statements that Entity Framework executes. EF6 and EF Core 2.x both provide interception hooks for this.

For EF6, implement an IDbCommandInterceptor interface:

public class LoggingInterceptor : IDbCommandInterceptor
{
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> context)
    {
        Console.WriteLine("Executing SQL: " + command.CommandText);
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> context) { }
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> context) { }
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> context) { }
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> context) { }
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> context) { }
}

Then, register the interceptor at startup:

DbInterception.Add(new LoggingInterceptor());

In EF Core 2.x, interception support is more limited, but you can still hook into the logging pipeline via DbContextOptionsBuilder.LogTo or a custom DiagnosticListener subscriptions like this:

optionsBuilder.LogTo(sql => Debug.WriteLine(sql));

Where can you tutilize these, then? Practical uses include:

• Capturing slow queries for tuning.
• Auditing read/write operations.
• Testing generated SQL before deployment.

Be careful not to log sensitive data from parameters, especially in production.