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

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.

H/T to Julie Lerman for this over in https://thedatafarm.com/ . I learned about this method using her Pluralslight course here

This is something I wish I knew about starting off. I hope it is 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)
Logging in .NET Core and ASP.NET Core
Learn how to use the logging framework provided by the Microsoft.Extensions.Logging NuGet package.
Default providers for logging in .Net Core
Default Logging providers for ASP.NET Core application as of 14 May 2020

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.

My ConfigureServices method before enabling sensitive data logging

Enable sensitive data logging by calling the EnableSensitiveDataLogging method.

 services.AddDbContext<DutchContext>(
                cfg =>
                {
                    cfg.UseSqlServer(_config.GetConnectionString("DutchConnectionString")).EnableSensitiveDataLogging();
                }
                );
My ConfigureServices method after enabling sensitive data logging

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

2. Configure the Logging

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:

Configuration file for my application before configuring the logging for Entity Framework Core Database commands.

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"
		}
	}

My final configuration file:

Configuration file after adding logging for Entity Framework Core Database Commands

That's it!

You can now run your application and see the SQL generated by Entity Framework Core in any of the default providers mentioned earlier.

Here I'm using the output window in Visual Studio 2019 and showing the output from ASP.NET Core Web Server.

Output window in Visual Studio Code showing the SQL commands generated by Entity Framework Core

If you can't see the output window just use 'Ctrl + Alt + O' to bring it up or find it in the View menu at the top of visual studio.

That's it for today. As always, if you have any questions on this or want to reach out to me the best place to go is Twitter.

You can follow me on twitter @eamokeane where I'll continue to document my journey as a software developer.