Inject debug information into Entity Framework queries

Question

We're using Dapper and EF in our shop, and Dapper proofed to be extremely helpful in debugging queries in SQL server when something went wrong. Instead of just submitting raw SQL, we created a thin decorator that also adds some context information (the origin) as an SQL comment, something like

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

This allows our DBAs and developers to reacy very quickly and find the source of a problem if we have DB calls that are erroneous, or introduce performance hits (we have hundreds of thousands of DB calls per day, so one bad query can cause quite some damage).

We would also like to do this with EF. It doesn't have to be an SQL comment, but some kind of hook in order to supply meta information that is submitted with the call. Any idea whether this is possible?

Thanks for your advice

Philipp

I just discovered that EF 6 provides better interception capabilitie (http://msdn.microsoft.com/en-us/magazine/dn532202.aspx). I didn't get to play with it, but may have time in the near future and would report back here. — Philipp Sumi, Feb 06 '14 at 16:43

score 6 · Accepted Answer · answered Feb 24 '14 at 21:48

Turns out this becomes very easy with EF 6. All that's needed is an implementation of IDbCommandInterceptor, which allowed me to augment the submitted SQL with a custom (SQL) comment. That comment will appear in the database logs, and thus enable debugging / tracing from the DBA side.

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)
    {
    }
}

In order to get the above interceptor operational, I simply registered it with the static DbInterception class:

DbInterception.Add(new DebugCommentInterceptor());

Acknowledging that it's been a few years now… do you remember how you were getting the information about the caller to insert here? That seems more complex than the interception part. (Dapper seems to capture this information when you call their public API, which isn't an option with EF.) — Sixten Otto, Nov 08 '18 at 18:14
I have no idea I'm afraid, and I'm currently not even working on .NET. Hopefully somebody else can chime in - good luck! — Philipp Sumi, Nov 15 '18 at 17:29

score 2 · Answer 2 · answered Feb 05 '20 at 11:06

If you use EF Core, you can use Query tags.

var nearestFriends =
  (from f in context.Friends.TagWith("This is my spatial query!")
  orderby f.Location.Distance(myLocation) descending
  select f).Take(5).ToList();

There is new linq .TagWith(''), It will generate Sql query with:

-- This is my spatial query!

https://learn.microsoft.com/en-us/ef/core/querying/tags

Inject debug information into Entity Framework queries

2 Answers2

Linked