如何查看实体框架生成的SQL ?

(在我的特殊情况下，我使用mysql提供商-如果它重要)

当前回答

在我的EF 6+的情况下，而不是在立即窗口中使用这个来查找查询字符串:

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

我最终不得不使用这个来获得生成的SQL命令:

var sql = ((System.Data.Entity.Infrastructure.DbQuery<<>f__AnonymousType3<string,string,string,short,string>>)query).ToString();

当然，您的匿名类型签名可能有所不同。

HTH.

2015-09-02 18:14:40

其他回答

在EF 4.1中可以执行以下操作:

var result = from x in appEntities
             where x.id = 32
             select x;

System.Diagnostics.Trace.WriteLine(result .ToString());

这将为您提供生成的SQL。

2011-09-01 08:48:47

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;
        }


    }


}

2018-02-13 18:58:22

从实体框架核心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

2022-07-01 21:33:08

实体框架4解决方案

这里的大多数答案都是ef6特异性的。这里有一个给那些还在使用EF4的人。

这个方法替换了@p__linq__0/etc。参数的实际值，所以您可以复制并粘贴输出到SSMS并运行它或调试它。

    /// <summary>
    /// Temporary debug function that spits out the actual SQL query LINQ is generating (with parameters)
    /// </summary>
    /// <param name="q">IQueryable object</param>
    private string Debug_GetSQLFromIQueryable<T>(IQueryable<T> q)
    {
        System.Data.Objects.ObjectQuery oq = (System.Data.Objects.ObjectQuery)q;
        var result = oq.ToTraceString();
        List<string> paramNames = new List<string>();
        List<string> paramVals = new List<string>();
        foreach (var parameter in oq.Parameters)
        {
            paramNames.Add(parameter.Name);
            paramVals.Add(parameter.Value == null ? "NULL" : ("'" + parameter.Value.ToString() + "'"));
        }
        //replace params in reverse order, otherwise @p__linq__1 incorrectly replaces @p__linq__10 for instance
        for (var i = paramNames.Count - 1; i >= 0; i--)
        {
            result = result.Replace("@" + paramNames[i], paramVals[i]);
        }
        return result;
    }

2020-05-26 04:07:25