Entity framework adding Custom method

Question

We are using newrelic for Database performance monitoring. Our problems is Entity framework is generating SQL and we are having issues tracking where this SQL query was generated in our code base for example

Lets say the below query is causing performance issues. How can I modify the generated sql query and add custom comment on it for example

SELECT 
    ? AS [C1], 
    [GroupBy1].[K1] AS [Destination], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[Destination] AS [K1], 
        SUM([Extent1].[SearchCount]) AS [A1]
        FROM [Flight].[Item] AS [Extent1]
        WHERE ([Extent1].[Origin] IN (?, ?)) AND ( NOT ((@p__linq__0 = ?) AND ([Extent1].[IsDomesticTurkish] = ?))) AND ([Extent1].[DestinationCity] IN (?, ?, ?, ?, ?, ?, ?, ?)) AND ([Extent1].[DestinationCity] IS NOT NULL)
        GROUP BY [Extent1...More…

So How can I do something like this var DbContext.MyDbSet.Where(myWhereEx).AddCustomComment("Hello this is a custom comment I will write it from my code")

After that The output will be like this

SELECT 
    ? AS [C1], 
    [GroupBy1].[K1] AS [Destination], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[Destination] AS [K1], 
        SUM([Extent1].[SearchCount]) AS [A1]
        FROM [Flight].[Item] AS [Extent1]
        WHERE ([Extent1].[Origin] IN (?, ?)) AND ( NOT ((@p__linq__0 = ?) AND ([Extent1].[IsDomesticTurkish] = ?))) AND ([Extent1].[DestinationCity] IN (?, ?, ?, ?, ?, ?, ?, ?)) AND ([Extent1].[DestinationCity] IS NOT NULL)
        GROUP BY [Extent1...More…

--Hello this is a custom comment I will write it from my code

My question is how to implement AddCustomComment and inside AddCustomComment modify the generated sql before it goes to SQL server

Answer 1

I'm not aware about any EF built-in functionality to do that, but here is one idea how this might be done.

You can use thread-local variable to store comment (so each thread has it's own copy of that variable) and use Entity Framework command interceptor to add comment to the command before executing it, then clear comment variable when command has been executed. Here is sample implementation:

class EFCommentInterceptor : IDbCommandInterceptor {
    private static readonly ThreadLocal<string> _comment = new ThreadLocal<string>();

    internal static void SetComment(string comment) {
        _comment.Value = comment;
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) {
        AddComment(command);
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) {
        _comment.Value = null;
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) {
        AddComment(command);
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) {
        _comment.Value = null;
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) {
        AddComment(command);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) {
        _comment.Value = null;
    }

    private void AddComment(DbCommand command) {
        if (!String.IsNullOrWhiteSpace(_comment.Value))
            command.CommandText += "\r\n\r\n-- " + _comment.Value;
    }
}

Then add extension method like this:

static class QueryableExtensions {
    public static IQueryable<T> WithComment<T>(this IQueryable<T> query, string comment) {
        EFCommentInterceptor.SetComment(comment);
        return query;
    }
}

Register interceptor:

DbInterception.Add(new EFCommentInterceptor());

And use it:

using (var ctx = new MyContext()) {
    ctx.MyDbSet.Where(c = c.MyColumn > 1).WithComment("Hello this is a custom comment I will write it from my code").ToArray();
    ctx.MyDbSet.Take(10).ToArray(); // no comment here
    ctx.MyDbSet.Take(10).WithComment("Again with comment").ToArray();
}