Entity Framework Core Intermediate

Logging, ToQueryString and diagnostics

Seeing the SQL that EF Core sends is the fastest way to find slow or surprising queries. This lesson shows ToQueryString, logging SQL in development, finding N+1 query problems, tagging queries, and the extra details to turn on only while debugging.

Finding a slow query
  1. StartTurn on command loggingDevelopment settings only
  2. Step 2Run the pageWatch the SQL and timings in the log
  3. Step 3Spot the problemRepeated SELECTs, wide columns, no WHERE
  4. Step 4Fix the LINQInclude, Select, filters or paging
  5. EndCheck againFewer, smaller, faster commands

See the SQL for one query

ToQueryString returns the SQL for a LINQ query without running it. Use it in a debugger or a test.

C#
var query = db.Orders
    .Where(o => o.Total > 1000)
    .OrderByDescending(o => o.OrderedAt)
    .TagWith("Big orders on the dashboard");

Console.WriteLine(query.ToQueryString());
// -- Big orders on the dashboard
// DECLARE @__p_0 decimal(18,2) = 1000.0;
// SELECT ... FROM [Orders] AS [o] WHERE [o].[Total] > @__p_0 ORDER BY ...

Log every command

ASP.NET Core logging already includes EF Core. Raise the Database.Command category to Information in development to see each SQL statement and how long it took.

JSON
// appsettings.Development.json
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

// Or directly on the context, development only
options.UseSqlServer(cs)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging()     // shows parameter values: never in production
       .EnableDetailedErrors();

What to look for

  • N+1 queries: the same SELECT repeated once per row in a loop. Fix with Include or a projection with Select.
  • SELECT of every column when the page shows two: project with Select.
  • Queries with no WHERE on big tables, or Take missing on lists.
  • Slow commands: the log shows the elapsed milliseconds for each.
  • TagWith puts a comment in the SQL, so you can find the query in SQL Server tools and Query Store.

In production

Keep EF Core logs at Warning, and never turn on sensitive data logging, because parameter values can include personal data. Use OpenTelemetry or Application Insights to trace slow database calls, and Query Store in SQL Server to find the worst queries.

Interview question

What is the N+1 query problem and how do you spot it in EF Core?

It happens when code loads a list with one query and then runs one more query per item, for example by touching a lazy-loaded navigation inside a loop. With 100 rows that is 101 round trips. It shows up in the SQL log as the same statement repeated many times. Fix it with Include, a projection using Select, or AsSplitQuery for large graphs.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.