A Comprehensive Guide to Display Underlying SQL Queries in EF Core

Introduction

In Entity Framework Core (EF Core) development, viewing the underlying generated SQL queries is crucial for debugging performance issues and understanding ORM behavior. Traditional methods like using SQL Profiler can be cumbersome, while EF Core offers built-in logging functionality to output SQL directly within the application. Based on high-scoring answers from Stack Overflow, this article systematically explains how to configure EF Core to display SQL queries, covering from basic setups to advanced custom methods.

Default Logging Configuration (.NET 6+)

Starting from .NET 6, EF Core enables SQL query logging by default in development environments. This is achieved through ASP.NET Core's logging system, where developers only need to add specific configurations in the appsettings.Development.json file. For example, setting "Microsoft.EntityFrameworkCore.Database.Command": "Information" configures the SQL query log level to Information. Below is a complete configuration example:

{
  "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": "*"
}

This configuration ensures that SQL logs are not output in production environments, avoiding performance overhead. Logs will appear in the console or Visual Studio output window for real-time viewing. According to Microsoft's official documentation, the lack of default enabling in earlier versions was a known issue, fixed in .NET 6.

Using LoggerFactory (Older EF Core Versions)

For versions before EF Core 5, or when more granular control is needed, LoggerFactory can be used. In the DbContext class, configure logging by overriding the OnConfiguring method. For instance, create a static LoggerFactory instance using DebugLoggerProvider to output logs to the debug window:

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

This method requires referencing the Microsoft.Extensions.Logging namespace and installing the Microsoft.Extensions.Logging.Debug NuGet package. It outputs logs only when a debugger is attached, making it suitable for development environments.

Simple Logging Method (EF Core 5+)

EF Core 5 introduced the LogTo method, providing a lightweight logging configuration without additional packages. In the OnConfiguring method, logs can be directed to various targets. For example, output to the console:

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

Or output to the debug window:

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

Logs can also be output to a file using StreamWriter, with resource cleanup in the Dispose method:

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

This approach simplifies logging configuration, ideal for quick debugging scenarios.

Dependency Injection Configuration

In ASP.NET Core applications, EF Core logging can be configured via dependency injection. For example, in Startup.cs or the Program class, integrate logging when using AddDbContext:

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

This allows centralized logging setup during service configuration, suitable for large-scale projects.

Best Practices and Considerations

Enabling SQL logging in development environments is recommended, but it should be disabled in production to avoid performance and security risks. Use conditional compilation or environment variables to control log output. For instance, enable the LogTo method only in Debug mode. Additionally, log content may include sensitive data like query parameters, so ensure secure log storage. For complex queries, combining EF Core's profiling tools (e.g., EnableSensitiveDataLogging) can provide deeper insights.

Conclusion

EF Core offers multiple flexible methods to display underlying SQL queries, from default configurations in .NET 6 to custom logger factories and simple logging methods. Developers should choose the appropriate solution based on project needs and EF Core versions. These tools not only accelerate debugging but also help optimize database interactions, enhancing application performance. As EF Core evolves, logging functionality is expected to become more integrated and user-friendly.