如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
当前回答
好吧,目前我正在使用Express分析器,缺点是它只适用于MS SQL Server。你可以在这里找到这个工具:https://expressprofiler.codeplex.com/
其他回答
从EF6.1开始,您可以使用拦截器来注册数据库记录器。 请参见“拦截器”和“记录数据库操作”章节
<configuration>
<entityFramework>
<interceptors>
<interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework">
<parameters>
<parameter value="C:\Temp\LogOutput.txt"/>
<parameter value="true" type="System.Boolean"/>
</parameters>
</interceptor>
</interceptors>
</entityFramework>
</configuration>
对于那些使用Entity Framework 6及以上版本的用户,如果你想在Visual Studio中查看输出SQL(就像我做的那样),你必须使用新的日志/拦截功能。
添加下面这行代码将在Visual Studio输出面板中输出生成的SQL(以及其他与执行相关的详细信息):
using (MyDatabaseEntities context = new MyDatabaseEntities())
{
context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
// query the database using EF here.
}
有关登录EF6的更多信息,请参阅这个漂亮的博客系列:http://blog.oneunicorn.com/2013/05/08/ef6-sql-logging-part-1-simple-logging/
注意:确保在DEBUG模式下运行项目。
好吧,目前我正在使用Express分析器,缺点是它只适用于MS SQL Server。你可以在这里找到这个工具:https://expressprofiler.codeplex.com/
如果您还想拥有参数值(不仅是@p_linq_0,而且还有它们的值),您可以使用IDbCommandInterceptor并向ReaderExecuted方法添加一些日志记录。
适用于EF 6.0及以上: 对于那些想要了解更多关于日志功能并添加到已经给出的一些答案的人。
现在可以记录从EF发送到数据库的任何命令。查看从EF 6生成的查询。x,使用DBContext.Database.Log属性
记录什么
- SQL for all different kinds of commands. For example: - Queries, including normal LINQ queries, eSQL queries, and raw queries from methods such as SqlQuery. - Inserts, updates, and deletes generated as part of SaveChanges - Relationship loading queries such as those generated by lazy loading - Parameters - Whether or not the command is being executed asynchronously - A timestamp indicating when the command started executing - Whether or not the command completed successfully, failed by throwing an exception, or, for async, was canceled - Some indication of the result value - The approximate amount of time it took to execute the command. Note that this is the time from sending the command to getting the result object back. It does not include time to read the results.
例子:
using (var context = new BlogContext())
{
context.Database.Log = Console.Write;
var blog = context.Blogs.First(b => b.Title == "One Unicorn");
blog.Posts.First().Title = "Green Eggs and Ham";
blog.Posts.Add(new Post { Title = "I do not like them!" });
context.SaveChangesAsync().Wait();
}
输出:
SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title]
FROM [dbo].[Blogs] AS [Extent1]
WHERE (N'One Unicorn' = [Extent1].[Title]) AND ([Extent1].[Title] IS NOT NULL)
-- Executing at 10/8/2013 10:55:41 AM -07:00
-- Completed in 4 ms with result: SqlDataReader
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title],
[Extent1].[BlogId] AS [BlogId]
FROM [dbo].[Posts] AS [Extent1]
WHERE [Extent1].[BlogId] = @EntityKeyValue1
-- EntityKeyValue1: '1' (Type = Int32)
-- Executing at 10/8/2013 10:55:41 AM -07:00
-- Completed in 2 ms with result: SqlDataReader
UPDATE [dbo].[Posts]
SET [Title] = @0
WHERE ([Id] = @1)
-- @0: 'Green Eggs and Ham' (Type = String, Size = -1)
-- @1: '1' (Type = Int32)
-- Executing asynchronously at 10/8/2013 10:55:41 AM -07:00
-- Completed in 12 ms with result: 1
INSERT [dbo].[Posts]([Title], [BlogId])
VALUES (@0, @1)
SELECT [Id]
FROM [dbo].[Posts]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
-- @0: 'I do not like them!' (Type = String, Size = -1)
-- @1: '1' (Type = Int32)
-- Executing asynchronously at 10/8/2013 10:55:41 AM -07:00
-- Completed in 2 ms with result: SqlDataReader
记录到外部文件:
using (var context = new BlogContext())
{
using (var sqlLogFile = new StreamWriter("C:\\temp\\LogFile.txt"))
{
context.Database.Log = sqlLogFile.Write;
var blog = context.Blogs.First(b => b.Title == "One Unicorn");
blog.Posts.First().Title = "Green Eggs and Ham";
context.SaveChanges();
}
}
更多信息在这里:记录和拦截数据库操作