entity-framework core see - How do I view the SQL generated by the Entity Framework?





8 Answers

For those using Entity Framework 6 and up, if you want to view the output SQL in Visual Studio (like I did) you have to use the new logging/interception functionality.

Adding the following line will spit out the generated SQL (along with additional execution-related details) in the Visual Studio output panel:

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

More information about logging in EF6 in this nifty blog series: http://blog.oneunicorn.com/2013/05/08/ef6-sql-logging-part-1-simple-logging/

Note: Make sure you are running your project in DEBUG mode.

executed ef get

How do I view the SQL generated by entity framework ?

(In my particular case I'm using the mysql provider - if it matters)




Starting with EF6.1 you can use Interceptors to register a database logger. See chapters "Interceptors" and "Logging Database Operations" to a File 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>



Applicable for EF 6.0 and above: For those of you wanting to know more about the logging functionality and adding to the some of the answers already given.

Any command sent from the EF to the database can now be logged. To view the generated queries from EF 6.x, use the DBContext.Database.Log property

What Gets Logged

 - SQL for all different kinds of commands. For example:
    - Queries, including normal LINQ queries, eSQL queries, and raw queries from methods such as SqlQuery.
    - Inserts, updates, and deletes generated as part of SaveChanges
    - Relationship loading queries such as those generated by lazy loading
 - Parameters
 - Whether or not the command is being executed asynchronously
 - A timestamp indicating when the command started executing
 - Whether or not the command completed successfully, failed by throwing an exception, or, for async, was canceled
 - Some indication of the result value
 - The approximate amount of time it took to execute the command. Note that this is the time from sending the command to getting the result object back. It does not include time to read the results.

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

More info here: Logging and Intercepting Database Operations




My answer addresses EF core. I reference this github issue, and the docs on configuring DbContext:

Simple

Override the OnConfiguring method of your DbContext class (YourCustomDbContext) as shown here to use a ConsoleLoggerProvider; your queries should log to the console:

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

This Complex case avoids overriding the DbContext OnConfiguring method. , which is discouraged in the docs: "This approach does not lend itself to testing, unless the tests target the full database."

This Complex case uses:

  • The IServiceCollection in Startup class ConfigureServices method (instead of overriding the OnConfiguring method; the benefit is a looser coupling between the DbContext and the ILoggerProvider you want to use)
  • An implementation of ILoggerProvider (instead of using the ConsoleLoggerProvider implementation shown above; benefit is our implementation shows how we would log to File (I don't see a File Logging Provider shipped with EF Core))

Like this:

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

Here's the implementation of a MyLoggerProvider (and its MyLogger which appends its logs to a File you can configure; your EF Core queries will appear in the file.)

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



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

Will return the sql query. Working using datacontext of EntityFramework 6




In my case for EF 6+, instead of using this in the Immediate Window to find the query string:

var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query).ToTraceString();

I ended up having to use this to get the generated SQL command:

var sql = ((System.Data.Entity.Infrastructure.DbQuery<<>f__AnonymousType3<string,string,string,short,string>>)query).ToString();

Of course your anonymous type signature might be different.

HTH.




For me, using EF6 and Visual Studio 2015 I entered query in the immediate window and it gave me the generated SQL Statement




If you want to have parameter values (not only @p_linq_0 but also their values) too, you can use IDbCommandInterceptor and add some logging to ReaderExecuted method.






Related