How to Log SQL Queries in EF Core

Summary: in this tutorial, you will learn how to log SQL queries in EF Core for enhanced debugging and troubleshooting purposes.

Configuring EF Core logging

To log information generated by EF Core to a target like the Console, a file, or a database, you can configure it in the OnConfiguring() method of the DbContext class like this:

protected override void OnConfiguring(
        DbContextOptionsBuilder 
        optionsBuilder
    )
    {
        optionsBuilder
            .UseSqlServer(connectionString)
            .LogTo(target)
    }Code language: C# (cs)

For example, to log information generated by EF Core to the Console, you can pass the Console.WriteLine to the OnConfiguring method as follows:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace HR;

public class HRContext : DbContext
{
    public DbSet<Employee> Employees { get; set;}
    public DbSet<Department> Departments  {  get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {

        var configuration = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

        var connectionString = configuration.GetConnectionString("Local");

        optionsBuilder.UseSqlServer(connectionString)
                      .LogTo(Console.WriteLine)
                      .EnableSensitiveDataLogging();
    }
}Code language: C# (cs)

If you run the project, you’ll see a lot of information displayed on the Console.

Filtering the logs

EF Core assigns every log message a category and log level. To filter out the messages, you can specify the message category and log level using the LogTo() method.

Message Category

EF Core assigns a message category to every log message. By filtering the messages by categories, you can specify which messages EF Core should include in the logs. To do that, you pass an array of message categories to the LogTo() method.

For example, the following instructs EF Core to include only database commands in the logs and writes them to the Console:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{

    var configuration = new ConfigurationBuilder()
        .SetBasePath(Directory.GetCurrentDirectory())
        .AddJsonFile("appsettings.json")
        .Build();

    var connectionString = configuration.GetConnectionString("Local");


    optionsBuilder.UseSqlServer(connectionString)
                  .LogTo(Console.WriteLine,
                         new[] { DbLoggerCategory.Database.Command.Name });
}Code language: C# (cs)

If you run the project, you’ll see that the Console displays only database commands.

Here is the extracted version of the logs that shows the SQL query that EF Core sent to the database for selecting all rows from the Departments table:

...
info: 6/8/2023 18:52:06.635 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (23ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [d].[Id], [d].[Name]
...Code language: C# (cs)

Log Level

Besides the message category, EF Core assigns a log level to every log message. The log level is defined by the LogLevel enum with the following members:

Debug (default)
Error
Critical
Information
Trace
Warning
None

By default, EF Core includes log messages at Debug level and above. To filter out log messages, you can pass a higher log level to the LogTo() method.

For example, the following write only the information log messages in the Console:

optionsBuilder.UseSqlServer(connectionString)
               .LogTo(Console.WriteLine,
                      new[] { DbLoggerCategory.Database.Command.Name },
                      LogLevel.Information);Code language: C# (cs)

If you run the program, you’ll see only the information log messages in the console as follows:

info: 6/8/2023 18:54:10.967 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (15ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [d].[Id], [d].[Name]
      FROM [Departments] AS [d]Code language: C# (cs)

Enabling sensitive data

Modify the Program.cs in the HR Console App project to the following:

using HR;
using static System.Console;


using var context = new HRContext();

var name = "Sales";
var departments = context.Departments
                        .Where(d => d.Name == name)
                        .ToList();

foreach (var department in departments)
{
    WriteLine($"{department.Id} {department.Name}");
}Code language: C# (cs)

In this program, we find departments by name and display the results in the console. If you run the program, you’ll see the following log information:

info: 6/8/2023 18:55:01.286 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (36ms) [Parameters=[@__name_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT [d].[Id], [d].[Name]
      FROM [Departments] AS [d]
      WHERE [d].[Name] = @__name_0
Code language: C# (cs)

As you can see from the log, it doesn’t show the actual value of the name parameter. Instead, it shows a question mark (?).

The reason is that command parameters may contain sensitive information like passwords and social security numbers. If you store sensitive information in the log files, it becomes vulnerable to various forms of attacks like hacking, data breaches, and identity theft. Therefore, by default, EF Core doesn’t include application data in the log.

If you work in the development or test environment, you may want to include the sensitive data in the log for debugging and testing purposes.

To do that you can call the EnableSensitiveDataLogging() method of the OptionBuilder object like this:

optionsBuilder.UseSqlServer(connectionString)
              .LogTo(Console.WriteLine,
                    new[] { DbLoggerCategory.Database.Command.Name },
                    LogLevel.Information)
              .EnableSensitiveDataLogging();Code language: C# (cs)

And if you run the application, you’ll see the name’s value ('Sales') instead of a question mark (?) in the log:

info: 6/8/2023 11:56:24.939 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (28ms) [Parameters=[@__name_0='Sales' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT [d].[Id], [d].[Name]
      FROM [Departments] AS [d]
      WHERE [d].[Name] = @__name_0Code language: C# (cs)

Configuring logging targets

EF Core supports logging to the following targets:

Console
File via StreamWriter
Debug window
Or external logger API

For example, you can set the logging target to a logs.txt file using the StreamWriter like this:

using HR.Domain;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace HR.Data;

public class HRContext : DbContext
{
    // Write logs to logs.txt
    StreamWriter _writer = new("logs.txt", true);

    public DbSet<Employee> Employees { get; set;}

    public DbSet<Department> Departments {  get; set; }

    protected override void OnConfiguring(
        DbContextOptionsBuilder
        optionsBuilder
    )
    {

        // Pass the writer.WriterLine to 
        optionsBuilder
           .UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB; Initial Catalog=HR")
           .LogTo(_writer.WriteLine);
    }

    public override void Dispose()
    {
        // dispose the StreamWriter to save the log file
        _writer.Dispose();
        base.Dispose();
    }
}Code language: C# (cs)

How it works.

First, create a new StreamWriter object that appends log messages to the logs.txt file:

StreamWriter _writer = new("logs.txt", true);Code language: C# (cs)

Note that to append log messages to the “logs.txt” file, you set the second argument of the StreamWriter to true. Otherwise, it will overwrite the existing logs.

Second, pass the _writer.WriteLine as the target to the LogTo() method of the OptionBuilder object:

optionsBuilder.UseSqlServer(connectionString)
              .LogTo(_writer.WriteLine,
                    new[] { DbLoggerCategory.Database.Command.Name },
                    LogLevel.Information)
                .EnableSensitiveDataLogging();Code language: C# (cs)

Third, override the Dispose() method of the HRContext class to dispose of the StreamWriter object to ensure the log file is saved properly:

public override void Dispose()
{
    // dispose the StreamWriter to save the log file
    _writer.Dispose();
    base.Dispose();
}Code language: C# (cs)

If you run the program, you’ll see the log messages on the logs.txt file.

Note that if you see an empty logs.txt file, it’s likely that the HRContext object is not disposed properly. In this case, you can fix it by adding the using statement in the HRContext declarations:

using HR;
using static System.Console;


using var context = new HRContext();

var name = "Sales";
var departments = context.Departments
                        .Where(d => d.Name == name)
                        .ToList();

foreach (var department in departments)
{
    WriteLine($"{department.Id} {department.Name}");
}Code language: JavaScript (javascript)

Summary

Logs are useful for debugging and troubleshooting problems.
EF Core logs provide a lot of information including SQL queries.
Use simple logging API (LogTo() method) to configure EF Core logging.
Use log level & category to filter out the log messages.
Explicitly configure sensitive data in parameters to include in the log by calling the EnableSensitiveDataLogging() method of the OptionBuilderobject.

Was this tutorial helpful ?