Skip to content
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

Closed
JalpeshVadgama opened this issue Sep 7, 2016 · 83 comments
Closed

Comments

@JalpeshVadgama
Copy link

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

@JalpeshVadgama
Copy link
Author

@JalpeshVadgama JalpeshVadgama 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 Is there any way to get SQL Statement generated from the Entity framework core Provide a simple way to get SQL statements generated from EF queries Sep 7, 2016
@JalpeshVadgama
Copy link
Author

@JalpeshVadgama JalpeshVadgama commented Nov 18, 2016

@JalpeshVadgama
Copy link
Author

@JalpeshVadgama JalpeshVadgama commented Dec 12, 2016

Guys is there any update when this will be implemented?

@ErikEJ
Copy link
Contributor

@ErikEJ ErikEJ commented Dec 12, 2016

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

@simbo1905
Copy link

@simbo1905 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
Copy link

@mguinness mguinness commented Apr 29, 2017

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

Ricardo Peres' blogged about getting it using reflection.

@d1mnewz
Copy link

@d1mnewz 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
Copy link
Contributor

@manigandham 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);
}
@JalpeshVadgama
Copy link
Author

@JalpeshVadgama JalpeshVadgama commented May 12, 2017

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

@manigandham
Copy link
Contributor

@manigandham 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
Copy link

@simbo1905 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
Copy link
Contributor

@manigandham 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
Copy link

@abhishekbdutta 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
Copy link

@hvdijk 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
Copy link

@mguinness 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
Copy link

@gordon-matt 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
Copy link
Member

@bricelam bricelam commented Jul 21, 2017

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

@smitpatel
Copy link
Member

@smitpatel 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
Copy link

@mguinness mguinness commented Aug 15, 2017

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

@smitpatel
Copy link
Member

@smitpatel 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
Copy link

@nickgaudreau 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
Copy link
Member

@smitpatel smitpatel commented Sep 5, 2017

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

@xaviergxf
Copy link

@xaviergxf 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 added a commit that referenced this issue Dec 19, 2019
Design meeting feedback on #19334 for #6482

* Formatting of the relational query string can now be overridden by providers
* SQL Server overrides this to create runnable `Parameter` declarations
* Tested by:
  * Executing the DbCommand we create when using `CreateDbCommand`
  * Executing a DbCommand created externally and given the generated query string
ajcvickers added a commit that referenced this issue Dec 23, 2019
Design meeting feedback on #19334 for #6482

* Formatting of the relational query string can now be overridden by providers
* SQL Server overrides this to create runnable `Parameter` declarations
* Tested by:
  * Executing the DbCommand we create when using `CreateDbCommand`
  * Executing a DbCommand created externally and given the generated query string
ajcvickers added a commit that referenced this issue Dec 24, 2019
ajcvickers added a commit that referenced this issue Dec 24, 2019
ajcvickers added a commit that referenced this issue Dec 26, 2019
@roji
Copy link
Member

@roji roji commented Jan 27, 2020

Providers-beware for the query string parameter syntax etc?

@JasonLoKiSmith
Copy link

@JasonLoKiSmith JasonLoKiSmith commented Jan 27, 2020

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
Copy link
Member

@ajcvickers ajcvickers commented Jan 27, 2020

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

@ytodorov
Copy link

@ytodorov ytodorov commented Feb 18, 2020

Hello,

will this feature be available in ef core 3.1?

@ajcvickers
Copy link
Member

@ajcvickers ajcvickers commented Feb 18, 2020

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

@ytodorov
Copy link

@ytodorov ytodorov commented Feb 18, 2020

Thanks for the quick response!

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

@RamType0
Copy link

@RamType0 RamType0 commented May 26, 2020

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
Copy link
Member

@roji roji commented May 26, 2020

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

@mguinness
Copy link

@mguinness mguinness commented May 26, 2020

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
Copy link
Member

@roji roji commented May 26, 2020

@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
Copy link

@ocelot-pl ocelot-pl commented Jun 7, 2020

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
Copy link
Contributor

@ErikEJ ErikEJ commented Jun 7, 2020

@ocelot-pl is that not for EF 6?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.