Logging SQL statements in Entity Framework with interception
In a previous post I have shown how to log SQL statements that Entity Framework creates for us. In that post we used DbContext.Database.Log for logging. DbContext.Database.Log is a very effective tool but if you want more flexibility you should try out DbCommand Interception.
DbCommand Interception makes it available for you to intercept commands both before and after execution. This makes it a very powerful tool for logging.
You set up Interception by creating a class that implements the IDbCommandInterceptor interface
Lets have look at how that could be done
class EFInterceptor : IDbCommandInterceptor
{
public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
Log(String.Format("NonQueryExecuted with the command {0}", command.CommandText));
}
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
Log(String.Format("NonQueryExecuting with the command {0}", command.CommandText));
}
public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
Log(String.Format("ReaderExecuted with the command {0}", command.CommandText));
}
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
Log(String.Format("ReaderExecuting with the command {0}", command.CommandText));
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
Log(String.Format("ScalarExecuted with the command {0}", command.CommandText));
}
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
Log(String.Format("ScalarExecuting with the command {0}", command.CommandText));
}
private void Log(string message)
{
Console.WriteLine(message);
}
}
After implementing the interface we need to wire it up to the application. That can be done in config file or in code.
Config File
<entityFramework>
<interceptors>
<interceptor type="EntityFrameworkLogging.EFInterceptor, EntityFrameworkLogging">
</interceptor>
</interceptors>
</entityFramework>
Code
public class Configuration : DbConfiguration
{
Configuration()
{
DbInterception.Add(new EFInterceptor());
}
}
The implementation above is very basic and simply outputs the commands to the console but you could log to whatever logging framework that you have in place.
As you may already have noticed Interception can be used for much more than logging SQL statements. Here is a small subset of possibilities
- Error logging by inspecting interceptionContext.Exception
- Feature toggle on behavior towards the database - for instance soft deletes vs. hard deletes
- Change SQL statements that are executed on the database
- Change results that are returned from the database
You might also be interested in these articles...
You can also find Morten Anderson on Twitter, LinkedIn and Stackoverflow. Get in touch by Email.
