C# Entity Framework 4.0：如何查看 SaveChanges 方法的 SQL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11578374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Entity Framework 4.0: How to see SQL statements for SaveChanges method
提问by LCJ
I used to use the context.Log for tracing LINQ to SQL generated SQL Statements as shown in Sql Server Query Visualizer – Cannot see generated SQL Query
我曾经使用 context.Log 来跟踪 LINQ to SQL 生成的 SQL 语句，如Sql Server Query Visualizer – 无法看到生成的 SQL 查询
context.Log = new OutputWindowWriter();
For EF, is there anything similar and easy like the above approach?
对于 EF，是否有类似上述方法的类似和简单的方法？
采纳答案by Alex Klaus
In general you can hook up the built-in tracer or any logger by simple
一般来说，您可以通过简单的方式连接内置跟踪器或任何记录器
context.Database.Log = msg => Trace.WriteLine(msg);
in the DbContext constructor. See more in MSDN. Some other approaches from MS are here(all based on DataContext.Logproperty).
在 DbContext 构造函数中。在MSDN 中查看更多信息。MS 的其他一些方法在这里（均基于DataContext.Log属性）。
Talking about the Clutchsolution mentioned by Nate, it doesn't work with EF v6 (see this bug-report).
谈到Nate 提到的Clutch解决方案，它不适用于 EF v6（请参阅此错误报告）。
REFERENCES
参考
- Logging and Intercepting Database Operations (EF6 Onwards)
- Logging and Intercepting Database Operations
回答by Alexander Efimov
I believe you can use ToTraceString method of the ObjectQuery instance you have. Another approach would be to look at the IntelliTrace of Visual Studion as it logs SQL going out of your project.
我相信您可以使用您拥有的 ObjectQuery 实例的 ToTraceString 方法。另一种方法是查看 Visual Studion 的 IntelliTrace，因为它记录了项目中的 SQL。
回答by Jonathan Rupp
The EF Tracing Providercan output all SQL statements executed as traces. You can also use it to add your own logging if you want. Here's some code you could put in the constructor of your context class (this is for a DBContext, but the tweak to use ObjectContext should be pretty apparent):
该EF跟踪提供程序可以输出跟踪执行的所有SQL语句。如果需要，您还可以使用它来添加自己的日志记录。下面是一些您可以放入上下文类的构造函数中的代码（这是针对 DBContext，但使用 ObjectContext 的调整应该很明显）：
// enable logging all queries executed by EF
var cx = ((IObjectContextAdapter)this).ObjectContext; // change to var cx = this; if using ObjectContext.
cx.EnableTracing();
cx.Connection.GetTracingConnections().ToList().ForEach(
c =>
{
c.CommandExecuting += (s, e) => Log(e);
c.CommandFailed += (s, e) => Log(e);
c.CommandFinished += (s, e) => Log(e);
});
回答by Judo
Lots of solutions to this, but simplest in code is just call ToString() on the IQueryable of the LINQ statement.
对此有很多解决方案，但最简单的代码就是在 LINQ 语句的 IQueryable 上调用 ToString()。
var query = db.Employees.Where(x => x.ID = 1); //query will be an IQueryable object
var sql = query.ToString();
This is only in EF4.1 on (previously calling ToTraceString on the ObjectQuery was the way to achieve this).
这仅在 EF4.1 上（以前在 ObjectQuery 上调用 ToTraceString 是实现此目的的方法）。
回答by Nate Cook
The Clutch.Diagnostics.EntityFramework (available in NuGet) works perfectly for me, and it's simpler than the EFTracingProvider.
Clutch.Diagnostics.EntityFramework（在 NuGet 中可用）非常适合我，而且它比 EFTracingProvider 更简单。
UPDATE for EF 6:
EF 6 更新：
Starting with Entity Framework 6, anytime Entity Framework sends a command to the database this command can be intercepted by application code. This is most commonly used for logging SQL, but can also be used to modify or abort the command.
Specifically, EF includes:
* A Log property for the context similar to DataContext.Log in LINQ to SQL.
* A mechanism to customize the content and formatting of the output sent to the log.
* Low-level building blocks for interception giving greater control/flexibility.
从 Entity Framework 6 开始，任何时候 Entity Framework 向数据库发送命令，该命令都可以被应用程序代码拦截。这最常用于记录 SQL，但也可用于修改或中止命令。
具体来说，EF 包括：
* 类似于 LINQ to SQL 中的 DataContext.Log 上下文的 Log 属性。
* 一种自定义发送到日志的输出的内容和格式的机制。
* 用于拦截的低级构建块，提供更大的控制/灵活性。
回答by fiat
Expanding on Nate's answer for EF6, the NLogCommandInterceptorseen in Logging and Intercepting Database Operationsonly shows the CommandText.
扩展 Nate 对 EF6 的回答，NLogCommandInterceptor在日志记录和拦截数据库操作中看到的仅显示命令文本。
If there was some particular parameter value that caused that commandText to fail, the parameters values aren't emitted to the log. In my case I wanted to log what values were causing Foreign Key violations.
如果存在导致该 commandText 失败的某些特定参数值，则不会将参数值发送到日志中。就我而言，我想记录导致外键违规的值。
This can be improved by altering NLogCommandInterceptor's LogIfErrormethod like thus
这可以通过LogIfError像这样改变 NLogCommandInterceptor 的方法来改进
private void LogIfError<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
{
if (interceptionContext.Exception != null)
{
var commandDumper = new DbCommandDumper(command);
Log.Warn(Command failed:\r\n{0}", commandDumper.GetLogDump());
// Exception will get logged further up the stack
}
}
where the DbCommandDumperclass reconstructs the DbCommand into TSQL that can be replayed into a test database.
其中DbCommandDumper类将DbCommand重构为可以重播到测试数据库中的 TSQL。
