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

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


当前回答

您可以执行以下操作:

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。

其他回答

我刚刚做了这个:

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

从EF6.1开始,您可以使用拦截器来注册数据库记录器。 请参见“拦截器”和“记录数据库操作”章节

<configuration>
  <entityFramework>
    <interceptors> 
      <interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework"> 
        <parameters> 
          <parameter value="C:\Temp\LogOutput.txt"/> 
          <parameter value="true" type="System.Boolean"/> 
        </parameters> 
      </interceptor> 
    </interceptors>
  </entityFramework>
</configuration>

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

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

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