Introductory debugging information in Entity Framework queries

Question

We use Dapper and EF in our store, and Dapper is extremely useful when debugging queries on the SQL server when something went wrong. Instead of simply representing the original SQL, we created a thin decorator that also adds some contextual information (source) as an SQL comment, something like

/* Foo.Bar.GetOrders() */ SELECT * FROM Order WHERE orderId > 123 

This allows our database administrators and developers to quickly and quickly find the source of the problem if we have errors in the database that are erroneous or lead to performance impacts (we have hundreds of thousands of database calls per day, so one bad request may cause quite some damage).

We would also like to do this with EF. This does not have to be a SQL comment, but some kind of hook for the delivery of meta information that is sent with a call. Any idea, is this possible?

thank you for your advice

Philipp

Answer 1

It turns out that it is very easy with EF 6. All I need is an implementation of IDbCommandInterceptor, which allowed me to extend the provided SQL with a special (SQL) comment. This comment will appear in the database logs and thus enable debugging / tracing from the DBA.

 public class DebugCommentInterceptor : IDbCommandInterceptor { public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { command.CommandText = "/* TRACING INFORMATION GOES HERE */ " + command.CommandText; } public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { command.CommandText = "/* TRACING INFORMATION GOES HERE */ " + command.CommandText; } public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { } public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { } public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { } public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { } } 

To get the above interceptor, I just registered it with the static DbInterception class:

 DbInterception.Add(new DebugCommentInterceptor());