Provide a simple way to get SQL statements generated from EF queries

[JalpeshVadgama]

Hello Team,

First of all congratulations for this awesome product. We are using it in very extensively. I have one question there. Is there any way to get SQL statement generated in Entity Framework Core. We want to see SQL statements generated and based on that optimize the queries that we are using.

I tried searching it on documentation as well online but have not found anything. Please let me know if there is a way to do this. Right now the only way to profile the database. But in some cases it costs us more when we use Azure SQL databases.

Thank you for anticipation in advance!

Further technical details

EF Core version: 1.0.0
Operating system: Windows 10 Professional
Visual Studio version: Visual Studio 2015

Other details about my project setup: ASP.NET Core, Angular js 1.5 and Entity Framework Core 1.0 with Azure SQL database.

[JalpeshVadgama]

Logging will not help it is a huge application so there will be lot many things there in logging.Its very hard to find all information there.I want to have something like below.

http://www.dotnetjalps.com/2012/12/Where-I-can-find-SQL-Generated-by-Entity-framework.html

[JalpeshVadgama]

Since 1.1 is released -https://blogs.msdn.microsoft.com/dotnet/2016/11/16/announcing-entity-framework-core-1-1/ Should we have this available?

[JalpeshVadgama]

Guys is there any update when this will be implemented?

[ErikEJ]

There is some code available here: #5106 (comment)

[simbo1905]

+1 being able to see the generated SQL. Java ORMs typically let you see it at startup else have a build tool option so that you can generate the SQL on the build. there are many good use cases for this such as being able to import it into data migration tooling or to investigate query performance. right now it would be very helpful to see the differences on the official demo app which I am trying to port from SQLServer to Postgres 👍

[mguinness]

+1 This was available in EF6 using context.Database.Log.

Ricardo Peres' blogged about getting it using reflection.

[d1mnewz]

Also facing this problem, earlier we used Database.Log to investigate performance of EF auto-generated queries, but now we are moving to aspnetcore1.1 and entityframeworkcore.
Is there already proper way of logging sql queries?

[manigandham]

Here's the documentation: https://docs.microsoft.com/en-us/ef/core/miscellaneous/logging

ILoggerFactory will be automatically injected into your DbContext constructor and you can assign it using optionsBuilder.UseLoggerFactory to share the logging config of the rest of your app.

If you need something more custom, you can use an implementation of ILoggerProvider assigned to a new ILoggerFactory which can then be assigned to the DbContext to log the statements and internal logic.

For example: create this logger provider:

using Microsoft.Extensions.Logging;
using System;
using System.IO;

namespace EFLogging
{
    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;
            }
        } 
    }
}

Then hook it up in your dbcontext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var lf = new LoggerFactory();
    lf.AddProvider(new MyLoggerProvider());
    optionsBuilder.UseLoggerFactory(lf);
}

[JalpeshVadgama]

Log is a option. But I think it should be something similar to EF6

[manigandham]

What? Logging is all there is, how else would you see the SQL? EF6 and EFCore just have different ways to do the configuration.

Did you look at the code above? You can use a different logging setup for your db context to so you can see sql statements separately from the rest of your app.