How to Detect EF Core N+1 Queries in ASP.NET Core Tests

How Did We Discover the N+1 Query Problem in Our EF Core Application?

During a recent project for a large-scale logistics ERP platform, we encountered a critical degradation in backend performance. Our platform relied heavily on ASP.NET Core and Entity Framework (EF) Core to manage thousands of daily shipments, inventory movements and warehouse assignments. While working on a new dashboard feature that aggregated active shipments and their associated line items, our QA team noticed that API response times were creeping up from a snappy 150 milliseconds to well over 3 seconds under moderate load.

We realized that a seemingly harmless code change had introduced a classic N+1 query problem. A developer had inadvertently dropped an .Include() statement in a LINQ query, causing EF Core to issue a separate SQL query to fetch the line items for every single shipment returned by the parent query. What was supposed to be one efficient JOIN had exploded into 51 separate database calls for a payload of 50 shipments.

While fixing the immediate issue was straightforward, ensuring it would not happen again became a primary architectural concern. We needed a reliable way to enforce strict query execution limits within our CI/CD pipeline. This challenge inspired this article so other engineering teams can avoid the same oversight. If you are looking to hire software developer teams that understand these architectural nuances, building automated guardrails against database regressions is an absolute necessity.

Why Is Catching EF Core N+1 Queries So Difficult in Production?

In a complex enterprise ERP system, the data access layer is often abstracted away behind repository patterns, CQRS handlers or service facades. Because EF Core handles the translation of LINQ to SQL implicitly, developers rarely see the raw queries being executed unless they are actively profiling the application. Features like lazy loading—or simply iterating over unpopulated navigation properties—can silently trigger massive amounts of synchronous database calls.

The core business use case for our endpoint was to retrieve a paginated list of shipments alongside their assigned vehicles and drivers. Because the test environment database only had a handful of records, the N+1 behavior went completely unnoticed during local development. It was only when the code reached a staging environment with production-like data volume that the latency became apparent.

We realized that relying solely on code reviews to catch N+1 issues is unsustainable. To truly safeguard our application, we needed our integration tests to fail if an HTTP request that was expected to execute exactly two SQL queries suddenly started executing twenty.

What Caused the Performance Bottleneck in Our ASP.NET Core API?

When analyzing the root cause, our database logs painted a grim picture. The database server CPU utilization spiked during concurrent requests to the dashboard endpoint. By inspecting the SQL Server Profiler traces, we observed a textbook N+1 symptom: a single query pulling the primary records, immediately followed by dozens of nearly identical queries fetching related entities by foreign key.

The architectural oversight was twofold. First, the data mapping logic was inadvertently triggering lazy loading via a navigation property loop. Second, our existing integration tests were validating the shape and correctness of the JSON response, but they were completely blind to how much database I/O was required to produce that response. The functional tests passed with flying colors while the underlying performance was failing spectacularly.

What Are the Best Ways to Count SQL Queries in ASP.NET Core Integration Tests?

To implement an automated assertion for SQL query execution limits, we evaluated several different approaches. Our goal was to find a clean, reliable and thread-safe method that integrated easily with ASP.NET Core’s WebApplicationFactory.

Can We Just Parse the EF Core ILogger Output?

Our first thought was to capture the standard ASP.NET Core logging output, intercept the ILogger messages emitted by EF Core and count the occurrences of “Executing DbCommand”. We quickly discarded this idea. Scraping logs is incredibly brittle. Log message templates can change between EF Core versions and dealing with string manipulation in tests leads to false positives and slow test execution.

Should We Use DiagnosticListener to Track EF Core Events?

We considered tapping into the DiagnosticListener, which EF Core uses internally to broadcast rich telemetry events. By subscribing to events like Microsoft.EntityFrameworkCore.Database.Command.CommandExecuted, we could increment a counter. While this approach is very robust, managing the subscription lifecycle within a test runner—especially ensuring thread isolation for parallel test execution—added unnecessary complexity to our test infrastructure.

Can We Leverage EF Core DbCommandInterceptor for Query Counting?

We ultimately decided that leveraging an EF Core DbCommandInterceptor was the most elegant solution. Interceptors allow you to inject custom logic right before or right after a database command is executed. By creating a custom interceptor, we could cleanly increment a counter stored in a scoped dependency injection service. Because integration tests using WebApplicationFactory resolve HTTP requests within a defined DI scope, this guaranteed that our query counts would be perfectly isolated per HTTP request, even when tests run in parallel.

How Did We Implement a Query Assertion Mechanism Using DbCommandInterceptor?

To implement the solution, we needed three components: a scoped counter service, an interceptor to increment it and a method to extract this value during our integration tests.

First, we created a simple scoped service to hold our query count:

public class SqlQueryCounter
{
    public int QueryCount { get; private set; }
    public void Increment() => QueryCount++;
}

Next, we built the custom DbCommandInterceptor. This interceptor receives the scoped SqlQueryCounter via constructor injection and increments it every time a command executes.

public class QueryCountingInterceptor : DbCommandInterceptor
{
    private readonly SqlQueryCounter _counter;
    public QueryCountingInterceptor(SqlQueryCounter counter)
    {
        _counter = counter;
    }
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result)
    {
        _counter.Increment();
        return base.ReaderExecuting(command, eventData, result);
    }
    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result, 
        CancellationToken cancellationToken = default)
    {
        _counter.Increment();
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }
}

In our integration test project, we customized the WebApplicationFactory to register these services and apply the interceptor to the DbContext. Crucially, we ensure that the interceptor pulls the scoped SqlQueryCounter from the current service provider.

services.AddScoped<SqlQueryCounter>();
services.AddDbContext<ApplicationDbContext>((sp, options) =>
{
    var counter = sp.GetRequiredService<SqlQueryCounter>();
    options.UseSqlServer("Your_Test_Connection_String")
           .AddInterceptors(new QueryCountingInterceptor(counter));
});

Finally, in our integration tests, we can now make an HTTP request and assert the exact number of queries executed. This ensures zero N+1 regressions.

[Fact]
public async Task GetShipments_ShouldExecuteExactlyTwoQueries()
{
    // Arrange
    var client = _factory.CreateClient();
    // Act
    var response = await client.GetAsync("/api/shipments");
    response.EnsureSuccessStatusCode();
    // Assert
    using var scope = _factory.Services.CreateScope();
    var counter = scope.ServiceProvider.GetRequiredService<SqlQueryCounter>();
    
    Assert.Equal(2, counter.QueryCount);
}

This implementation was a game-changer for our ERP platform. For tech leaders looking to hire dotnet developers for enterprise modernization, establishing these kinds of automated architectural constraints is a hallmark of engineering maturity.

What Architectural Lessons Can Engineering Teams Learn from Query Profiling?

Implementing this solution provided several crucial insights for our engineering teams:

• Isolate State in Tests: Using a scoped DI service ensures that parallel tests do not step on each other’s toes when counting queries. Global static variables would have caused flaky test suites.
• Test the I/O, Not Just the Output: Validating JSON responses is only half the battle. Your integration tests should enforce boundaries on database I/O, memory allocations and execution time.
• Disable Lazy Loading by Default: Explicit loading and eager loading force developers to be intentional about their data retrieval, reducing accidental N+1 queries.
• Adopt Interceptors for Observability: The EF Core interceptor pattern is incredibly powerful not just for counting queries, but for logging slow queries, injecting tenant IDs or routing reads and writes.
• Monitor Production Queries: Tests are a safety net, but production monitoring is essential. Use Application Insights or OpenTelemetry to track database command duration and frequency in real-world scenarios.

How Can You Ensure Your ASP.NET Core Apps Remain Performant?

The N+1 query problem is one of the most common performance killers in ORM-backed applications. By leveraging a custom DbCommandInterceptor and a scoped counting service, we successfully bridged the gap between functional testing and performance testing. We can now confidently refactor complex endpoints knowing our CI pipeline will catch any unintended database chatter. When you hire backend developers for scalable APIs, having these automated constraints allows the team to move fast without breaking production performance.

If your organization is struggling with application performance, backend scalability or if you want to implement rigorous test automation, our dedicated remote engineering teams are ready to help. Please contact us to discuss how we can support your technical roadmap.

Social Hashtags

#EFCore #ASPNETCore #DotNet #EntityFrameworkCore #NPlusOne #DatabasePerformance #IntegrationTesting #DbCommandInterceptor #SoftwareTesting #BackendDevelopment #DotNetDeveloper #APIPerformance #PerformanceOptimization #CICD #SQLPerformance