New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
dotnetjalps opened this Issue Sep 7, 2016 · 38 comments

Comments

Projects
None yet
@dotnetjalps

dotnetjalps commented Sep 7, 2016

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.

@dotnetjalps

This comment has been minimized.

dotnetjalps commented Sep 7, 2016

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

@divega divega modified the milestones: 1.1.0, Backlog Sep 7, 2016

@divega divega changed the title from Is there any way to get SQL Statement generated from the Entity framework core to Provide a simple way to get SQL statements generated from EF queries Sep 7, 2016

@dotnetjalps

This comment has been minimized.

dotnetjalps commented Nov 18, 2016

@dotnetjalps

This comment has been minimized.

dotnetjalps commented Dec 12, 2016

Guys is there any update when this will be implemented?

@ErikEJ

This comment has been minimized.

Contributor

ErikEJ commented Dec 12, 2016

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

@simbo1905

This comment has been minimized.

simbo1905 commented Apr 13, 2017

+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

This comment has been minimized.

mguinness commented Apr 29, 2017

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

Ricardo Peres' blogged about getting it using reflection.

@d1mnewz

This comment has been minimized.

d1mnewz commented May 10, 2017

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

This comment has been minimized.

Contributor

manigandham commented May 12, 2017

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);
}
@dotnetjalps

This comment has been minimized.

dotnetjalps commented May 12, 2017

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

@manigandham

This comment has been minimized.

Contributor

manigandham commented May 12, 2017

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

This comment has been minimized.

simbo1905 commented May 13, 2017

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

@manigandham

This comment has been minimized.

Contributor

manigandham commented May 13, 2017

@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

This comment has been minimized.

abhishekbdutta commented May 21, 2017

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

This comment has been minimized.

hvdijk commented May 27, 2017

@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

This comment has been minimized.

mguinness commented Jun 7, 2017

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 comment has been minimized.

gordon-matt commented Jul 21, 2017

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

This comment has been minimized.

Member

bricelam commented Jul 21, 2017

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

@smitpatel

This comment has been minimized.

Contributor

smitpatel commented Aug 15, 2017

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

This comment has been minimized.

mguinness commented Aug 15, 2017

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

@smitpatel

This comment has been minimized.

Contributor

smitpatel commented Aug 15, 2017

@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

This comment has been minimized.

nickgaudreau commented Sep 2, 2017

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

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

Thanks

@smitpatel

This comment has been minimized.

Contributor

smitpatel commented Sep 5, 2017

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

@xaviergxf

This comment has been minimized.

xaviergxf commented Dec 1, 2017

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

@ajcvickers

This comment has been minimized.

Member

ajcvickers commented Dec 1, 2017

@xaviergxf If you explicitly set the schema in your model, then it should be used in generated queries. For example, modelBuilder.HasDefaultSchema("dbo");

@camilin87

This comment has been minimized.

camilin87 commented Mar 6, 2018

I created this nuget to address this issue until it gets a permanent solution https://www.tddapps.com/2018/03/06/seamlessly-debug-entity-framework-core-sql-commands/

@mguinness

This comment has been minimized.

mguinness commented Mar 16, 2018

@CutieDaisy

This comment has been minimized.

CutieDaisy commented May 9, 2018

This no longer works with .net core 2.1 rc.

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

This comment has been minimized.

CutieDaisy commented May 9, 2018

can someone please help ?

@carlreinke

This comment has been minimized.

carlreinke commented May 13, 2018

This seems to work for 2.1-rc1:

public static class IQueryableExtensions
{
    private static readonly FieldInfo _queryCompilerField = typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields.Single(x => x.Name == "_queryCompiler");

    private static readonly TypeInfo _queryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();

    private static readonly FieldInfo _queryModelGeneratorField = _queryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_queryModelGenerator");

    private static readonly FieldInfo _databaseField = _queryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");

    private static readonly PropertyInfo _dependenciesProperty = typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");

    public static string ToSql<TEntity>(this IQueryable<TEntity> queryable)
        where TEntity : class
    {
        if (!(queryable is EntityQueryable<TEntity>) && !(queryable is InternalDbSet<TEntity>))
            throw new ArgumentException();

        var queryCompiler = (IQueryCompiler)_queryCompilerField.GetValue(queryable.Provider);
        var queryModelGenerator = (IQueryModelGenerator)_queryModelGeneratorField.GetValue(queryCompiler);
        var queryModel = queryModelGenerator.ParseQuery(queryable.Expression);
        var database = _databaseField.GetValue(queryCompiler);
        var queryCompilationContextFactory = ((DatabaseDependencies)_dependenciesProperty.GetValue(database)).QueryCompilationContextFactory;
        var queryCompilationContext = queryCompilationContextFactory.Create(false);
        var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
        modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
        return modelVisitor.Queries.Join(Environment.NewLine + Environment.NewLine);
    }
}
@jaredcnance

This comment has been minimized.

jaredcnance commented Jun 13, 2018

This would be a really great feature for frameworks that build on top of EF Core (JsonApiDotNetCore for example). I'm currently using the above solutions in our automated tests to confirm the abstractions we build on top of EF Core actually generate the expected SQL.

@JasonLoKiSmith

This comment has been minimized.

JasonLoKiSmith commented Jun 21, 2018

As suggested by @carlreinke. this does work even in the full release of Core 2.1.1 . I have included it with the required namespaces:

using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace SomeApplicationNamespace
{
    public static class IQueryableHelper
    {
        private static readonly FieldInfo _queryCompilerField = typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields.Single(x => x.Name == "_queryCompiler");

        private static readonly TypeInfo _queryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();

        private static readonly FieldInfo _queryModelGeneratorField = _queryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_queryModelGenerator");

        private static readonly FieldInfo _databaseField = _queryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");

        private static readonly PropertyInfo _dependenciesProperty = typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");

        public static string ToSql<TEntity>(this IQueryable<TEntity> queryable)
            where TEntity : class
        {
            if (!(queryable is EntityQueryable<TEntity>) && !(queryable is InternalDbSet<TEntity>))
                throw new ArgumentException();

            var queryCompiler = (IQueryCompiler)_queryCompilerField.GetValue(queryable.Provider);
            var queryModelGenerator = (IQueryModelGenerator)_queryModelGeneratorField.GetValue(queryCompiler);
            var queryModel = queryModelGenerator.ParseQuery(queryable.Expression);
            var database = _databaseField.GetValue(queryCompiler);
            var queryCompilationContextFactory = ((DatabaseDependencies)_dependenciesProperty.GetValue(database)).QueryCompilationContextFactory;
            var queryCompilationContext = queryCompilationContextFactory.Create(false);
            var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
            modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
            return modelVisitor.Queries.Join(Environment.NewLine + Environment.NewLine);
        }
    }
}
@DalSoft

This comment has been minimized.

DalSoft commented Jul 14, 2018

Is there any reason why this extension can't just be added to EF? This is really useful as SQL Dependency Tracking is broken in ASP.NET Core 2.1, so I use this to track queries. I prefer this to logging everything anyway as it ensures I can opt out of queries that I don't want to log.

@ajcvickers ajcvickers removed this from the Backlog milestone Jul 25, 2018

@nathfy

This comment has been minimized.

nathfy commented Jul 27, 2018

Was tearing my hair out with this, I wanted sql logging to the VS debug window. In the end this solved it:
https://blogs.msdn.microsoft.com/dbrowne/2017/09/22/simple-logging-for-ef-core/#

so the context is:

public MyDbContext(DbContextOptions<MyDbContext> options): base(options)
{
    this.ConfigureLogging(s => Debug.WriteLine(s));
}

Which isn't too far from the original Database.Log = s => Debug.WriteLine(s);

Hope this helps people looking for this in the future - thanks for the work on EF core!

@ajcvickers

This comment has been minimized.

Member

ajcvickers commented Jul 30, 2018

We discussed this in triage and there are several complications with incorporating this:

  • It doesn't always work--that is, per info from @smitpatel, there are some cases where the SQL generated at runtime will not be the same as indicated here.
  • Given this, some similar API might still be useful, but it should probably be a TryGet... type method that can get you the SQL for simple cases--i.e. those cases that don't dynamically generate multiple queries base on the results of previous queries.
  • However, it may make more sense to expose the query plan in a way that can be more easily consumed. The query plan is what people on the EF team use to determine what is going to be executed.

So in general, we're not opposed to something like this in the product, but it needs more design to make it into something that won't cause more confusion than help.

@ajcvickers ajcvickers added this to the Backlog milestone Jul 30, 2018

@AlissonRS

This comment has been minimized.

AlissonRS commented Sep 10, 2018

Hey @manigandham thanks for your help.

formatter(state, exception) doesn't seem to get the query at all. It looks it returns the SQL to Entities representation (e.g the expression tree), but not the actual query.

I also tried using ILoggerFactory, but No registration for type ILoggerFactory could be found.. I'm missing something, but I don't know what.

image

@wizofaus

This comment has been minimized.

wizofaus commented Oct 23, 2018

@JasonLoKiSmith's suggestion above works well enough if you want to generate a full non-parameterised SQL query, but isn't so useful if you just want, for instance, the SQL for the "WHERE" clause, and in a format that is suitable for parameterising.

@kishoretvk

This comment has been minimized.

kishoretvk commented Oct 27, 2018

is there a way to log parameters in with generated sql , enablesenstivelogging(true), does not log parameters for stored procs or generated sql from linq to sql with Ef core.

@divega

This comment has been minimized.

Member

divega commented Oct 27, 2018

@kishoretvk interesting. I don't think that is intentional. Could you please create a separate issue for that? a repro would be great.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment