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

File =>新建跟踪…

使用Template =>空白

事件选择=> T-SQL

左侧检查:SP.StmtComplete

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

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

点击这里获取来源信息

我正在做集成测试，需要在实体框架核心2.1中调试生成的SQL语句，所以我使用DebugLoggerProvider或ConsoleLoggerProvider，如下所示:

[Fact]
public async Task MyAwesomeTest
    {
        //setup log to debug sql queries
        var loggerFactory = new LoggerFactory();
        loggerFactory.AddProvider(new DebugLoggerProvider());
        loggerFactory.AddProvider(new ConsoleLoggerProvider(new ConsoleLoggerSettings()));

        var builder = new DbContextOptionsBuilder<DbContext>();
        builder
            .UseSqlServer("my connection string") //"Server=.;Initial Catalog=TestDb;Integrated Security=True"
            .UseLoggerFactory(loggerFactory);

        var dbContext = new DbContext(builder.Options);

        ........

下面是Visual Studio控制台的输出示例:

我刚刚做了这个:

IQueryable<Product> query = EntitySet.Where(p => p.Id == id);
Debug.WriteLine(query);

结果显示在Output:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Code] AS [Code], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[FileName] AS [FileName], 
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[PersistedFiles] AS [Extent2] ON [Extent1].[PersistedFileId] = [Extent2].[Id]
    WHERE [Extent1].[Id] = @p__linq__0

Necromancing。 当搜索任何。net框架的解决方案时，这个页面是第一个搜索结果，所以这里作为一个公共服务，它是如何在EntityFrameworkCore(用于。net Core 1和2)中完成的:

var someQuery = (
    from projects in _context.projects
    join issues in _context.issues on projects.Id equals issues.ProjectId into tmpMapp
    from issues in tmpMapp.DefaultIfEmpty()
    select issues
) //.ToList()
;

// string sql = someQuery.ToString();
// string sql = Microsoft.EntityFrameworkCore.IQueryableExtensions.ToSql(someQuery);
// string sql = Microsoft.EntityFrameworkCore.IQueryableExtensions1.ToSql(someQuery);
// using Microsoft.EntityFrameworkCore;
string sql = someQuery.ToSql();
System.Console.WriteLine(sql);

然后这些扩展方法(IQueryableExtensions1用于。net Core 1.0, IQueryableExtensions用于。net Core 2.0):

using System;
using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Remotion.Linq.Parsing.Structure;


namespace Microsoft.EntityFrameworkCore
{

    // https://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework
    // http://rion.io/2016/10/19/accessing-entity-framework-core-queries-behind-the-scenes-in-asp-net-core/

    public static class IQueryableExtensions
    {
        private static readonly TypeInfo QueryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();

        private static readonly FieldInfo QueryCompilerField = typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields
            .First(x => x.Name == "_queryCompiler");

        private static readonly PropertyInfo NodeTypeProviderField =
            QueryCompilerTypeInfo.DeclaredProperties.Single(x => x.Name == "NodeTypeProvider");

        private static readonly MethodInfo CreateQueryParserMethod =
            QueryCompilerTypeInfo.DeclaredMethods.First(x => x.Name == "CreateQueryParser");

        private static readonly FieldInfo DataBaseField =
            QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");

        private static readonly PropertyInfo DatabaseDependenciesField =
            typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");

        public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
        {
            if (!(query is EntityQueryable<TEntity>) && !(query is InternalDbSet<TEntity>))
            {
                throw new ArgumentException("Invalid query");
            }

            var queryCompiler = (QueryCompiler) QueryCompilerField.GetValue(query.Provider);
            var nodeTypeProvider = (INodeTypeProvider) NodeTypeProviderField.GetValue(queryCompiler);
            var parser = (IQueryParser) CreateQueryParserMethod.Invoke(queryCompiler, new object[] {nodeTypeProvider});
            var queryModel = parser.GetParsedQuery(query.Expression);
            var database = DataBaseField.GetValue(queryCompiler);
            var databaseDependencies = (DatabaseDependencies) DatabaseDependenciesField.GetValue(database);
            var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
            var modelVisitor = (RelationalQueryModelVisitor) queryCompilationContext.CreateQueryModelVisitor();
            modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
            var sql = modelVisitor.Queries.First().ToString();

            return sql;
        }
    }



    public class IQueryableExtensions1
    {
        private static readonly TypeInfo QueryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();

        private static readonly FieldInfo QueryCompilerField = typeof(EntityQueryProvider).GetTypeInfo()
            .DeclaredFields
            .First(x => x.Name == "_queryCompiler");

        private static readonly PropertyInfo NodeTypeProviderField =
            QueryCompilerTypeInfo.DeclaredProperties.Single(x => x.Name == "NodeTypeProvider");

        private static readonly MethodInfo CreateQueryParserMethod =
            QueryCompilerTypeInfo.DeclaredMethods.First(x => x.Name == "CreateQueryParser");

        private static readonly FieldInfo DataBaseField =
            QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");

        private static readonly FieldInfo QueryCompilationContextFactoryField = typeof(Database).GetTypeInfo()
            .DeclaredFields.Single(x => x.Name == "_queryCompilationContextFactory");


        public static string ToSql<TEntity>(IQueryable<TEntity> query) where TEntity : class
        {
            if (!(query is EntityQueryable<TEntity>) && !(query is InternalDbSet<TEntity>))
            {
                throw new ArgumentException("Invalid query");
            }

            var queryCompiler = (IQueryCompiler) QueryCompilerField.GetValue(query.Provider);

            var nodeTypeProvider = (INodeTypeProvider) NodeTypeProviderField.GetValue(queryCompiler);
            var parser =
                (IQueryParser) CreateQueryParserMethod.Invoke(queryCompiler, new object[] {nodeTypeProvider});
            var queryModel = parser.GetParsedQuery(query.Expression);
            var database = DataBaseField.GetValue(queryCompiler);
            var queryCompilationContextFactory =
                (IQueryCompilationContextFactory) QueryCompilationContextFactoryField.GetValue(database);
            var queryCompilationContext = queryCompilationContextFactory.Create(false);
            var modelVisitor = (RelationalQueryModelVisitor) queryCompilationContext.CreateQueryModelVisitor();
            modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
            var sql = modelVisitor.Queries.First().ToString();

            return sql;
        }


    }


}

有两种方法:

To view the SQL that will be generated, simply call ToTraceString(). You can add it into your watch window and set a breakpoint to see what the query would be at any given point for any LINQ query. You can attach a tracer to your SQL server of choice, which will show you the final query in all its gory detail. In the case of MySQL, the easiest way to trace the queries is simply to tail the query log with tail -f. You can learn more about MySQL's logging facilities in the official documentation. For SQL Server, the easiest way is to use the included SQL Server profiler.

在不更改代码的情况下，使查询始终方便 将它添加到你的DbContext中，并在visual studio的输出窗口中检查它。

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.Log = (query)=> Debug.Write(query);
    }

类似于@Matt Nibecker的答案，但有了这个答案，你不必每次需要查询时都将它添加到当前代码中。