entity framework - 如何查看實體框架生成的SQL?




entity-framework ado.net (11)

我如何查看由實體框架生成的SQL?

(在我的具體情況下,我使用mysql提供程序 - 如果它很重要)


Necromancing。
此頁面是搜索任何.NET Framework解決方案時的第一個搜索結果,因此在此處作為公共服務,它是如何在EntityFramework Core (針對.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);

然後這些擴展方法(用於.NET Core 1.0的IQueryableExtensions1,用於.NET Core 2.0的IQueryableExtensions):

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://.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;
        }


    }


}

在我的情況下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。


如果您想擁有參數值(不僅@p_linq_0而且還有它們的值),則可以使用IDbCommandInterceptor並將一些日誌記錄添加到ReaderExecuted方法。


對於我來說,使用EF6和Visual Studio 2015,我在即時窗口中輸入了query ,它給了我生成的SQL語句


從EF6.1開始,您可以使用攔截器來註冊數據庫記錄器。 here請參閱章節“攔截器”和“記錄數據庫操作”到文件

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

您可以在EF 4.1中執行以下操作:

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

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

這會給你生成的SQL。


我剛剛做到了這一點:

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

輸出中顯示的結果

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

我的答案解決了EF 核心 。 我引用這個github問題 ,以及有關配置DbContext的文檔:

簡單

如此處所示重寫DbContext類的OnConfiguring方法( YourCustomDbContext )以使用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
            //.UseSqlServer("...");
    #endregion
}

複雜

這個複雜的情況避免了重寫DbContext OnConfiguring方法。 ,這在文檔中是不鼓勵的:“除非測試是針對完整數據庫,否則這種方法不適用於測試。”

這個複雜的案例使​​用:

  • Startup類中的IServiceCollectionConfigureServices方法(而不是重寫OnConfiguring方法;優點是DbContext和您想要使用的ILoggerProvider之間的DbContext
  • 一個ILoggerProvider的實現(而不是使用上面顯示的ConsoleLoggerProvider實現;好處是我們的實現顯示了我們如何登錄到File(我沒有看到EF Core附帶文件記錄提供程序 ))

喜歡這個:

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

為了讓查詢總是方便的,不用修改代碼,將它添加到DbContext中,並在Visual Studio的輸出窗口中檢查它。

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

類似於@Matt Nibecker的回答,但有了這個,你不必在當前代碼中添加它,每次你需要查詢。


適用於EF 6.0及以上版本:您想了解更多關於日誌記錄功能的內容,並添加一些已經給出的答案。

現在可以記錄從EF發送到數據庫的任何命令。 要從EF 6.x查看生成的查詢,請使用DBContext.Database.Log property

記錄什麼

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

更多信息在這裡: 記錄和攔截數據庫操作


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

將返回sql查詢。 使用EntityFramework的datacontext工作6





ado.net