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.

[simbo1905]

We want to see the SQL without having to run the application. Particularly the DDL to create the tables.

[manigandham]

@simbo1905

that's a different issue, if you're using a code-first approach and have all the entities ready, then you can use EF migrations to generate the sql script.

https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/migrations#create-an-initial-migration

https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet

dotnet ef migrations script [arguments] [options]

[abhishekbdutta]

The above solution is not logging the DDL statements, what can be done to do. What i want is that in my production environment need to log all the DDL and DML statements getting fired during the migration process

[hvdijk]

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

EF6 allowed the SQL to be obtained without actually executing anything. That's useful in some cases where that SQL will then be manipulated or executed manually. Logging only covers some of the use cases.

[mguinness]

Unit testing could be another reason why obtaining the generated SQL might be useful. If I wanted to ensure that changes in the database provider doesn't alter the SQL to something that was unexpected.

[gordon-matt]

This would be useful for me as well. In EF6, I was using a custom IDatabaseInitializer<TContext> which would first check if the database existed. If it did indeed exist, it would generate the database script as follows:

var script = ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();

and then it would cycle through all the CREATE TABLE statements in there and execute the ones for tables that didn't exist in the existing database. It was a bit of an ugly hack, but it worked well for years. The reason I needed this was because I have a plugin architecture and need to create new tables at runtime. EF migrations is not helpful in this regard.

I have just recently added a request for a better solution for EF Core. See issue #9238

EDIT: I think I misunderstood what SQL generation you guys were talking about. I just realized this is about the SELECT queries and not database creation.

[bricelam]

FYI, the code for an ObjectContext.CreateDatabaseScript() equivelant is in #2943.

[smitpatel]

This is small extension method you can include in your code to get SQL for a query. It works in 2.0.0 bits. It uses internal methods so can break in future releases.

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 = (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 = ((DatabaseDependencies)DatabaseDependenciesField.GetValue(database)).QueryCompilationContextFactory;
        var queryCompilationContext = queryCompilationContextFactory.Create(false);
        var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
        modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
        var sql = modelVisitor.Queries.First().ToString();

        return sql;
    }
}

[mguinness]

@smitpatel That looks very similar to the work by Ricardo Peres, perhaps you should give some attribution?

[smitpatel]

@mguinness - I found that in linked issue #9414 which references different source. I corrected the code to work for 2.0 on that issue. Posted here since there were more watchers on this.

[nickgaudreau]

@smitpatel Hi, could you provide the code for type : DatabaseDependencies

That line var queryCompilationContextFactory = ((DatabaseDependencies)DatabaseDependenciesField.GetValue(database)).QueryCompilationContextFactory;

Thanks

[smitpatel]

@nickgaudreau - DatabaseDependencies is in namespace Microsoft.EntityFrameworkCore.Storage

[xaviergxf]

Did anyone knows how to create queries with its explicit schema name? I need this to use with SqlDependency which has some crazy restrictions.

[roji]

Providers-beware for the query string parameter syntax etc?

[JasonLoKiSmith]

I have been following this thread now for over a year and it seems like a solution is not coming for this, maybe the gents have higher priorities? Anyway I have resolved to buying Linqpad which works great and does actually use the Core 3.1 driver to generate queries which is cool.

Please note i am in no way affiliated with Linqpad, i am just giving some advice :)

[ajcvickers]

@JasonLoKiSmith This is implemented and merged. See https://blog.oneunicorn.com/2020/01/12/toquerystring/

[ytodorov]

Hello,

will this feature be available in ef core 3.1?

[ajcvickers]

@ytodorov No, this will be shipped with EF Core 5.0 in November.

[ytodorov]

Thanks for the quick response!

I've been waiting for this feature for so much time.

[RamType0]

EF Core 5.0 requires .NET Standard 2.1,so this feature never become available in .NET Framework 4.8 ,or Unity, isn't it ?

[roji]

@RamType-0 that's correct (for. NET Framework, I don't know what Unity's plans are).

[mguinness]

https://forum.unity.com/threads/net-5-support.839890/#post-5548063

.NET 5 is not scheduled for release until November 2020. We would not be doing any work towards supporting it until that release happens and is supported in Mono. So, sometime in 2021 at the earliest.

[roji]

@mguinness note that EF Core 5.0 will support .NET Standard 2.1, not just .NET 5.0. This means that if Unity do support .NET Core 3.1, then it should be possible to use EF Core 5.0 (again, I have no concrete knowledge here).

[ocelot-pl]

Description of the cause of the EF error is often insufficient and you can waste hours searching for the real cause. The following code allows you to write to the log SQL generated during migration to a file. Thanks to its analysis, the problem can be solved in a few minutes.

internal sealed class Configuration : DbMigrationsConfiguration<MyContext>
{
    protected override void Seed(MyDatabaseContext context)
    {
#if DEBUG
        context.Database.Log = s => MyLog.Add(s);
#endif
        ...

        base.Seed(context);
    }
}

where

public static class MyLog
{
    public static void Add(string s) 
    {
         using (StreamWriter sw = File.AppendText(@"c:\temp\sql-log.log")) { sw.WriteLine(s); } 
    }
}

Thanks to this solution, when starting the database migration in debug mode, we can accurately trace what happened in the database. Thanks to this, within a few minutes I found a solution to the migration problem, which I lost several hours to run.

An analogous structure can be used in the code, the repository, anywhere, if we want to track SQL:

using (MyDatabaseContext context = new MyDatabaseContext())
{
#if DEBUG
        context.Database.Log = s => MyLog.Add(s);
#endif

        // query the database using EF here.
        ....
}

I prefere logging to a file, but of course you can always use:

context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

[ErikEJ]

@ocelot-pl is that not for EF 6?