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

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


当前回答

有两种方法:

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.

其他回答

IQueryable query = from x in appEntities
                   where x.id = 32
                   select x;
var queryString = query.ToString();

将返回sql查询。使用EntityFramework的数据上下文进行工作

实体框架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;
    }

使用实体框架核心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
}

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

我的答案是针对EF core的。我参考了这个github问题,以及配置DbContext的文档:

简单的

重写DbContext类(YourCustomDbContext)的onconfiguration方法,如下所示使用ConsoleLoggerProvider;你的查询应该记录到控制台:

public class YourCustomDbContext : DbContext
{
    #region DefineLoggerFactory
    public static readonly LoggerFactory MyLoggerFactory
        = new LoggerFactory(new[] {new ConsoleLoggerProvider((_, __) => true, true)});
    #endregion


    #region RegisterLoggerFactory
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseLoggerFactory(MyLoggerFactory); // Warning: Do not create a new ILoggerFactory instance each time                
    #endregion
}

复杂的

这个Complex case避免覆盖DbContext onconfiguration方法。,这在文档中是不鼓励的:“这种方法不适合测试,除非测试的目标是整个数据库。”

这个复杂的案例使用:

启动类ConfigureServices方法中的IServiceCollection (而不是重写onconfiguration方法;好处是DbContext和你想要使用的ILoggerProvider之间的耦合更松散) ILoggerProvider的实现(而不是使用上面所示的ConsoleLoggerProvider实现;好处是我们的实现展示了我们如何将日志记录到文件(我没有看到一个文件日志记录提供程序与EF核心))

是这样的:

public class Startup

    public void ConfigureServices(IServiceCollection services)
    {
        ...
        var lf = new LoggerFactory();
        lf.AddProvider(new MyLoggerProvider());

        services.AddDbContext<YOUR_DB_CONTEXT>(optionsBuilder => optionsBuilder
                .UseSqlServer(connection_string)
                //Using the LoggerFactory 
                .UseLoggerFactory(lf));
        ...
    }
}

这里是MyLoggerProvider的实现(以及它的MyLogger,它将其日志附加到您可以配置的文件中;您的EF Core查询将出现在文件中。)

public class MyLoggerProvider : ILoggerProvider
{
    public ILogger CreateLogger(string categoryName)
    {
        return new MyLogger();
    }

    public void Dispose()
    { }

    private class MyLogger : ILogger
    {
        public bool IsEnabled(LogLevel logLevel)
        {
            return true;
        }

        public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
        {
            File.AppendAllText(@"C:\temp\log.txt", formatter(state, exception));
            Console.WriteLine(formatter(state, exception));
        }

        public IDisposable BeginScope<TState>(TState state)
        {
            return null;
        }
    } 
}