View Generated Queries From EF Core

Debugging SQL queries can be a challenging task, particularly when trying to identify issues with an Entity Framework Core application. Fortunately, there are effective ways to view generated SQL queries from EF Core. These techniques can be beneficial for detecting and fixing issues. By applying these approaches, it becomes simpler to identify problems with our Entity Framework Core application. In this article, we'll explore some of the most common methods for viewing generated SQL queries.

Debugging

The simplest way to view generated SQL queries is to use a debugger. We can set a breakpoint to the method that interests us and see what's inside DebugView -> Query

Alternatively, we can assign this value to a local variable with the ToQueryString() method of IQueryable.

var sql = query.ToQueryString();

The generated query looks like this:

Logging

EF Core provides a built-in logging mechanism that can be used to log generated SQL queries to the console, a file, or any other logging provider supported by .NET. We can enable logging with these two ways:

Using a logging provider

To use this approach, we need to add a logging provider and configure the logging options in our application's startup code. For example at our Program.cs file we can do the following :

var loggerFactory = LoggerFactory.Create(builder =>
{
    builder
        .AddFilter((category, level) =>
            category == DbLoggerCategory.Database.Command.Name
            && level == LogLevel.Information)
        .AddConsole();
});

builder.Services.AddDbContext<TodoDbContext>(opt => 
    opt.UseSqlServer(connectionString)
       .UseLoggerFactory(loggerFactory));

Here we use Microsoft's logging framework to create a logger factory that outputs log messages to the console with the .AddConsole() method and we add a filter that only logs messages with a DbLoggerCategory.Database.Command.Name category and a LogLevel.Information level.

Using the LogTo method

Another approach is to use the LogTo() method of the DbContextOptionsBuilder. For example:

builder.Services.AddDbContext<TodoDbContext>(opt => 
opt.UseSqlServer(connectionString)
   .LogTo(Console.WriteLine,LogLevel.Information));

The LogTo() method takes an Action as a parameter, that specifies where log messages should be sent, and a LogLevel value that specifies the minimum log level at which messages should be sent.

In both approaches the results will show up in the console like this :

info: Microsoft.EntityFrameworkCore.Database.Command[20101]


Executed DbCommand (28ms) [Parameters=[@p_0='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30']


SELECT TOP(1) [t].[Id], [t].[IsComplete], [t].[Name]


FROM [TodoItems] AS [t]


WHERE [t].[Id] = @p_0

LINQPad

LINQPad is a popular tool for writing and testing LINQ queries in .NET applications. It also includes a feature for visualizing and analyzing generated SQL queries from EF Core, making it a useful tool for debugging and optimizing database access.

To use LINQPad with EF Core, we follow these steps:

1) Click at the left top corner on Add connection.

2) Select 'Entity Framework Core' on the Data Context prompt

3) We add the necessary information to connect to our database and test the connection by clicking Test.

4) After pressing the OK button , the database gets populated. To visualize the generated SQL query for a LINQ query, we simply run the query and view the SQL output in LINQPad.

For example:

Conclusion

In conclusion, there are several ways to see generated SQL queries from EF Core. Debugging and Logging are simple and effective ways to see SQL queries during development, and LINQPad is a useful tool for quick and ad-hoc debugging and optimization of database access. By using one of these methods, we can improve the performance and efficiency of our EF Core applications and ensure that they are working as expected.