Eamon Keane

Posted on May 16, 2020 • Updated on Oct 28, 2020 • Originally published at eamonkeane.dev

How to Inspect the SQL Generated by Entity Framework Core using .NET Core's built in Logging

#csharp #sql #dotnet #entityframeworkcore

Today I'm going to show you how to intercept and view the SQL generated by Entity Framework Core. This method takes advantage of Microsoft's built in logging for .NET core.

This is something I wish i knew about starting off. I hope it's of use to somebody else.

This method is an alternative to profiling the SQL using something like SQL Server Management Studio. This will work with an ASP.NET Core application which should cover the vast majority of use cases!

By default, .NET core outputs logs to the following locations when you call HostCreateDefaultBuilder(args) within Program.cs:

Console
Debug
Event Source
EventLog (Windows Only)

Default providers for logging is ASP.Net Core 3.1 (Microsoft Documentation)

1. Enable Sensitive Data Logging

Navigate to your startup file for your .NET Core project (Startup.cs). Now find where you've configured your application to use Entity Framework Core. This should be in the ConfigureServices method.

Enable sensitive data logging by calling the EnableSensitiveDataLogging method.

 services.AddDbContext<DutchContext>(
                cfg =>
                {
                    cfg.UseSqlServer(_config.GetConnectionString("DutchConnectionString")).EnableSensitiveDataLogging();
                }
                );

Enabling Sensitive Data Logging allows you to view the parameters being passed into the SQL Queries in the logs.

2. Configure the Logging using your Configuration File

The next step is to tell your ASP.NET Core App to log Entity Framework Core commands.

For this step, go to your config file. Typically this is named 'Appsettings.json'. It's likely you configured the connection string for your application here.

This is what mine looks like:

Add a new line to "Logging" => "LogLevel"

 "Microsoft.EntityFrameworkCore.Database.Command": "Information"

The logging config should now look something like this:

"Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft": "Warning",
            "Microsoft.EntityFrameworkCore.Database.Command": "Information"
        }
    }