SQL Management Studio => Tools => SQL Server分析器

File =>新建跟踪…

使用Template =>空白

事件选择=> T-SQL

左侧检查:SP.StmtComplete

列筛选器可用于选择特定的ApplicationName或DatabaseName

启动该概要文件运行，然后触发查询。

点击这里获取来源信息

您可以执行以下操作:

IQueryable query = from x in appEntities
             where x.id == 32
             select x;

var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

或EF6:

var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
            .ToTraceString();

或EF6.3+:

var sql = ((dynamic)flooringStoresProducts).Sql;

这将为您提供生成的SQL。

使用实体框架核心3.x进行日志记录

实体框架核心通过日志系统发出SQL。只有一些小技巧。您必须指定一个ILoggerFactory和一个筛选器。下面是本文中的一个例子

创建工厂:

var loggerFactory = LoggerFactory.Create(builder =>
{
    builder
    .AddConsole((options) => { })
    .AddFilter((category, level) =>
        category == DbLoggerCategory.Database.Command.Name
        && level == LogLevel.Information);
});

告诉DbContext在onconfigure方法中使用工厂:

optionsBuilder.UseLoggerFactory(_loggerFactory);

从这里，您可以获得更复杂的信息，并与Log方法挂钩以提取有关执行SQL的详细信息。有关完整讨论，请参阅文章。

public class EntityFrameworkSqlLogger : ILogger
{
    #region Fields
    Action<EntityFrameworkSqlLogMessage> _logMessage;
    #endregion
    #region Constructor
    public EntityFrameworkSqlLogger(Action<EntityFrameworkSqlLogMessage> logMessage)
    {
        _logMessage = logMessage;
    }
    #endregion
    #region Implementation
    public IDisposable BeginScope<TState>(TState state)
    {
        return default;
    }
    public bool IsEnabled(LogLevel logLevel)
    {
        return true;
    }
    public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
    {
        if (eventId.Id != 20101)
        {
            //Filter messages that aren't relevant.
            //There may be other types of messages that are relevant for other database platforms...
            return;
        }
        if (state is IReadOnlyList<KeyValuePair<string, object>> keyValuePairList)
        {
            var entityFrameworkSqlLogMessage = new EntityFrameworkSqlLogMessage
            (
                eventId,
                (string)keyValuePairList.FirstOrDefault(k => k.Key == "commandText").Value,
                (string)keyValuePairList.FirstOrDefault(k => k.Key == "parameters").Value,
                (CommandType)keyValuePairList.FirstOrDefault(k => k.Key == "commandType").Value,
                (int)keyValuePairList.FirstOrDefault(k => k.Key == "commandTimeout").Value,
                (string)keyValuePairList.FirstOrDefault(k => k.Key == "elapsed").Value
            );
            _logMessage(entityFrameworkSqlLogMessage);
        }
    }
    #endregion
}

虽然这里有很好的答案，但没有一个完全解决了我的问题(我希望从任何IQueryable DbContext中获得整个SQL语句，包括参数)。下面的代码就是这样做的。它是来自谷歌的代码片段的组合。我只用EF6+测试过。

说句题外话，这项任务花的时间比我想象的要长得多。恕我直言，实体框架中的抽象有点太多了。

首先是使用。您需要显式引用'System.Data.Entity.dll'。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.Entity.Core.Objects;
using System.Data.Entity;
using System.Data;
using System.Data.Entity.Infrastructure;
using System.Reflection;

下面的类将一个IQueryable转换为一个DataTable。根据您的需要修改:

public class EntityFrameworkCommand
{
    DbContext Context;

    string SQL;

    ObjectParameter[] Parameters;

    public EntityFrameworkCommand Initialize<T>(DbContext context, IQueryable<T> query)
    {
        Context = context;
        var dbQuery = query as DbQuery<T>;
        // get the IInternalQuery internal variable from the DbQuery object
        var iqProp = dbQuery.GetType().GetProperty("InternalQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
        var iq = iqProp.GetValue(dbQuery, null);
        // get the ObjectQuery internal variable from the IInternalQuery object
        var oqProp = iq.GetType().GetProperty("ObjectQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
        var objectQuery = oqProp.GetValue(iq, null) as ObjectQuery<T>;
        SQL = objectQuery.ToTraceString();
        Parameters = objectQuery.Parameters.ToArray();
        return this;
    }

    public DataTable GetData()
    {
        DataTable dt = new DataTable();
        var connection = Context.Database.Connection;
        var state = connection.State;
        if (!(state == ConnectionState.Open))
            connection.Open();
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = SQL;
            foreach (var p in Parameters)
            {
                var param = cmd.CreateParameter();
                param.Name = "@" + p.Name;
                param.Value = p.Value;
                cmd.Parameters.Add(param);
            }
            using (var da = DbProviderFactories.GetFactory(connection).CreateDataAdapter())
            {
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
        }
        if (!(state == ConnectionState.Open))
            connection.Close();
        return dt;
    }
}

要使用，只需如下所示调用它:

var context = new MyContext();
var data = ....//Query, return type can be anonymous
    .AsQueryable();
var dt = new EntityFrameworkCommand()
    .Initialize(context, data)
    .GetData();

适用于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();
   }
}

更多信息在这里:记录和拦截数据库操作

好吧，目前我正在使用Express分析器，缺点是它只适用于MS SQL Server。你可以在这里找到这个工具:https://expressprofiler.codeplex.com/