How do you show underlying SQL query in EF Core?

Question

At 3:15 from the end of this ".NET Core 2.0 Released!" video, Diego Vega shows a demo of new features in Entity Framework Core 2.0. As part of that, a dump of the underlying SQL is shown in the console app.

I have seen many answers on Stack Overflow suggesting that people use an SQL profiler to view the underlying queries. But now I'm curious: how can you do what Diego Vega did, and get the query to show right there in the application?

Update for .NET 6+: EF logging is enabled by default in development. See this GitHub issue

Update: Diego added "Microsoft.EntityFrameworkCore.Database.Command": "Information" to appsettings.Development.json. See How do you show underlying SQL query in EF Core? for more details.

Answer 1

Update for .NET 6 and later: EF logging is enabled by default in development.

Just add "Microsoft.EntityFrameworkCore.Database.Command": "Information" to appsettings.Development.json so it's only logged in dev mode. You typically don't want to log every query in a production app.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=MyDB-2;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
     ,"Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  },
  "AllowedHosts": "*"
}

The SQL output shows in the command window or VS output window.

See SQL Logging of Entity Framework Core in the official docs. In older versions, it was a bug that it didn't log by default, see this GitHub issue.

Answer 2

Hi you can do something like following to display Entity Framework Core generated sql code in output window. In your DbContext class:

public static readonly Microsoft.Extensions.Logging.LoggerFactory _myLoggerFactory = 
    new LoggerFactory(new[] { 
        new Microsoft.Extensions.Logging.Debug.DebugLoggerProvider() 
    });

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLoggerFactory(_myLoggerFactory);
}

The debug logger writes messages in the debug output window only when a debugger is attached.

You will have to do following:

• using Microsoft.Extensions.Logging;
• Install nuget package: Microsoft.Extensions.Logging.Debug

Answer 3

I use EF Core 3.x, this works for me:

services.AddDbContext<LibraryContext>(options => options
    .UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()))
    .UseSqlServer(Configuration.GetConnectionString("LibraryDemoSql")));

Credit: https://stackoverflow.com/a/59663606/2185783

Answer 4

EF Core >= 5

Simple logging

Entity Framework Core (EF Core) simple logging can be used to easily obtain logs while developing and debugging applications. This form of logging requires minimal configuration and no additional NuGet packages.

LogTo Console

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.LogTo(Console.WriteLine);

LogTo debug window

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.LogTo(message => Debug.WriteLine(message));

LogTo file

private readonly StreamWriter _logStream = new StreamWriter("mylog.txt", append: true);

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.LogTo(_logStream.WriteLine);

public override void Dispose()
{
    base.Dispose();
    _logStream.Dispose();
}

public override async ValueTask DisposeAsync()
{
    await base.DisposeAsync();
    await _logStream.DisposeAsync();
}

Answer 5

https://learn.microsoft.com/en-us/ef/core/miscellaneous/logging

In the OnConfiguring method of DbContext you can set your logger, log in console is a predefined type, just use this NuGet. Note that using Factory pattern is a best practice for the logger instances.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseLoggerFactory(MyLoggerFactory) // Warning: Do not create a new ILoggerFactory instance each time
        .UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=EFLogging;Trusted_Connection=True;ConnectRetryCount=0");

Answer 6

I'm sure the accepted answer works, but I wanted to know how to do this using DI so...

private readonly ILoggerFactory loggerFactory;  

public MyDataContext(DbContextOptions<MyDataContext> options, ILoggerFactory loggerFactory)
        : base(options)
{
    this.loggerFactory = loggerFactory;
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
{
    // Allow null if you are using an IDesignTimeDbContextFactory
    if (loggerFactory != null)
    { 
        if (Debugger.IsAttached)
        {
            // Probably shouldn't log sql statements in production
            optionsBuilder.UseLoggerFactory(this.loggerFactory); 
        }
    }
} 

Answer 7

Logging to the output window in visual studio in .Net Core 3

Use AddDebug to write to the output debug window.

services.AddDbContext<LibraryContext>(options => options
            .UseLoggerFactory(LoggerFactory.Create(builder => builder.AddDebug()))
            .UseSqlServer(Configuration.GetConnectionString("key")));

Answer 8

If you looking for just the Underlying Sql, then dani herrera answer is great.

But from EF Core 5(I am not sure of earlier versions), there is a feature called Debug View which I am sure, you will love it. This is not exactly what the question is asking for, but this debug view has lots useful info that you cannot ignore.

Here are a few ways to get to that.

1. Place a Break Point at context and look for Debug View as follows.

I have shown short view, now you try long view. Here you find a good info about the objects the context is holding.

2. If you have a IQueryable then try the following. This should be useful as well. Note here, the query is not yet sent to the database. So here you will get the query EF Core is about to send to the database.

Also note that you can call ToQueryString() on the IQueryable object to get the same query!

queryable.ToQueryString()

3. Last but not the least, try debug view on the Change Tracker object on the context.

The figure shows the state just before the save changes is called. Observer what happens after the save changes method is called(press F10, then observer again).

Answer 9

For people using EF Core 5, your query will have a Query property and also a ToQueryString() method that can be called to retrieve the query string. Note that these property/method only apply to a query, not the result - often there is no interim query variable as they're run immediately, so it is easiest to split a query that is formed and run into two steps to debug it:

//before
return db.Person.Where(p => p.Name == "John").ToList();

//after; q has Query/ToQueryString()
var q = db.Person.Where(p => p.Name == "John");
return q.ToList();

_{Image courtesy of Eamon Keene's blog which also details how to set up logging}

Answer 10

If you are writing an API or App service based on the ASP.NET Core MVC framework, you can enable SQL logging in your Startup.cs class like this

public void ConfigureServices(IServiceCollection services)
{
    ...

    Action<DbContextOptionsBuilder> dbOptionsContextBuilder = builder => 
        {
        builder.UseSqlServer(Configuration.DbConnection)  // Configuration.DbConnection is the db connection string
               .UseLoggerFactory(ConsoleLoggerFactory);   // Logs out SQL
        };

    services.AddDbContext<YourDatabaseContext>(dbOptionsContextBuilder);


    ...
}

where ConsoleLoggerFactory has been defined earlier something like this:

private static readonly LoggerFactory ConsoleLoggerFactory = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

Answer 11

Many answers here work like a charm, but not if you are using NLog.

If you are using NLog like me you can just do:

optionsBuilder.UseLoggerFactory(new NLogLoggerFactory());

Answer 12

If you're using Serilog:

Program.cs

public static IHostBuilder CreateHostBuilder(string[] args) =>
    Host.CreateDefaultBuilder(args)
        .UseSerilog()
        .MinimumLevel.Override("Microsoft.EntityFrameworkCore", Serilog.Events.LogEventLevel.Information)
            .WriteTo.Console(restrictedToMinimumLevel: Serilog.Events.LogEventLevel.Verbose))