如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
如何查看实体框架生成的SQL ?
(在我的特殊情况下,我使用mysql提供商-如果它重要)
当前回答
从实体框架核心5.0+开始,可以简单地覆盖DbContext中的onconfiguration方法一次用于日志记录。这也适用于Single()或Any()查询。
日志记录到调试窗口:
public class ExampleDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
// using System.Diagnostics;
optionsBuilder.LogTo(message => Debug.WriteLine(message));
}
}
登录控制台:
public class ExampleDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.LogTo(Console.WriteLine);
}
}
更多详细信息,包括日志级别和过滤:https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/simple-logging
其他回答
适用于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();
}
}
更多信息在这里:记录和拦截数据库操作
从实体框架核心5.0+开始,可以简单地覆盖DbContext中的onconfiguration方法一次用于日志记录。这也适用于Single()或Any()查询。
日志记录到调试窗口:
public class ExampleDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
// using System.Diagnostics;
optionsBuilder.LogTo(message => Debug.WriteLine(message));
}
}
登录控制台:
public class ExampleDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.LogTo(Console.WriteLine);
}
}
更多详细信息,包括日志级别和过滤:https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/simple-logging
在EF 4.1中可以执行以下操作:
var result = from x in appEntities
where x.id = 32
select x;
System.Diagnostics.Trace.WriteLine(result .ToString());
这将为您提供生成的SQL。
如果您正在使用DbContext,您可以执行以下操作来获取SQL:
var result = from i in myContext.appEntities
select new Model
{
field = i.stuff,
};
var sql = result.ToString();
EF Core 5.0+
这个期待已久的功能在EF Core 5.0中可用!这是每周的状态更新:
var query = context.Set<Customer>().Where(c => c.City == city); Console.WriteLine(query.ToQueryString()) results in this output when using the SQL Server database provider: DECLARE p0 nvarchar(4000) = N'London'; SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region] FROM [Customers] AS [c] WHERE [c].[City] = @__city_0 Notice that declarations for parameters of the correct type are also included in the output. This allows copy/pasting to SQL Server Management Studio, or similar tools, such that the query can be executed for debugging/analysis.
哦吼! !
(注意:你需要使用Microsoft.EntityFrameworkCore;)