Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 41 ยท Getting Started

Logging and Seeing the SQL EF Core Runs

Learn how to see the SQL that EF Core generates - LogTo, ToQueryString, sensitive data logging, ILogger categories and why reading the SQL is the best habit you can build.

EF Core writes SQL for you, which is convenient until a query is slow, returns the wrong rows, or runs fifty times when you expected once. The cure is always the same: look at the SQL. An ORM you never inspect is a black box; an ORM whose SQL you read regularly is a very productive tool.

This lesson shows the three ways to see what EF Core sends to the database, how to turn on parameter values safely, and how to control the noise. The real EF Core cannot run on this site, so the code is shown read-only, and the runnable parts are small stand-ins that model the idea (a logger and a query-to-text translator).

Option 1: ToQueryString

Any LINQ query that returns IQueryable<T> can show its SQL without running it:

C#
var query = db.Products
    .Where(p => p.Price > 20m)
    .OrderBy(p => p.Name);

Console.WriteLine(query.ToQueryString());

For SQLite this prints something like:

TEXT
.param set @__p_0 20.0

SELECT "p"."Id", "p"."Name", "p"."Price"
FROM "Products" AS "p"
WHERE "p"."Price" > @__p_0
ORDER BY "p"."Name"

Notice the parameter: EF Core never pastes your values into the SQL text. That prevents SQL injection and lets the database reuse query plans. ToQueryString is perfect in a unit test or a quick console experiment, because nothing is executed. It does not work on a query that has already been materialized (ToList() returns a list, not a query).

Option 2: LogTo on the options

To see every command as it runs, add LogTo when you configure the context:

C#
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
    options
        .UseSqlite("Data Source=shop.db")
        .LogTo(Console.WriteLine, LogLevel.Information);
}

You can filter by category so the output stays readable:

C#
.LogTo(
    Console.WriteLine,
    [DbLoggerCategory.Database.Command.Name],
    LogLevel.Information)

A typical line looks like this:

TEXT
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@p0='Ada'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "Customers" ("Name") VALUES (@p0) RETURNING "Id";

LogTo is the quickest option while learning because it needs no dependency injection setup.

Option 3: the normal ILogger pipeline

In an ASP.NET Core app, EF Core already uses Microsoft.Extensions.Logging. You do not need LogTo; you only need to set the level in appsettings.Development.json:

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

The category Microsoft.EntityFrameworkCore.Database.Command is the one that prints the SQL. Leave it at Warning in production (or send it to a structured log sink at a sampled rate), because logging every command costs time and storage.

Parameter values: opt in, in development only

By default parameter values are hidden and shown as ?, because they may contain personal data. While debugging locally you can reveal them:

C#
options.EnableSensitiveDataLogging()   // shows parameter values
       .EnableDetailedErrors();        // better messages for materialization errors

Never enable EnableSensitiveDataLogging in production. Logs are copied to many places, and names, emails and tokens would end up in them.

What to look for

When you read generated SQL, ask four questions:

  1. How many commands ran? One page view that runs 101 commands is the N+1 problem (a later chapter).
  2. Which columns are selected? SELECT *-like column lists for a screen that needs two fields means you should project (see Projections with Select).
  3. Is the filter in the SQL? If the WHERE is missing and your code then filters in memory, you pulled the whole table.
  4. Is there an ORDER BY before paging? Paging without a stable order returns unpredictable pages.

Three ways to see SQL

ToQueryString SQL without running
LogTo Every command, quickly
ILogger Normal app logging

Read the SQL often

1 / 2

Stand-in: a command logger

The stand-in is not EF Core. It models what LogTo does: a data source that reports every command it executes to a log callback, with the parameter values kept separate from the SQL text. Run it and count the commands.

C# Stand-in: logging every command

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Two of those three commands differ only by a parameter. In real EF Core, seeing a repeated command with changing parameters in the log is the classic sign of a query inside a loop.

Stand-in: from expression to SQL text

Real ToQueryString walks an expression tree. This stand-in builds a toy query object and renders it, so you can see how a fluent chain becomes one statement.

C# Stand-in: a query that renders its own SQL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Nothing ran against a database: building the query and rendering it are separate steps from executing it, which is exactly the deferred execution you will meet in LINQ to Entities.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which method shows a LINQ query's SQL without executing it?

    ToQueryString renders the SQL and parameters of an IQueryable without running the query.
  2. 2. Why are parameter values hidden in EF Core logs by default?

    Values can include names, emails and tokens, so you must opt in with EnableSensitiveDataLogging, in development only.
  3. 3. Which log category prints the SQL commands?

    Database.Command is the category that reports each executed DbCommand.
  4. 4. A single page runs the same SELECT 100 times with different ids. What does the log suggest?

    Repeated commands with changing parameters mean a query is executed per item instead of once for all.

Summary

  • Read the SQL: ToQueryString() for one query, LogTo or ILogger for everything that runs.
  • Parameters keep values out of the SQL text; values stay hidden unless you enable sensitive data logging in development.
  • Check the command count, selected columns, the WHERE and the ORDER BY of every query you care about.
  • Keep logging at Warning in production.

Next: DbContext and DbSet