entity-framework - see - how do i view the sql generated by the entity framework core




How do I view the SQL generated by the Entity Framework? (12)

How do I view the SQL generated by entity framework ?

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


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


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


I am doing integration test, and needed this to debug the generated SQL statement in Entity Framework Core 2.1, so I use DebugLoggerProvider or ConsoleLoggerProvider like so:

[Fact]
public async Task MyAwesomeTest
    {
        //setup log to debug sql queries
        var loggerFactory = new LoggerFactory();
        loggerFactory.AddProvider(new DebugLoggerProvider());
        loggerFactory.AddProvider(new ConsoleLoggerProvider(new ConsoleLoggerSettings()));

        var builder = new DbContextOptionsBuilder<DbContext>();
        builder
            .UseSqlServer("my connection string") //"Server=.;Initial Catalog=TestDb;Integrated Security=True"
            .UseLoggerFactory(loggerFactory);

        var dbContext = new DbContext(builder.Options);

        ........

Here is a sample output from Visual Studio console:


I've just done this:

IQueryable<Product> query = EntitySet.Where(p => p.Id == id);
Debug.WriteLine(query);

And the result shown in the Output:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Code] AS [Code], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[FileName] AS [FileName], 
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[PersistedFiles] AS [Extent2] ON [Extent1].[PersistedFileId] = [Extent2].[Id]
    WHERE [Extent1].[Id] = @p__linq__0

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.


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.


Necromancing.
This page is the first search result when searching for a solution for any .NET Framework, so here as a public service, how it's done in EntityFrameworkCore (for .NET Core 1 & 2):

var someQuery = (
    from projects in _context.projects
    join issues in _context.issues on projects.Id equals issues.ProjectId into tmpMapp
    from issues in tmpMapp.DefaultIfEmpty()
    select issues
) //.ToList()
;

// string sql = someQuery.ToString();
// string sql = Microsoft.EntityFrameworkCore.IQueryableExtensions.ToSql(someQuery);
// string sql = Microsoft.EntityFrameworkCore.IQueryableExtensions1.ToSql(someQuery);
// using Microsoft.EntityFrameworkCore;
string sql = someQuery.ToSql();
System.Console.WriteLine(sql);

And then these extension methods (IQueryableExtensions1 for .NET Core 1.0, IQueryableExtensions for .NET Core 2.0) :

using System;
using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Remotion.Linq.Parsing.Structure;


namespace Microsoft.EntityFrameworkCore
{

    // https://.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework
    // http://rion.io/2016/10/19/accessing-entity-framework-core-queries-behind-the-scenes-in-asp-net-core/

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

            return sql;
        }
    }



    public class IQueryableExtensions1
    {
        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 FieldInfo QueryCompilationContextFactoryField = typeof(Database).GetTypeInfo()
            .DeclaredFields.Single(x => x.Name == "_queryCompilationContextFactory");


        public static string ToSql<TEntity>(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 =
                (IQueryCompilationContextFactory) QueryCompilationContextFactoryField.GetValue(database);
            var queryCompilationContext = queryCompilationContextFactory.Create(false);
            var modelVisitor = (RelationalQueryModelVisitor) queryCompilationContext.CreateQueryModelVisitor();
            modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
            var sql = modelVisitor.Queries.First().ToString();

            return sql;
        }


    }


}

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>

To have the query always handy, without changing code add this to your DbContext and check it on the output window in visual studio.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.Log = (query)=> Debug.Write(query);
    }

Similar to @Matt Nibecker answer, but with this you do not have to add it in your current code, every time you need the query.


Well, I am using Express profiler for that purpose at the moment, the drawback is that it only works for MS SQL Server. You can find this tool here: https://expressprofiler.codeplex.com/


You can do the following:

IQueryable query = from x in appEntities
             where x.id = 32
             select x;

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

or in EF6:

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

That will give you the SQL that was generated.


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





ado.net