How do I view the SQL generated by the Entity Framework?

Question

How do I view the SQL generated by entity framework ?

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

Wartime

This artice from MSDN Magazine describes some profiling options for Entity Framework 4 – Carrollcarronade

The linked "duplicate" question is for LINQ to SQL, so its not actually a duplicate. – Inflect

When running under debugger, IntelliTrace shows SQL queries made, albeit without their results. – Medullated

If you're interested in seeing the SQL just while development, you can use LINQPad. When you run a LINQ query in the results there will be an SQL tab which shows the executed SQL statement. For mySQL you'll have to install a driver. I don't have a mySQL database available, but it should work. – Ambidexterity

The amount of complicated answers to this question is making me reconsider EF. This seems like a very basic function in an ORM. – Cardigan

Most methods I've seen don't seem to work for me but I did get something like this to finally work using EF 6.4 var sql = ((dynamic)result).ToString(); – Yulandayule

Answer 1

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

or in EF6.3+:

var sql = ((dynamic)flooringStoresProducts).Sql;

That will give you the SQL that was generated.

Allrud

You won't get SQL for queries ending with .Single(), .Count(), .Any(), etc. that way. – Ainslie

That is because after running .Single() your object is no more IQueryable I guess. – Adrienadriena

with EF6, I could get it only with reflection. but first, I had to convert result to System.Data.Entity.Infrastructure.DbQuery<T>, then get internal property InternalQuery as (System.Data.Entity.Internal.Linq.InternalQuery<T>), and only then, use ToTraceString() – Sodium

add reference to System.Data.Entity, System.Data.Objects.ObjectQuery exist in the above dll – Grappa

will this ToTraceString() return the final query (with the parameters already plugged in) or will it return the query without the parameters? ie: will the query have variables like p__linq__i? – Jequirity

also System.Data.Objects is no longer available in EF 6 – Jequirity

@Jequirity - re:EF 6, System.Data.Objects isn't removed.. it's actually here: System.Data.Entity.Core.Objects.ObjectQuery – Cache

In EF6 you can just do result.ToString() – Jitter

@ScottChamberlain It returns the query, but without the binding values. – Sideband

I'd like to get the sql commands for my add and update operations, It seems that these methods work only with select command? – Afforest

@NickBerardi, I guess that it should be where x.id == 32? Your code is =. – Bancroft

@NickBerardi This does not work in EF Core. What's the alternative for EF Core? – Centrality

Does not seem to work when the query is a DTO such as IQueryable<MyDto> Exception Message = "Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[My.Controllers.MyDto]' to type 'System.Data.Entity.Core.Objects.ObjectQuery'." – Episode

In EF6 using ((System.Data.Entity.Core.Objects.ObjectQuery)query).ToTraceString() I have "threw an exception of type 'System.InvalidCastException'" message. why? Using 'query.ToString()' I can see all tree, but not SQL. – Biogeochemistry

That doesn't work in EF6; I had to change the cast to DbQuery<T>, e.g.: var sql = ((System.Data.Entity.Infrastructure.DbQuery<MyEntity>)query).Sql; – Spode

@Spode I do not see how that is going to give you the parameter value though. ( what is .Sql for ? ) – Roentgen

I had to use (System.Data.Entity.Infrastructure.DbQuery<T>)query).Sql in EF 6.3.0, where T is the same as the IQueryable<T> – Centralization

i have a query with parameters; and i can't see the real query with compiled parameters; ToString() and InternalQuery displays a query with parameters placeholders – Cervical

your EF 6 didn't work for me on EFCore 6 but this did var sql = (query as Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable<MyEntity>)?.DebugView?.Query; – Remittee

Answer 2

For those using Entity Framework 6 and up (not in EF CORE - see comment below), 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.

Jive

This answer deserves more love (if you're using EF6+) - great debug addition, just add it in on the DBContext constructor (this.Database.Log = ...) – Bewick

@vittore - actually, I'm using EF6.1.0; and as you said its not working when building stating: Cannot create delegate with 'System.Diagnostics.Debug.WriteLine(string)' because it has a Conditional attribute. However the above answer works fine! – Forethoughtful

@StephenLautier I have to rephrase my comment or remove it, in fact without lambda it will work only from Command Window. – Hypoglossal

Make sure you are running your project in DEBUG MODE, check if the item "Debug" has selected on combobox of Output pane and also check if your debug is not redirecting to Immediate (Tools > Options > Debugging > Redirect all Output Window text to Immediate Window) – Novara

is there a way to get this to include the variable values directly within the generated sql? Bit of a pain with the bigger ones. – Iridescence

YES. or using something like Serilog etc. context.Database.Log = s => Log.Verbose(s); – Knoll

For those using a DataContext but stumbled here anyway, you can use DataContext.Log msdn.microsoft.com/en-us/library/… – Ernest

@Matt Nibecker This does not work in EF Core. What's the alternative for EF Core? – Centrality

Unfortunately whatever you use assign to .Log will be called multiple times per query (open conn, first part of sql, more parts of sql, close conn). – Buckjumper

quite useful! Looks like context.Database.Log = Debug.WriteLine; doesn't work.. you sepecifically have to go like ... s => ...(s); , the way it is in your answer! – Martinmartina

Hm, it doesn't work when I debug my unit test. I put the statement in the constructor of my DbContext. – Durwood

WARNING: I implemented this with the intention of it only running in development. When we deployed to our testing environment, we started to abruptly see memory leaks in the IIS Worker Process. After memory profiling, we realized even explicit GC wasn't collecting the entity context objects anymore (yes, they were in using statements). Removing this line returned all to normal. So, while this is a great tool, make sure you only build it into your app for development. – Dioptric

Or just add it to your concrete constructor: public MyDbContext() : base("name=MyDbContext") { Database.Log = sql => System.Diagnostics.Debug.WriteLine(sql); } – Dianthe

How can this be configured using ASP.NET Core? The DbContext is injected automatically and we don't control the creation of it. – Popish

how do you do this for core? – Franni

Everyone looking for a solution for ef core. See the answer of Rosdi Kasim further down here: https://mcmap.net/q/53782/-how-do-i-view-the-sql-generated-by-the-entity-framework – Steed

For EF Core, mine was already logging the SQL to the output window by default - I just hadn't checked! – Boccioni

For anyone still using VB.NET, the syntax is Me.Database.Log = Sub(s) Logger.Debug(s) (in my case, I added it directly on the constructor and I used a Logger instead of outputting to the console). – Tillford

.NET 6 > Error CS1656: Cannot assign to 'Log' because it is a 'method group' – Nope

Answer 3

Starting with EF6.1 you can use Interceptors to register a database logger. See chapters "Interceptors" and "Logging Database Operations" to a File here

<configuration>
  <entityFramework>
    <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>
  </entityFramework>
</configuration>

Ushaushant

Blog post on the subject blog.oneunicorn.com/2014/02/09/… – Chelsiechelsy

Precision, It's under : <configuration> <entityFramework> <interceptors> ... </interceptors> </entityFramework> </configuration> – Jsandye

Answer 4

If you are using a DbContext, you can use .ToString() to get the SQL:

var result = from i in myContext.appEntities
             select new Model
             {
                 field = i.stuff,
             };
var sql = result.ToString();

Trampoline

ToString() will give you the query with variables in it, like p__linq__0, instead of the final values (eg: 34563 instead of p__linq__0) – Jequirity

Answer 5

EF Core 5.0+

This loooong-awaited feature is available in EF Core 5.0! This is from the weekly status updates:

var query = context.Set<Customer>().Where(c => c.City == city);
Console.WriteLine(query.ToQueryString())

results in this output when using the SQL Server database provider:

DECLARE p0 nvarchar(4000) = N'London';

SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName],
[c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone],
[c].[PostalCode], [c].[Region]
FROM [Customers] AS [c]
WHERE [c].[City] = @__city_0

Notice that declarations for parameters of the correct type are also included in the output. This allows copy/pasting to SQL Server Management Studio, or similar tools, such that the query can be executed for debugging/analysis.

woohoo!!!

(Note: You will need using Microsoft.EntityFrameworkCore;)

Crt

@ChristianFindlay here is the interface with the method and here is where it is included in EntityFrameworkQueryableExtensions. Are you using EF Core 5.0? – Crt

If you are checking for the query in a watch or inspect window and you haven't added using Microsoft.EntityFrameworkCore; to your class you can call the extension method directly by using: Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(query); – Hennessy

Answer 6

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

Sphygmo

How to do this in EFCore 7? – Boschvark

Answer 7

You can do the following in EF 4.1:

var result = from x in appEntities
             where x.id = 32
             select x;

System.Diagnostics.Trace.WriteLine(result .ToString());

That will give you the SQL that was generated.

Boathouse

Point of fact, I believe this only works when the query returns an anonymous type. If it returns a custom type, the ToString() output is the namespace of that custom type. For example, if the above code was select new CustomType { x = x.Name }, the returned value would be something like Company.Models.CustomType instead of the generated SQL. – Rollicking

This technique produces System.Data.Objects.ObjectQuery``1[MyProject.Models.Product] for me. – Escritoire

@CarlG System.Data.Objects.ObjectQuery is not EF 4.1 (DbContext). Using DbContext it would be System.Data.Entity.Infrastructure.DbQuery`1[MyProject.Models.Product] which indeed outputs it's SQL on a call to "ToString()" – Ainslie

This will give you the SQL that was generated, where, in the output window? which option from the dropdown? – Comedienne

Answer 8

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

Marvamarve

So...there is no begginers way of doing it? – Enyo

@JuanDelaCruz I simplified my answer; try the simple alternative – Marvamarve

Thank you for posting this. I an flabbergasted that there is no longer a way to do it in the immediate window in .NET Core. I don't want to write code, I just want to debug it. SQL profiler is obviously an option but that is still more complicated than it used to be – Brunel

Answer 9

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.

Sonia

Dang shame that EF Core / 5|6 no longer even has this .Log method/property. :( – Deweydewhirst

Answer 10

There are two ways:

1. To view the SQL that will be generated, simply call ToTraceString(). You can add it into your watch window and set a breakpoint to see what the query would be at any given point for any LINQ query.
2. You can attach a tracer to your SQL server of choice, which will show you the final query in all its gory detail. In the case of MySQL, the easiest way to trace the queries is simply to tail the query log with tail -f. You can learn more about MySQL's logging facilities in the official documentation. For SQL Server, the easiest way is to use the included SQL Server profiler.

Cornstarch

The ToTraceString of what ? – Wartime

The ObjectQuery, as Nick noted right after I posted my response. – Cornstarch

SQL Server Profiler captures the first 4000 characters, but EF queries can be much longer than that. – Narcosis

Answer 11

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:

Patrilineage

DebugLoggerPrivider and ConsoleLoggerProvider seem to exist only in .NET Core: learn.microsoft.com/en-us/dotnet/api/… – Libove

What is in new ConsoleLoggerSettings()? – Hedonism

Answer 12

SQL Management Studio => Tools => SQL Server profiler

File => New Trace...

Use the Template => Blank

Event selection => T-SQL

Lefthandside check for: SP.StmtComplete

Column filters can be used to select a specific ApplicationName or DatabaseName

Start that profile running then trigger the query.

Click here for Source information

Yuzik

sorry thats just for SQL server, not MySQL – Yuzik

This doesn't work if you are connecting to a server where you not an admin – Nihil

Answer 13

While there are good answers here, none solved my problem completely (I wished to get the entire SQL statement, including Parameters, from the DbContext from any IQueryable. The following code does just that. It is a combination of code snippets from Google. I have only tested it with EF6+.

Just an aside, this task took me way longer than I thought it would. Abstraction in Entity Framework is a bit much, IMHO.

First the using. You will need an explicit reference to 'System.Data.Entity.dll'.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.Entity.Core.Objects;
using System.Data.Entity;
using System.Data;
using System.Data.Entity.Infrastructure;
using System.Reflection;

The following class converts an IQueryable into a DataTable. Modify as your need may be:

public class EntityFrameworkCommand
{
    DbContext Context;

    string SQL;

    ObjectParameter[] Parameters;

    public EntityFrameworkCommand Initialize<T>(DbContext context, IQueryable<T> query)
    {
        Context = context;
        var dbQuery = query as DbQuery<T>;
        // get the IInternalQuery internal variable from the DbQuery object
        var iqProp = dbQuery.GetType().GetProperty("InternalQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
        var iq = iqProp.GetValue(dbQuery, null);
        // get the ObjectQuery internal variable from the IInternalQuery object
        var oqProp = iq.GetType().GetProperty("ObjectQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
        var objectQuery = oqProp.GetValue(iq, null) as ObjectQuery<T>;
        SQL = objectQuery.ToTraceString();
        Parameters = objectQuery.Parameters.ToArray();
        return this;
    }

    public DataTable GetData()
    {
        DataTable dt = new DataTable();
        var connection = Context.Database.Connection;
        var state = connection.State;
        if (!(state == ConnectionState.Open))
            connection.Open();
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = SQL;
            foreach (var p in Parameters)
            {
                var param = cmd.CreateParameter();
                param.Name = "@" + p.Name;
                param.Value = p.Value;
                cmd.Parameters.Add(param);
            }
            using (var da = DbProviderFactories.GetFactory(connection).CreateDataAdapter())
            {
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
        }
        if (!(state == ConnectionState.Open))
            connection.Close();
        return dt;
    }
}

To use, simply call it as below:

var context = new MyContext();
var data = ....//Query, return type can be anonymous
    .AsQueryable();
var dt = new EntityFrameworkCommand()
    .Initialize(context, data)
    .GetData();

Alithia

Great answer.Instead of new SqlParameter, you should use DbProviderFactories.GetFactory(connection).CreateParameter() so that it is compatible with other database providers. – Radiative

Answer 14

Use Logging with Entity Framework Core 3.x

Entity Framework Core emits SQL via the logging system. There are only a couple of small tricks. You must specify an ILoggerFactory and you must specify a filter. Here is an example from this article

Create the factory:

var loggerFactory = LoggerFactory.Create(builder =>
{
    builder
    .AddConsole((options) => { })
    .AddFilter((category, level) =>
        category == DbLoggerCategory.Database.Command.Name
        && level == LogLevel.Information);
});

Tell the DbContext to use the factory in the OnConfiguring method:

optionsBuilder.UseLoggerFactory(_loggerFactory);

From here, you can get a lot more sophisticated and hook into the Log method to extract details about the executed SQL. See the article for a full discussion.

public class EntityFrameworkSqlLogger : ILogger
{
    #region Fields
    Action<EntityFrameworkSqlLogMessage> _logMessage;
    #endregion
    #region Constructor
    public EntityFrameworkSqlLogger(Action<EntityFrameworkSqlLogMessage> logMessage)
    {
        _logMessage = logMessage;
    }
    #endregion
    #region Implementation
    public IDisposable BeginScope<TState>(TState state)
    {
        return default;
    }
    public bool IsEnabled(LogLevel logLevel)
    {
        return true;
    }
    public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
    {
        if (eventId.Id != 20101)
        {
            //Filter messages that aren't relevant.
            //There may be other types of messages that are relevant for other database platforms...
            return;
        }
        if (state is IReadOnlyList<KeyValuePair<string, object>> keyValuePairList)
        {
            var entityFrameworkSqlLogMessage = new EntityFrameworkSqlLogMessage
            (
                eventId,
                (string)keyValuePairList.FirstOrDefault(k => k.Key == "commandText").Value,
                (string)keyValuePairList.FirstOrDefault(k => k.Key == "parameters").Value,
                (CommandType)keyValuePairList.FirstOrDefault(k => k.Key == "commandType").Value,
                (int)keyValuePairList.FirstOrDefault(k => k.Key == "commandTimeout").Value,
                (string)keyValuePairList.FirstOrDefault(k => k.Key == "elapsed").Value
            );
            _logMessage(entityFrameworkSqlLogMessage);
        }
    }
    #endregion
}

Unknowable

Answer 15

Starting from Entity Framework Core 5.0+ one can simply override the OnConfiguring method in the DbContext once for logging. This works also for Single() or Any() queries.

For logging to debug window:

public class ExampleDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // using System.Diagnostics;
        optionsBuilder.LogTo(message => Debug.WriteLine(message));
    }
}

For logging to console:

public class ExampleDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.LogTo(Console.WriteLine);
    }
}

See here for more details including log levels and filtering: https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/simple-logging

Nissensohn

After the .LogTo you can tell it to log SensitiveData, for dev, or it will hide parameterized values. Here is a filtered log example: EFCore 6 .LogTo(Console.Writeline, new [] {DbLoggerCategory.Database.Command.Name }, LogLevel.Information) .Enable SensitiveDataLogging(); – Eo

Answer 16

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://mcmap.net/q/53782/-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;
        }


    }


}

Restivo

I am using EF Core 2.0.1 and the above suggestion results in: System.InvalidCastException: 'Unable to cast object of type Microsoft.EntityFrameworkCore.Query.Internal.InMemoryQueryModelVisitor' to type ''Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor'` for the line: var modelVisitor = (RelationalQueryModelVisitor) queryCompilationContext.CreateQueryModelVisitor(); – Heterogeneous

@ChrisWolf if you follow the original author's gist you can find somebody who provided an updated version of that extension method. Worked for me. – Jara

Answer 17

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/

Grasmere

Answer 18

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

Dematerialize

Answer 19

Entity Framework 4 Solution

Most of the answers here were EF6-specific. Here's one for those of you still using EF4.

This method replaces the @p__linq__0/etc. parameters with their actual values, so you can just copy and paste the output into SSMS and run it or debug it.

    /// <summary>
    /// Temporary debug function that spits out the actual SQL query LINQ is generating (with parameters)
    /// </summary>
    /// <param name="q">IQueryable object</param>
    private string Debug_GetSQLFromIQueryable<T>(IQueryable<T> q)
    {
        System.Data.Objects.ObjectQuery oq = (System.Data.Objects.ObjectQuery)q;
        var result = oq.ToTraceString();
        List<string> paramNames = new List<string>();
        List<string> paramVals = new List<string>();
        foreach (var parameter in oq.Parameters)
        {
            paramNames.Add(parameter.Name);
            paramVals.Add(parameter.Value == null ? "NULL" : ("'" + parameter.Value.ToString() + "'"));
        }
        //replace params in reverse order, otherwise @p__linq__1 incorrectly replaces @p__linq__10 for instance
        for (var i = paramNames.Count - 1; i >= 0; i--)
        {
            result = result.Replace("@" + paramNames[i], paramVals[i]);
        }
        return result;
    }

Concoction

Wow! I have looked for this a long time! Good job! (I have one old EF 4 project) – Haemophilia

Answer 20

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.

Deragon

Answer 21

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

Subscription

Yes, but i believe that Nobody wants to see the p__linq__i , but the real values – Roentgen

This way still works in EF 6 and it will be helpful if you only care about what the query structure look like. In my case the project I create the IQueryable<T> object does not have reference to System.Data.Entity nor I want to add it just for debugging purpose. So this method worked just fine. – Mendelssohn

Answer 22

For me

result.ToQueryString()

wasn't working because I was using .SingleAsync() or .ToListAsync()

So when I removed .SingleAsync() and separated it out into two statements, it worked

Example before

var result = await Context.Work.AsNoTracking().Where(x => x.WorkId == workId).SingleAsync();

Example after

var query  = Context.Work.AsNoTracking().Where(x => x.WorkId == workId);

Debug.WriteLine(query.ToQueryString()); // Separated into two pieces so I can see what the query is
    
var result = await query.SingleAsync();

Hic

Isn't that kinda obvious? ToQueryString works on IQueryable. – Chansoo

Answer 23

For EF Core 8:

In the class overridden by DbContext, the LogTo method can be used as follows.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer("Server=localhost;Database=efcoreexample;Integrated Security=SSPI;TrustServerCertificate=True")
        .LogTo(e => System.Diagnostics.Debug.WriteLine(e));
}

Intranuclear

Answer 24

If you are using Entity Framework Core 7 or newer you can use this free extension to view the query and the query plan directly inside VS: View Entity Framework Core query plan directly inside Visual Studio.

Note: I'm the author of this extension.

Supports SQL Server and PostgreSQL

Selie

Answer 25

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.

Wertheimer

Answer 26

I found this worked for EF6 based on @itfso comment.

public static string GetSqlStatement(this IQueryable query)
{
    // get InternalQry Object
    PropertyInfo internalQueryProperty = query.GetType().GetProperty("InternalQuery", BindingFlags.NonPublic | BindingFlags.Instance);
    var internalQryObj = internalQueryProperty.GetValue(query);

    // get TraceString
    var traceStringMethod = internalQryObj.GetType().GetMethod("ToTraceString");
    var sql = traceStringMethod.Invoke(internalQryObj, null) as string;

    //return
    return sql;
}

Pratfall