SQL 'comment' that can be read in a sql profiler

Question

I've tried several methods such as using double hyphens, i.e. --THIS IS A COMMENT but when the executed sql is read in a profiler the comment is stripped out leaving only raw SQL that is being performed.

I want to do this to enable rapid identification of queries and their origins when looking at a SQL Profilers output that has over 8000 entries per minute, so something like

--Method signature and an application name

e.g.

--MyMethod(string username) in MyFunkyAppName.

I'm using EntityFramework 4.3 which complicates things even further with linq to entities and a smattering of linq to sql thrown in for good measure.

EDIT: I'm aware of solutions to add a dodgy where clause or use anonymous properties to identify things such as Clever tricks to find specific LINQ queries in SQL Profiler but I'm hoping for a far less hacky approach or perhaps a generic one.

score 7 · Answer 1 · answered Dec 21 '16 at 13:24

Here is an extension method you can use to tag your Entity Framework queries. It uses the WHERE clause, but shouldn't impair performance.

public static class ExtensionMethods
{
  public static IQueryable<T> SetQueryName<T>(this IQueryable<T> source,
    [CallerMemberName] String name = null,
    [CallerFilePath] String sourceFilePath = "",
    [CallerLineNumber] Int32 sourceLineNumber = 0)
  {
    var expr = Expression.NotEqual(Expression.Constant("Query name: " + name), Expression.Constant(null));
    var param = Expression.Parameter(typeof(T), "param");
    var criteria1 = Expression.Lambda<Func<T, Boolean>>(expr, param);

    expr = Expression.NotEqual(Expression.Constant($"Source: {sourceFilePath} ({sourceLineNumber})"), Expression.Constant(null));
    var criteria2 = Expression.Lambda<Func<T, Boolean>>(expr, param);

    return source.Where(criteria1).Where(criteria2);
  }
}

Here is how to use it:

context.Table1.SetQueryName().Where(x => x.C1 > 4)

It will use the calling method name as the query name.

You can specify another name like this:

context.Table1.SetQueryName("Search for numbers > 4").Where(x => x.Number > 4)

Here is how the SQL will look like:

SELECT 
    [Extent1].[Number] AS [Number]
    FROM (SELECT 
    [Table1].[Number] AS [Number]
    FROM [dbo].[Table1] AS [Table1]) AS [Extent1]
    WHERE
      (N'Query name: Search for numbers > 4' IS NOT NULL)
      AND
      (N'Source: C:\Code\Projects\MyApp\Program.cs (49)' IS NOT NULL)
      AND ([Extent1].[Number] > 4)

SQL 'comment' that can be read in a sql profiler

1 Answers1