In EF Core 10, how do you make a DbContext log the SQL it sends, and what do EnableSensitiveDataLogging and EnableDetailedErrors add?
answer
- a delegate on the options builder
- LogTo minimum level defaults to Debug
- Executed DbCommand at Information
- parameter values logged as '?'
- try-catch around each value read
basics
~20 sCall LogTo on DbContextOptionsBuilder, or let AddDbContext use the host's ILoggerFactory, and EF Core logs each command with its SQL. EnableSensitiveDataLogging adds parameter and key values; EnableDetailedErrors names the property behind a failed value read.
solid answer
~40 sEF Core logs every command it runs as `RelationalEventId.CommandExecuted`, at Information level under the `Microsoft.EntityFrameworkCore.Database.Command` category, with the SQL text and elapsed time. In ASP.NET Core, `AddDbContext` wires EF into the host's `ILoggerFactory`, so the SQL appears wherever the app logs, if that category's level allows it. `LogTo(Console.WriteLine, ...)` on `DbContextOptionsBuilder` is the framework-free alternative, with overloads that filter by level, event or category. By default parameter values are logged as `'?'` and key values are left out of exception messages; `EnableSensitiveDataLogging()` includes them, and in EF Core 10 also un-redacts values inlined into the SQL. `EnableDetailedErrors()` wraps each value read in a try-catch so a bad NULL or type names the property. Both are development settings; `TagWith` then labels the query you are looking for.
code
csharp · 20 linesbuilder.Services.AddDbContext<GearContext>(options =>
{
options.UseNpgsql(builder.Configuration.GetConnectionString("Gear"));
if (builder.Environment.IsDevelopment())
{
options
.LogTo(Console.WriteLine,
new[] { DbLoggerCategory.Database.Command.Name },
LogLevel.Information)
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
});
// in the search endpoint
var products = await db.Products
.TagWith("product search")
.Where(p => p.Category == category)
.ToListAsync();go deeper
Recall LogTo on the options builder, that SQL arrives as Executed DbCommand entries, and that parameter values stay hidden until EnableSensitiveDataLogging is called.
Explain the event, level and category behind the SQL log, how the LogTo overloads filter it, and what EnableDetailedErrors adds when a value read fails.
Show how you keep SQL logging useful and safe: sensitive data only in development, per-category levels in production, and TagWith to find one query among thousands.
Weigh diagnosability against log volume and data-protection risk, and set a team rule for which EF Core diagnostics may ever reach production log storage.
## Where EF Core's SQL log comes from EF Core reports what it does as **log events**, each with an `EventId`, a level and a category. The event that matters when hunting a slow query is `RelationalEventId.CommandExecuted`, logged at **Information** under the category `Microsoft.EntityFrameworkCore.Database.Command`. Its message starts with `Executed DbCommand (12ms)`, lists the parameters, the command type and timeout, and then prints the SQL. A companion event, `CommandExecuting`, is logged at **Debug** just before the command runs. There are two ways to receive those events: - **Through the host's logging.** In an ASP.NET Core app, `AddDbContext` and `AddDbContextPool` make EF use the application's `ILoggerFactory` automatically, so command events reach the same sinks as every other log line, provided the minimum level configured for that category lets Information through. How those levels are set is the ILogger topic, not EF's. - **Through `LogTo`.** A hook on `DbContextOptionsBuilder` that sends formatted messages to any `Action<string>`: `Console.WriteLine`, a `StreamWriter`, a test's output helper. It needs no logging framework and behaves the same in a console tool, a test or a web app. ## LogTo and its overloads The basic form is `LogTo(Action<string> action, LogLevel minimumLevel = LogLevel.Debug, DbContextLoggerOptions? options = null)`. At the default Debug level it is noisy: it includes executing and executed commands, connection opening and closing and context initialization. The overloads narrow it: | Call | What is logged | |---|---| | `LogTo(Console.WriteLine, LogLevel.Information)` | Information and above, which keeps `CommandExecuted` | | `LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Information)` | only command events | | `LogTo(Console.WriteLine, new[] { RelationalEventId.CommandExecuted })` | only that one event | | `LogTo(action, (eventId, level) => ...)` | whatever the filter returns true for | `DbContextLoggerOptions` controls the format: `SingleLine` keeps each message on one line, and `DefaultWithUtcTime` replaces the default local timestamp with UTC. ## What EnableSensitiveDataLogging changes By default EF treats application data as confidential: - parameter values in logged commands appear as `'?'`, for example `@category='?'`, followed by details such as `(Size = 4000)`; - exception messages leave out key values; the identity-conflict error even suggests calling `EnableSensitiveDataLogging` to see them; - since **EF Core 10**, values EF inlines into the SQL text, for instance through `EF.Constant`, are also redacted to `?` in the log, although the database still receives the real values. `EnableSensitiveDataLogging()` turns all of this off: parameter values, key values and inlined constants appear in logs and exception messages. EF then logs `CoreEventId.SensitiveDataLoggingEnabledWarning` when the context is initialized, warning that the mode should only be enabled during development. Left on in production, it copies search terms, e-mail addresses and order references into log storage. ## What EnableDetailedErrors changes For speed, EF does not wrap each value it reads from a data reader in a try-catch. When stored data does not fit the model, such as a NULL in a column mapped to a non-nullable `int`, the exception can be hard to trace back to a property. `EnableDetailedErrors()` adds those try-catch blocks, so the error names the entity property that failed. It adds a small overhead to every query, so it is a development setting too. ## Finding the product search in the log In a busy log the shop's search query looks like every other `SELECT`. Two operators make it findable: 1. `TagWith("product search")` prepends `-- product search` as a SQL comment, visible in EF's log and in the database's own query monitoring. Several `TagWith` calls stack. 2. `TagWithCallSite()` adds the source file path and line number instead of a hand-written label. Read the elapsed time in the `Executed DbCommand` message, copy the SQL and examine its plan in the database before rewriting anything. ## Keeping it safe - Log `Microsoft.EntityFrameworkCore.Database.Command` at Information in development; in production keep it quieter, or every query becomes a log line. - Gate `EnableSensitiveDataLogging()` and `EnableDetailedErrors()` on the development environment. - These options are per context: they change nothing about the application's other loggers. - In tests, point `LogTo` at the test framework's output so a failing test shows the SQL it ran.
- Why might an ASP.NET Core app using AddDbContext log no SQL at all?EF logs `CommandExecuted` at Information under `Microsoft.EntityFrameworkCore.Database.Command` through the host's `ILoggerFactory`. If configuration sets that category, `Microsoft.EntityFrameworkCore`, or the default level to Warning or higher, the events are filtered out before any sink sees them. Lowering that one category in development, or adding `LogTo`, brings the SQL back.
- What does TagWithCallSite add, and can a compiled query take its tag as a parameter?`TagWithCallSite()` writes a SQL comment with the source file path and line number of the query. Tags are always emitted as literal comments in the SQL text, so a compiled query cannot take a tag as a parameter; the tag is fixed when the query is written. Multiple `TagWith` calls are cumulative.
- What did EF Core 10 change about redaction in logged SQL?Earlier versions hid parameter values but printed values EF inlined into the SQL text, for example via `EF.Constant`, in full. EF Core 10 replaces those inlined values with `?` in the logged SQL while still sending them to the database. `EnableSensitiveDataLogging()` shows them again.
- EnableSensitiveDataLogging is what makes EF Core write the SQL text to the log at all.
- EnableDetailedErrors adds the generated SQL to every exception message.
- LogTo on one DbContext reconfigures logging for the whole ASP.NET Core app.
- EnableSensitiveDataLogging is harmless in production because logs stay internal.
- LogTo(Console.WriteLine, LogLevel.Warning) is enough to see every executed command.
Assumes EF Core 10, which also redacts values inlined into logged SQL as ? unless EnableSensitiveDataLogging is on; earlier versions logged inlined values verbatim.