Debugging Generated SQL

Use logger configurations to intercept and inspect raw SQL during development.

In this lesson, you will learn how to configure Entity Framework Core to output the exact SQL statements generated by your LINQ queries directly to your console, making it easy to identify performance bottlenecks.

The "Why" of SQL Inspection

When writing LINQ-to-Entities, it is easy to assume the generated SQL is efficient, but the reality can sometimes be quite different. A simple query might trigger an unexpected JOIN or a missing WHERE clause that leads to a full table scan. Instead of guessing what the database is doing, we can tap into EF Core’s internal logging mechanism to see the raw SQL in real-time.

Configuring the Logger

The most effective way to intercept generated SQL during development is by configuring a LoggerFactory on your DbContextOptions. By setting the logging level for RelationalEventId.CommandExecuting to LogLevel.Information, EF Core will print every query it sends to your database.

You can set this up within your OnConfiguring method:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information); 
}

Interpreting the Output

Once enabled, every time you execute a command (like ToList() or SaveChangesAsync()), the generated SQL will appear in your console output. You will see the parameterized query followed by the actual values used for those parameters.

Pro-tip: For highly complex applications, the output can become noisy. You can filter this logging to only target SQL commands by utilizing the DbLoggerCategory:

optionsBuilder.LogTo(message => Debug.WriteLine(message), 
    new[] { DbLoggerCategory.Database.Command.Name }, 
    LogLevel.Information);

By inspecting this output, you can spot "N+1" query issues, redundant column selections, or inefficient join logic immediately after running your code. This visibility allows you to refactor your LINQ expressions or adjust your modeling strategies based on empirical evidence rather than assumptions.

Key Takeaway: Use LogTo with DbLoggerCategory.Database.Command to expose your generated SQL, allowing you to catch inefficient query translation before it hits production.