64
votes

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.

enter image description here

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: 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.

9
Apparently he's using EF Core Logging, most likely with filter if (eventId.Id == Microsoft.EntityFrameworkCore.Diagnostics.RelationalEventId.CommandExecuted.Id) - Ivan Stoev
Just add "Microsoft.EntityFrameworkCore.Database.Command": "Information" to appsettings.Development.json - RickAndMSFT

9 Answers

26
votes

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.

enter image description here

See SQL Logging of Entity Framework Core in the official docs. It's a bug that it doesn't log by default, see this GitHub issue.

82
votes

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
32
votes

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

19
votes

https://docs.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");
9
votes

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

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")));
5
votes

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) });
1
votes

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))
1
votes

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