在运行时如何知道哪一个Linq语句生成了SQL？ [英] How to know which Linq statement produced the SQL on hand during runtime?

问题描述

我想以一种方式编写Entity Framework或LINQ to SQL查询，所以当我在SQL Server Profiler中看到一个SQL查询时，我可以快速识别哪个LINQ语句产生了SQL，而不进入调试器并跟踪它。该应用程序不会使用存储过程，这样可以通过proc名称进行搜索。

任何想法？有没有办法在LINQ查询中使用代码注入静态字符串，仅用于识别查询而不影响查询结果？

更新

我正在添加这个以响应日志建议。我不想让日志记录在生产中一直运行，只要在需要查看某些SQL的情况下出现问题。寻找一种具有最低成本的性能。为每个linq语句附加一些唯一的字符串，以便代码可以很容易地与SQL相关联？

解决方案

这是一个扩展方法，可用于标记您的实体框架查询：

  public static class ExtensionMethods 
 {
 public static IQueryable< T> SetQueryName< T>（此IQueryable< T>源，
 [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）; 
} 
} 
  

以下是如何使用它：

  context.Table1.SetQueryName（）。其中​​（x => x.C1> 4）
  

它将使用调用方法名作为查询名。

您可以指定另一个这样的名字：

  context.Table1.SetQueryName（搜索数字> 4）。其中（x => ; x.Number> 4）
  

以下是SQL的外观：

  SELECT 
 [Extent1]。[Number] AS [Number] 
 FROM（SELECT 
 [Table1] [Number] AS [Number] 
 FROM [dbo]。[表1] AS [表1]）AS [Extent1] 
 WHERE 
（N'Query名称：搜索数字> 4 'IS NOT NULL）
 AND 
（N'Source：C：\Code\Projects\MyApp\Program.cs（49）'IS NOT NULL）
 AND（[ Extent1]。[Number]> 4）
  

I want to write Entity Framework or LINQ to SQL queries in a way so that when I see a SQL query in SQL Server Profiler, I can quickly identify which LINQ statement produced that SQL, without going into the debugger and tracing it down. The app won't use stored procedures which make it easy to search by proc name.

Any ideas? Is there a way to inject static string with codes in a LINQ query only for the purpose of identifying the query without having an effect in query results?

Update
I am adding this in response to logging suggestions. I don't want to have logging running in production all the time just in the event I need look at some SQL when there's an issue. Looking for a way that has mininal cost to performance. Appending some unique string to every linq statement so that the code can be correlated to the SQL very easily?

解决方案

Here is an extension method you can use to tag your 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 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)

这篇关于在运行时如何知道哪一个Linq语句生成了SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！