首页 文章

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

提问于
浏览
512

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

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

17 回答

  • 1

    如果您使用的是DbContext,则可以执行以下操作来获取SQL:

    var result = from i in myContext.appEntities
                 select new Model
                 {
                     field = i.stuff,
                 };
    var sql = result.ToString();
    
  • 418

    我正在进行集成测试,需要这个来调试Entity Framework Core 2.1中生成的SQL语句,所以我像这样使用 DebugLoggerProviderConsoleLoggerProvider

    [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控制台的示例输出:

    Sample SQL statement output

  • 72

    适用于EF 6.0及以上版本:对于那些想要了解更多有关日志记录功能并添加已经给出的一些答案的人 .

    现在可以记录从EF发送到数据库的任何命令 . 要从EF 6.x查看生成的查询,请使用 DBContext.Database.Log property

    What Gets Logged

    • 用于所有不同类型命令的SQL . 例如:
    • 查询,包括正常的LINQ查询,eSQL查询和来自SqlQuery等方法的原始查询 .
    • 作为SaveChanges的一部分生成的插入,更新和删除
    • 关系加载查询,例如延迟加载生成的查询
    • 参数
    • 命令是否异步执行
    • 指示命令何时开始执行的时间戳
    • 命令是否成功完成,抛出异常失败,或者异步,是否已取消
    • 结果值的一些指示
    • 执行命令所花费的大致时间 . 请注意,这是从发送命令到返回结果对象的时间 . 它没有时间来阅读结果 .

    Example:

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

    Output:

    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
    

    To log to an external file:

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

    更多信息:Logging and Intercepting Database Operations

  • 2

    对于使用Entity Framework 6及更高版本的用户,如果要在Visual Studio中查看输出SQL(就像我一样),则必须使用新的日志记录/拦截功能 .

    添加以下行将在Visual Studio输出面板中吐出生成的SQL(以及其他与执行相关的详细信息):

    using (MyDatabaseEntities context = new MyDatabaseEntities())
    {
        context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
        // query the database using EF here.
    }
    

    有关在这个漂亮的博客系列中登录EF6的更多信息:http://blog.oneunicorn.com/2013/05/08/ef6-sql-logging-part-1-simple-logging/

    注意:确保以DEBUG模式运行项目 .

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

    将返回sql查询 . 使用EntityFramework 6的datacontext工作

  • 17

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

    var result = from x in appEntities
                 where x.id = 32
                 select x;
    
    System.Diagnostics.Trace.WriteLine(result .ToString());
    

    这将为您提供生成的SQL .

  • 2

    对我来说,使用EF6和Visual Studio 2015,我在即时窗口中输入 query ,它给了我生成的SQL语句

  • 8

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

  • 15

    有两种方法:

    • 要查看将生成的SQL,只需调用 ToTraceString() . 您可以将其添加到监视窗口并设置断点,以查看任何LINQ查询的任何给定点的查询 .

    • 您可以将跟踪器附加到您选择的SQL服务器上,该服务器将显示其所有血腥细节的最终查询 . 在MySQL的情况下,跟踪查询的最简单方法是使用 tail -f 尾随查询日志 . 您可以在the official documentation中了解有关MySQL日志记录功能的更多信息 . 对于SQL Server,最简单的方法是使用包含的SQL Server探查器 .

  • 2

    我刚刚这样做了:

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

    我的回答是EF core . 我在configuring DbContext上引用了这个github issue和文档:

    Simple

    覆盖 DbContext 类( YourCustomDbContextas shown hereOnConfiguring 方法以使用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

    这个复杂的案例避免覆盖the DbContext OnConfiguring method.,这在文档中是不鼓励的:"This approach does not lend itself to testing, unless the tests target the full database."

    此复杂案例使用:

    • IServiceCollectionStartupConfigureServices 方法中(而不是覆盖 OnConfiguring 方法;好处是 DbContextILoggerProvider 之间的松散耦合你想要使用)

    • ILoggerProvider 的实现(而不是使用上面显示的 ConsoleLoggerProvider 实现;好处是我们的实现显示了我们如何记录到File(我看不到File Logging Provider shipped with 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;
            }
        } 
    }
    
  • 4

    要使查询始终方便,不更改代码,请将其添加到DbContext并在visual studio的输出窗口中进行检查 .

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

    与@Matt Nibecker的答案类似,但是每次需要查询时,您都不必在当前代码中添加它 .

  • 4

    从EF6.1开始,您可以使用Interceptor来注册数据库 Logger . 请参阅"Interceptors"和"Logging Database Operations"章节到文件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>
    
  • 4

    在我的情况下,对于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 .

  • 819

    如果您想要参数值(不仅是 @p_linq_0 而且还有它们的值),您可以使用IDbCommandInterceptor并将一些日志记录添加到 ReaderExecuted 方法 .

  • 69

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

    您可以执行以下操作:

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

    这将为您提供生成的SQL .

相关问题