How do I know which Linq statement generated SQL at hand at runtime?

Question

I want to write Entity Framework or LINQ to SQL queries in such a way that when I see a SQL query in SQL Server Profiler, I can quickly determine which LINQ statement created this SQL without going into the debugger and not tracking it. The application will not Use stored procedures that simplify searching by the name of proc.

Any ideas? Is there a way to insert a static line of codes in a LINQ query just to identify the query without effect in the query results?

Update
I am adding this in response to registration suggestions. I don’t want the logging to be done in production all the time only if I need to look at some kind of SQL when there is a problem. Look for a way that has minimal performance overhead. Adding any unique line to each linq statement so that the code can be correlated with SQL very easily?

Answer 1

Here is an extension method that you can use to mark Entity Framework queries:

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's how to use it:

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

It will use the name of the calling method as the name of the request.

You can specify a different name like this:

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

This is what SQL looks 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) 

Answer 2

You can use the ToTraceString method to view the generated SQL. Or use something like LINQPad to test queries.

ToTraceString documentation: http://msdn.microsoft.com/en-us/library/system.data.objects.objectquery.totracestring(v=vs.110).aspx

Answer 3

This is really strange and will have little performance ...

But you could probably do something like comparing a known sentinel value that is not possible for a clustered index:

 var queryId = -123456789; this.Products .Where(p => p.ProductId != -queryId); 

Generated SQL will contain Id:

 DECLARE @p0 Int = -456789 SELECT .... FROM [Product] AS [t0] WHERE [t0].[ProductId] <> @p0 

I totally do not recommend recommending doing something like this in production: D

If the column you are comparing against does not have an index, you will get a terrible table scan that will kill performance.

Answer 4

If you use MiniProfiler, it can provide you with a small panel that shows which SQL is created in different places of your queries. It includes the names of all methods on the stack at the time the request is executed. This is not exactly the same as providing a complete string representation of a LINQ query, but in fact it may be what you are looking for.