How to Log and Debug EF Core SQL Queries

When I first started using Entity Framework Core, I had this feeling that everything was almost magical. I write some LINQ, hit Run, and boom, data comes back. At the beginning, that felt amazing. But later, when projects became bigger and business logic became more complicated, that magic sometimes turned into confusion.

I remember one project where a simple screen was loading very slowly. The API was not doing anything crazy, or at least that was what I thought. I looked at my C# code and everything looked clean. Nice LINQ. Very readable. But the users were complaining, and in production the query was taking seconds instead of milliseconds. That was the moment I really understood why logging and debugging EF Core SQL queries is not optional. It is a survival skill.

In this post, I want to share how I usually log and debug EF Core SQL queries, based on real situations I faced. This is not a textbook explanation. This is more like this is what worked for me, this is what I broke, and this is how I fixed it.

All example code in this blog post is not taken from my actual projects. The snippets are simplified and created only to demonstrate the concepts clearly.

Why You Should Care About the SQL Behind EF Core


EF Core is great, but at the end of the day, your database does not understand LINQ. It understands SQL. If you do not know what SQL EF Core is generating, you are basically blind.

In one of my projects, we had a report screen that joined several tables. On the C# side, the query looked fine. But EF Core was generating a SQL query with multiple joins, subqueries, and columns we did not even need. That extra data was killing performance.

So the first lesson I learned is very simple. If something is slow, confusing, or behaving weirdly, look at the SQL.

Enabling Basic SQL Logging

Let us start with the simplest thing. Logging SQL queries to the console or logs.

Logging Using ILogger

In most ASP.NET Core projects, EF Core already integrates with ILogger. You just need to configure it properly. Here is a simple example in Program.cs.

C#
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))
           .LogTo(Console.WriteLine);
});

The first time I did this, I was shocked. I ran one API endpoint and saw many SQL statements printed. That was a wake up call. I thought I was executing one query, but EF Core was doing more behind the scenes.

Later, I improved this by filtering only database commands.

C#
options.LogTo(
    Console.WriteLine,
    new[] { DbLoggerCategory.Database.Command.Name }
);

This way, I only see SQL queries, not everything else.

Adding Log Level Control

In real projects, logging everything in production is not a good idea. I usually control this with log levels.

C#
options.LogTo(
    Console.WriteLine,
    new[] { DbLoggerCategory.Database.Command.Name },
    LogLevel.Information
);

In development, I set it to Information. In production, I usually turn this off or log only warnings and errors.

Understanding a Simple Query First

Before debugging complex queries, I always start with something very simple.

C#
var customers = dbContext.Customers
    .Where(c => c.IsActive)
    .ToList();

Looks harmless, right? When you enable logging, you will see something like this

SQL
SELECT [c].[Id], [c].[Name], [c].[IsActive]
FROM [Customers] AS [c]
WHERE [c].[IsActive] = 1

This is good. Simple and clean. At this stage, logging helps confirm that EF Core is doing what you expect. This is especially helpful when you are new to EF Core or when onboarding junior developers.

Filtering and Business Rules That Grow Over Time

Now let us make it more realistics. In one of my projects, filtering started simple. Then business rules kept coming.

C#
var orders = dbContext.Orders
    .Where(o => o.Status == OrderStatus.Completed)
    .Where(o => o.TotalAmount > 1000)
    .Where(o => o.CreatedAt >= startDate && o.CreatedAt <= endDate)
    .ToList();

At first glance, this still looks okay. But when I checked the SQL, I noticed something important. EF Core combined all those Where calls into one SQL statement. That is good.

SQL
SELECT ...
FROM [Orders] AS [o]
WHERE [o].[Status] = 2
  AND [o].[TotalAmount] > 1000
  AND [o].[CreatedAt] BETWEEN @startDate AND @endDate

Logging here gave me confidence that chaining conditions was no the problem.

Joins and Navigation Properties

This is where things usually start to get tricky.

Using Navigation Properties

C#
var orders = dbContext.Orders
    .Where(o => o.Customer.IsVip)
    .Select(o => new
    {
        o.Id,
        o.TotalAmount,
        CustomerName = o.Customer.Name
    })
    .ToList();

When I first used queries like this, I assumed EF Core would do something inefficient. But when I checked the SQL, it was actually fine.

SQL
SELECT [o].[Id], [o].[TotalAmount], [c].[Name] AS [CustomerName]
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
WHERE [c].[IsVip] = 1

Logging taught me something important here. EF Core is smarter than we sometimes think. But you still need to verify.

The N Plus One Problem and How Logging Saved Me

I clearly remember struggling with this. I had code like this.

C#
var orders = dbContext.Orders.ToList();

foreach (var order in orders)
{
    var items = dbContext.OrderItems
        .Where(i => i.OrderId == order.Id)
        .ToList();
}

From a C# point of view, it worked. From a database point of view, it was a disaster. When I enabled SQL logging, I saw one query for oders and then one query per order for items. That was painful to watch. This is where logging really helped me understand the problem, not just read about it.

The fix was simple

C#
var orders = dbContext.Orders
    .Include(o => o.Items)
    .ToList();

After that, the SQL logs showed a single query with joins instead of dozens of queries.

Projections to Control What SQL Returns

Another lesson I learned the hard way is this. If you do not control what you select, EF Core will select everything.

C#
var orders = dbContext.Orders
    .Include(o => o.Customer)
    .ToList();

This pulls all columns from both tables. In real projects, especially with reporting or dashboards, that is not ideal. I started using projections more intentionally.

C#
var orders = dbContext.Orders
    .Select(o => new
    {
        o.Id,
        o.TotalAmount,
        CustomerName = o.Customer.Name
    })
    .ToList();

When I checked the SQL, it was much cleaner. Only the needed columns were selected.

Logging made this visible and measurable.

Debugging Queries Without Executing Them

Sometimes, I do not even want to execute the query. I just want to see the SQL.

EF Core has a very useful method.

C#
var query = dbContext.Orders
    .Where(o => o.TotalAmount > 500);

var sql = query.ToQueryString();

This saved me many times during debugging. I could copy the SQL, paste it into SQL Server Management Studio, and analyze execution plans.

In one project, this helped me identify missing indexes. Without seeing the SQL, I would never know where to start.

Tracking Versus No Tracking

Another performance issue I faced was related to tracking. By default, EF Core tracks entities.

C#
var customers = dbContext.Customers.ToList();

For read only scenarios, this tracking is unnecessary.

C#
var customers = dbContext.Customers
    .AsNoTracking()
    .ToList();

When logging SQL, the query itself looks the same. But memory usage and peformance improve. Logging plus profiling helped me understand when tracking was hurting us.

Logging Parameters and Sensitive Data

One thing to be careful about sensitive data. You can enable parameter logging.

C#
options.EnableSensitiveDataLogging();

This is useful in development. But never enable this in production if you deal with sensitive data. I learned this rule early, especially in payment related systems. Logs can easily become a security issue of you are not careful.

How Logging Helped Me Talk to Non Developers

This is a small but important point. Sometimes, I had to explain performance issues to product owners or managers. Showing them SQL logs and execution time helped make the problem concrete. Instead of saying EF Core is slow, I could say this query scans a million rows.

Logging gave me evidence, not just opinions.

Final Thoughts

EF Core is a powerful tool, but it does not remove the need to understand the SQL. Logging and debugging queries helped me grow as a developer. It helped me fix performance issues, avoid bad patterns, and build better solutions for real business problems.

If you take only one thing from this post, let it be this: “Do not guess what EF Core is doing. Log it. Read it. Learn from it”.

Sources

Here are some references I often go back to and that you can explore further.

Assi Arai
Assi Arai
Articles: 53

Related Posts

Trending now

WordPress Functions
How to Set Up a WordPress Website with Docker Compose
How to Use Microsoft.Extensions.Polly for Resilient HTTP Requests in .NET
LINQ: The Basics and Core Concepts