High-Performance .NET Core APIs: Defeating N+1 Queries and Leveraging EF Core Interceptors for Performance Auditing

Introduction: The 3 AM Production Incident

It was 3:17 AM on a Tuesday. Our on-call engineer pinged the team Slack: the order fulfillment API was timing out. Response times had tanked from 200ms to 8 seconds overnight. No code had changed. No infrastructure was added. Yet the database CPU meter was pegged at 95%.

After digging through Azure Application Insights, we found the culprit: a simple .ToList() inside a loop, spawning 1,400 queries per request instead of one. The ORM was lazily loading related entities for each order—a silent performance killer masquerading as clean, maintainable code.

If you’re running a broader .NET stack on Azure, pairing performance work like this with a wider look at your Microsoft solutions roadmap usually catches issues like this one earlier. 

That incident taught me something every .NET developer learns eventually: Object-Relational Mappers are powerful, but they’re also mines waiting to detonate under production load. Entity Framework Core is no exception.

In this post, I’ll walk you through the two most dangerous performance gotchas I’ve encountered in enterprise .NET APIs:

1.   The N+1 query antipattern — how innocent-looking code turns into a database DoS
2.   How to audit and track database performance using EF Core Interceptors without polluting your business logic

By the end, you’ll have battle-tested patterns and production-ready code to keep your database healthy and your on-call engineer sleeping soundly.

 

Part 1: The N+1 Query Antipattern — The Silent Killer

What Is N+1, Really?

At its core, the N+1 problem is a lazy-loading curse. When you fetch a parent entity (say, an Order) and then access its navigation properties (its Items collection), Entity Framework Core by default does not fetch those related entities upfront. Instead, it fires off a separate query the moment you touch that navigation property.

Here’s the innocent-looking code that causes the carnage:

// The Setup: A simple e-commerce domain
public class Order
{
public int Id { get; set; }
public DateTime CreatedAt { get; set; }
public ICollection<OrderItem> Items { get; set; } = new List<OrderItem>();
}

public class OrderItem
{
public int Id { get; set; }
public int OrderId { get; set; }
public string ProductName { get; set; }
public decimal Price { get; set; }
}

// The Culprit: What seemed fine in development
public async Task<List<OrderDto>> GetAllOrdersAsync()
{
var orders = await _context.Orders.ToListAsync();

var result = new List<OrderDto>();
foreach (var order in orders)
{
    result.Add(new OrderDto
    {
        Id = order.Id,
        CreatedAt = order.CreatedAt,
        // This single line triggers a database query PER ORDER
        ItemCount = order.Items.Count
    });
}

return result;
}

What happens under the hood:

•     Query 1: SELECT * FROM Orders — fetches 500 orders
•     Queries 2-501: SELECT * FROM OrderItems WHERE OrderId = @id — one per order

Total: 501 queries. At scale, this melts your connection pool.

Why This Matters (And Why It Caught Us Off Guard)

In development, you’re probably working with 10-50 test records. Latency is so low that 501 queries still feel fast—maybe 50-100ms total. Your laptop’s SQL Server instance is screaming with spare cycles.

But production is different:

•     Network latency multiplies: Each query incurs a TCP round-trip. A remote Azure SQL Database might add 5-10ms per round-trip. Suddenly, 501 queries = 2500-5000ms of pure network overhead.
•     Connection pooling becomes a bottleneck: Every concurrent request competing for a slice of the connection pool. If your pool has 100 connections and you get 50 concurrent requests, each running 501 queries, you’re queuing requests waiting for connection availability.
•     Database CPU spikes unpredictably: The SQL Server engine isn’t optimized for query storms. Parameter plan caching gets thrashed. Lock contention increases. That 500-order request cascades into a 5-second wait for everyone behind you.

I’ve seen this pattern collapse a database that handled 1,000 requests/second just fine—until one endpoint started lazily loading. Suddenly, that endpoint alone was generating 500,000 queries/second.

The Traditional Workarounds (And Why They Failed)

Before EF Core matured, developers had several escape hatches:

1. Manually Writing Raw SQL (Dapper)

// The old way: skip the ORM entirely for hot paths
using (var connection = new SqlConnection(_connectionString))
{
var sql = @”
    SELECT o.Id, o.CreatedAt, COUNT(oi.Id) as ItemCount
    FROM Orders o
    LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
    GROUP BY o.Id, o.CreatedAt”;

var orders = await connection.QueryAsync<OrderDto>(sql);
return orders.ToList();
}

Problem: You’ve just split your data access logic. Some queries live in repositories using EF Core. Others live as raw SQL strings scattered across controllers and services. Onboarding new engineers becomes a nightmare. Refactoring the schema breaks things silently.

2. Explicit .Include() Chaining (The Middle Ground)

var orders = await _context.Orders
.Include(o => o.Items)
.ToListAsync();

Problem: This works until you have nested relationships. .Include().ThenInclude().ThenInclude() chains become unreadable. Worse, if you have multiple collection properties, you risk Cartesian explosion—row count multiplies for each collection joined.

// This looks innocent…
var orders = await _context.Orders
.Include(o => o.Items)
.Include(o => o.Shipments)
.Include(o => o.Payments)
.ToListAsync();

// But SQL-wise, it’s doing a FULL OUTER JOIN chain.
// If Order 1 has 5 Items, 3 Shipments, and 2 Payments,
// that’s 5*3*2 = 30 rows just to represent that single order!
// Your result set bloats by 30x. More network traffic. More memory.

 

The Modern .NET Core Solution: Strategic Eager Loading

EF Core in .NET 6+ gives us surgical tools to eliminate N+1 without sacrificing maintainability.

Strategy 1: Eager Loading with .Include() (For Simple Cases)

When you have one or two collection relationships, explicit .Include() is your friend:

public class OrderRepository
{
private readonly AppDbContext _context;

public OrderRepository(AppDbContext context)
{
    _context = context;
}

public async Task<List<OrderDto>> GetOrdersWithItemsAsync()
{
    // Eager load the Items collection upfront
    var orders = await _context.Orders
        .Include(o => o.Items)
        .AsNoTracking()  // Skip change tracking for read-only operations
        .Select(o => new OrderDto
        {
            Id = o.Id,
            CreatedAt = o.CreatedAt,
            ItemCount = o.Items.Count,
            TotalPrice = o.Items.Sum(i => i.Price)
        })
        .ToListAsync();

    return orders;
}
}

What’s happening:

•     .Include(o => o.Items) generates a single SQL query with a LEFT JOIN, fetching orders and items in one round-trip.
•     .AsNoTracking() tells EF Core, “I’m not modifying these entities,” which bypasses the change tracker overhead. For read-only queries, this is a ~15% performance win.
•     .Select() projects directly to DTO, avoiding unnecessary entity materialization.

Generated SQL (simplified):

SELECT o.Id, o.CreatedAt, oi.Id, oi.OrderId, oi.ProductName, oi.Price
FROM Orders o
LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
ORDER BY o.Id

One query. Done.

Strategy 2: Split Queries for Multiple Collections (Cartesian Explosion Prevention)

When you have multiple collection relationships, use .AsSplitQuery():

public async Task<List<OrderDetailDto>> GetOrderDetailsAsync()
{
var orders = await _context.Orders
    .Include(o => o.Items)
    .Include(o => o.Shipments)
    .Include(o => o.Payments)
    .AsSplitQuery()  // Splits this into 4 separate queries instead of 1 bloated join
    .AsNoTracking()
    .ToListAsync();

return orders.Select(o => new OrderDetailDto
{
    Id = o.Id,
    Items = o.Items.Select(i => new { i.ProductName, i.Price }).ToList(),
    Shipments = o.Shipments.Select(s => new { s.TrackingNumber, s.Status }).ToList(),
    Payments = o.Payments.Select(p => new { p.Method, p.Amount }).ToList()
}).ToList();
}

Generated SQL:

— Query 1: Fetch orders
SELECT o.Id, o.CreatedAt FROM Orders o

— Query 2: Fetch all items for those orders
SELECT oi.* FROM OrderItems oi
WHERE oi.OrderId IN (…)

— Query 3: Fetch all shipments for those orders
SELECT s.* FROM Shipments s
WHERE s.OrderId IN (…)

— Query 4: Fetch all payments for those orders
SELECT p.* FROM Payments p
WHERE p.OrderId IN (…)

Why this is smarter:

•     Instead of a 30x row explosion, you get 4 focused queries.
•     Each query is lean and indexed efficiently.
•     Total network traffic is often less than the Cartesian explosion approach.

Pro-Tip: .AsSplitQuery() was introduced in EF Core 5.0. If you’re on .NET Framework or EF Core 3.1, you need to hand-roll this pattern using multiple queries and client-side joining. Upgrade if you can.

Strategy 3: Projection-Based Loading (The Gold Standard)

The absolute best way to beat N+1 is to never materialize unnecessary entities in the first place. Project directly to DTOs:

public class OrderQueryService
{
private readonly AppDbContext _context;

public OrderQueryService(AppDbContext context)
{
    _context = context;
}

// Returns only what we need, in one query, zero change tracking overhead
public async Task<List<OrderSummaryDto>> GetOrderSummariesAsync(int skip = 0, int take = 50)
{
    var orderSummaries = await _context.Orders
        .OrderByDescending(o => o.CreatedAt)
        .Skip(skip)
        .Take(take)
        .Select(o => new OrderSummaryDto
        {
            Id = o.Id,
            CreatedAt = o.CreatedAt,
            ItemCount = o.Items.Count,
            TotalPrice = o.Items.Sum(i => i.Price),
            CustomerName = o.Customer.Name,  // Navigation to Customer
            LatestShipment = o.Shipments
                .OrderByDescending(s => s.CreatedAt)
                .Select(s => new ShipmentSummaryDto
                {
                    TrackingNumber = s.TrackingNumber,
                        Status = s.Status
                })
                .FirstOrDefault()
        })
        .AsNoTracking()
        .ToListAsync();

    return orderSummaries;
}
}

public class OrderSummaryDto
{
public int Id { get; set; }
public DateTime CreatedAt { get; set; }
public int ItemCount { get; set; }
public decimal TotalPrice { get; set; }
public string CustomerName { get; set; }
public ShipmentSummaryDto LatestShipment { get; set; }
}

public class ShipmentSummaryDto
{
public string TrackingNumber { get; set; }
public string Status { get; set; }
}

Why this rocks:

•     EF Core translates your LINQ projection into a single, optimized SQL query.
•     Only the columns you need are fetched from the database.
•     Zero change tracking overhead.
•     The DTO shape forces you to think about your API contract upfront.

Generated SQL (simplified):

SELECT
o.Id,
o.CreatedAt,
COUNT(oi.Id) as ItemCount,
SUM(oi.Price) as TotalPrice,
c.Name as CustomerName,
(SELECT TOP 1 s.TrackingNumber FROM Shipments s
  WHERE s.OrderId = o.Id ORDER BY s.CreatedAt DESC) as LatestShipmentTrackingNumber
FROM Orders o
LEFT JOIN Customers c ON o.CustomerId = c.Id
LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
GROUP BY o.Id, o.CreatedAt, c.Name

One query. Efficient aggregations pushed to SQL. Beautiful.

When NOT to Eager Load (The Gotcha)

// ❌ DON’T DO THIS
public async Task<OrderDto> GetOrderByIdAsync(int orderId)
{
return await _context.Orders
    .Include(o => o.Items)
    .Include(o => o.Shipments)
    .Include(o => o.Payments)
    .Include(o => o.Payments).ThenInclude(p => p.PaymentTransactions)
    .FirstOrDefaultAsync(o => o.Id == orderId);
// You’re fetching every relationship, even if the endpoint only needs Items.
// Over-fetching defeats the purpose of eager loading.
}

// ✅ DO THIS
public async Task<OrderDto> GetOrderByIdAsync(int orderId)
{
return await _context.Orders
    .Include(o => o.Items)
    .AsNoTracking()
    .FirstOrDefaultAsync(o => o.Id == orderId);
// Only load what you need.
}

The lesson: Eager loading is powerful, but it’s also a loaded gun. Load only the relationships your specific query requires.

 

Part 2: EF Core Interceptors for Performance Auditing & Telemetry

You’ve eliminated N+1 queries. Great. But how do you know a slow query isn’t lurking somewhere? How do you track database performance in production without adding logging to every repository method?

Enter EF Core Interceptors.

The Problem: Where Are Your Slow Queries Hiding?

Scenario: Your API runs smoothly for months. Then one day, response times degrade by 30%. You flip open Application Insights. The database calls are the culprit—but which ones? Your repository has 50 different query methods. Are they all slow, or is one query going haywire?

Traditional approaches fail here:

Approach 1: Overriding SaveChanges (Clutters Your DbContext)

// ❌ Messy and limited to writes
public class AppDbContext : DbContext
{
private readonly ILogger<AppDbContext> _logger;

public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
    var sw = Stopwatch.StartNew();
    var result = await base.SaveChangesAsync(cancellationToken);
    sw.Stop();

    if (sw.ElapsedMilliseconds > 500)
    {
            _logger.LogWarning(“SaveChanges took {DurationMs}ms”, sw.ElapsedMilliseconds);
    }

    return result;
}
}

Problems:

•     Only tracks SaveChanges (writes), not reads.
•     Clutters your DbContext with infrastructure concerns.
•     No visibility into individual query details.

Approach 2: Relying on APM Tools Alone (Limited Control)

Application Insights, Datadog, and New Relic give you aggregate metrics: “Your database layer took 500ms.” But they don’t give you fine-grained control over what you log or how you act on it. You can’t easily log the actual SQL or parameter values for debugging.

What Are EF Core Interceptors?

Interceptors are hooks into the EF Core pipeline that let you observe (or modify) database commands before and after they execute. Think of them as middleware for your data access layer.

EF Core provides several interceptor interfaces:

•     ICommandInterceptor — Fires before/after command execution (reads & writes)
•     ISaveChangesInterceptor — Fires before/after SaveChanges
•     IConnectionInterceptor — Fires on connection open/close
•     ITransactionInterceptor — Fires on transaction begin/commit/rollback

For performance auditing, ICommandInterceptor is your workhorse. Here’s why: it intercepts every SQL command (SELECT, INSERT, UPDATE, DELETE) and gives you exact timing, the actual SQL text, and even parameter values if you need them.

The Modern Solution: Custom Command Interceptors

Let me build a production-grade interceptor from scratch:

Step 1: Create the Interceptor Class

using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;
using System.Data.Common;
using System.Diagnostics;

namespace MyApp.Infrastructure.Data.Interceptors
{
/// <summary>
/// Intercepts EF Core commands to log performance metrics and identify slow queries.
/// Integrates with structured logging and APM tools.
/// </summary>
public class PerformanceAuditingInterceptor : DbCommandInterceptor
{
    private readonly ILogger<PerformanceAuditingInterceptor> _logger;
    private readonly PerformanceAuditingOptions _options;

    // Thread-local stopwatch storage to measure query duration
    private static readonly object _stopwatchKey = new();

    public PerformanceAuditingInterceptor(
            ILogger<PerformanceAuditingInterceptor> logger,
        PerformanceAuditingOptions options = null)
    {
        _logger = logger;
        _options = options ?? PerformanceAuditingOptions.Default;
    }

    /// <summary>
    /// Fires before command execution. Start timing.
    /// </summary>
    public override ValueTask<DbCommand> CommandCreatedAsync(
        CommandEndEventData eventData,
        DbCommand result,
        CancellationToken cancellationToken = default)
    {
        // Store a stopwatch in the diagnostic source state for later retrieval
        eventData.InterceptionState ??= new Dictionary<object, object>();
        ((Dictionary<object, object>)eventData.InterceptionState)[_stopwatchKey] = Stopwatch.StartNew();

        return base.CommandCreatedAsync(eventData, result, cancellationToken);
    }

    /// <summary>
    /// Fires after a non-query command executes (INSERT, UPDATE, DELETE).
    /// </summary>
    public override async ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command,
        CommandExecutedEventData eventData,
        DbDataReader result,
        CancellationToken cancellationToken = default)
    {
            LogCommandPerformance(command, eventData.Duration);
        return await base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
    }

    /// <summary>
    /// Fires after a scalar command (COUNT, MAX, etc.) executes.
    /// </summary>
    public override async ValueTask<object> ScalarExecutedAsync(
        DbCommand command,
        CommandExecutedEventData eventData,
        object result,
        CancellationToken cancellationToken = default)
    {
            LogCommandPerformance(command, eventData.Duration);
        return await base.ScalarExecutedAsync(command, eventData, result, cancellationToken);
    }

    /// <summary>
    /// Fires after a non-query command (INSERT, UPDATE, DELETE) executes and rows are affected.
    /// </summary>
    public override async ValueTask<int> NonQueryExecutedAsync(
        DbCommand command,
        CommandExecutedEventData eventData,
        int result,
        CancellationToken cancellationToken = default)
    {
            LogCommandPerformance(command, eventData.Duration, rowsAffected: result);
        return await base.NonQueryExecutedAsync(command, eventData, result, cancellationToken);
    }

    /// <summary>
    /// Core logging logic. Fired after any command completes.
    /// </summary>
    private void LogCommandPerformance(DbCommand command, TimeSpan duration, int? rowsAffected = null)
    {
        var durationMs = duration.TotalMilliseconds;

        // Only log if duration exceeds threshold
        if (durationMs < _options.SlowQueryThresholdMs)
        {
            return;
        }

        var commandText = command.CommandText;
        var parameters = FormatParameters(command.Parameters);

        // Determine severity: warn if slow, error if critically slow
        var level = durationMs > _options.CriticalQueryThresholdMs
            ? LogLevel.Error
            : LogLevel.Warning;

        _logger.Log(
            level,
            “Slow database query detected. Duration: {DurationMs}ms, Command: {CommandText}, Parameters: {Parameters}{RowsAffected}”,
            durationMs,
            commandText,
            parameters,
            rowsAffected.HasValue ? $”, Rows Affected: {rowsAffected}” : string.Empty);

        // Optional: Send to APM tool
        if (_options.SendToAPM)
        {
                RecordAPMMetric(commandText, durationMs, level);
        }
    }

    /// <summary>
    /// Formats command parameters for readable logging.
    /// </summary>
    private static string FormatParameters(DbParameterCollection parameters)
    {
        if (parameters.Count == 0)
        {
            return “None”;
        }

        var formatted = new System.Text.StringBuilder();
        foreach (DbParameter param in parameters)
        {
            var value = param.Value ?? “NULL”;
                formatted.Append($”{param.ParameterName}={value}, “);
        }

        return formatted.ToString().TrimEnd(‘,’, ‘ ‘);
    }

    /// <summary>
    /// Sends performance metrics to an APM tool (e.g., Application Insights, Datadog).
    /// </summary>
    private static void RecordAPMMetric(string commandText, double durationMs, LogLevel level)
    {
        // This is a placeholder. In production, you’d integrate with your APM tool.
        // Example with Application Insights:
        // TelemetryClient.TrackEvent(“SlowDatabaseQuery”,
        // new Dictionary<string, string> { { “query”, commandText } },
        // new Dictionary<string, double> { { “durationMs”, durationMs } });
    }
}

/// <summary>
/// Configuration options for the performance auditing interceptor.
/// </summary>
public class PerformanceAuditingOptions
{
    /// <summary>
    /// Queries slower than this threshold (in milliseconds) will be logged as warnings.
    /// Default: 500ms
    /// </summary>
    public int SlowQueryThresholdMs { get; set; } = 500;

    /// <summary>
    /// Queries slower than this threshold will be logged as errors.
    /// Default: 2000ms (2 seconds)
    /// </summary>
    public int CriticalQueryThresholdMs { get; set; } = 2000;

    /// <summary>
    /// Whether to send metrics to your APM tool.
    /// </summary>
    public bool SendToAPM { get; set; } = true;

    /// <summary>
    /// Default configuration: 500ms slow query threshold, 2s critical threshold.
    /// </summary>
    public static PerformanceAuditingOptions Default => new();
}
}

Step 2: Register the Interceptor in Dependency Injection

// In Program.cs (or Startup.cs for older .NET Core)
using MyApp.Infrastructure.Data.Interceptors;

var builder = WebApplicationBuilder.CreateBuilder(args);

// Add the performance auditing interceptor
builder.Services.AddSingleton<PerformanceAuditingInterceptor>();

// Register the DbContext with the interceptor
builder.Services.AddDbContext<AppDbContext>((provider, options) =>
{
var interceptor = provider.GetRequiredService<PerformanceAuditingInterceptor>();

options
        .UseSqlServer(builder.Configuration.GetConnectionString(“DefaultConnection”))
    .AddInterceptors(interceptor);
});

var app = builder.Build();
// … rest of configuration

Step 3: Advanced Usage — Contextual Interception

Sometimes you want to intercept only certain queries or collect additional context. Here’s a scoped interceptor that respects feature flags or operation contexts:

public class ContextualPerformanceInterceptor : DbCommandInterceptor
{
private readonly ILogger<ContextualPerformanceInterceptor> _logger;
private readonly IHttpContextAccessor _httpContextAccessor;  // Or IOperationContext

public ContextualPerformanceInterceptor(
        ILogger<ContextualPerformanceInterceptor> logger,
    IHttpContextAccessor httpContextAccessor)
{
    _logger = logger;
    _httpContextAccessor = httpContextAccessor;
}

public override async ValueTask<DbDataReader> ReaderExecutedAsync(
    DbCommand command,
    CommandExecutedEventData eventData,
    DbDataReader result,
    CancellationToken cancellationToken = default)
{
    var durationMs = eventData.Duration.TotalMilliseconds;

    if (durationMs > 500)
    {
        var httpContext = _httpContextAccessor?.HttpContext;
        var requestId = httpContext?.TraceIdentifier ?? “N/A”;
        var userId = httpContext?.User?.FindFirst(“sub”)?.Value ?? “Anonymous”;
        var endpoint = httpContext?.Request.Path ?? “N/A”;

        _logger.LogWarning(
            “Slow query in {Endpoint} by {UserId} (RequestId: {RequestId}). ” +
            “Duration: {DurationMs}ms. Query: {Query}”,
            endpoint,
            userId,
            requestId,
            durationMs,
            command.CommandText);
    }

    return await base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
}
}

This version enriches slow query logs with HTTP context—which user triggered it, which endpoint, which request ID—making debugging production issues dramatically easier.

Step 4: Real-World Scenario — Catching the N+1 Red-Handed

Here’s how the interceptor helps you catch N+1 problems in the wild:

// Your endpoint
[HttpGet(“orders”)]
public async Task<IActionResult> GetOrders()
{
var orders = await _context.Orders.ToListAsync();

var result = new List<OrderDto>();
foreach (var order in orders)  // ❌ This still has N+1!
{
    result.Add(new OrderDto
    {
        Id = order.Id,
        ItemCount = order.Items.Count  // Lazy load per order
    });
}

return Ok(result);
}

What the interceptor logs:

warn: MyApp.Infrastructure.Data.Interceptors.PerformanceAuditingInterceptor[0]
  Slow database query detected. Duration: 156ms, Command: SELECT [o].[Id], [o].[CreatedAt]
  FROM [Orders] AS [o], Parameters: None

warn: MyApp.Infrastructure.Data.Interceptors.PerformanceAuditingInterceptor[0]
  Slow database query detected. Duration: 8ms, Command: SELECT [o].[Id], [o].[ProductName], [o].[Price], [o].[OrderId]
  FROM [OrderItems] AS [o]
  WHERE [o].[OrderId] = @__order_Id_0, Parameters: @__order_Id_0=1

warn: MyApp.Infrastructure.Data.Interceptors.PerformanceAuditingInterceptor[0]
  Slow database query detected. Duration: 7ms, Command: SELECT [o].[Id], [o].[ProductName], [o].[Price], [o].[OrderId]
  FROM [OrderItems] AS [o]
  WHERE [o].[OrderId] = @__order_Id_0, Parameters: @__order_Id_0=2

… (repeated 500+ times)

Boom. The logs make it crystal clear: one SELECT on Orders, then hundreds of identical SELECTs with different OrderId parameters. Classic N+1. Your interceptor just caught the culprit.

Additional Interceptor Patterns

Pattern 1: Detecting Cartesian Explosions

public class CartesianExplosionDetector : DbCommandInterceptor
{
private readonly ILogger<CartesianExplosionDetector> _logger;

public CartesianExplosionDetector(ILogger<CartesianExplosionDetector> logger)
{
    _logger = logger;
}

public override async ValueTask<DbDataReader> ReaderExecutedAsync(
    DbCommand command,
    CommandExecutedEventData eventData,
    DbDataReader result,
    CancellationToken cancellationToken = default)
{
    // Count result rows
    var rowCount = 0;
    while (await result.ReadAsync(cancellationToken))
    {
        rowCount++;
    }

    // If query returned way more rows than expected, warn
    if (rowCount > 10000)
    {
        _logger.LogWarning(
            “Cartesian explosion detected! Query returned {RowCount} rows. ” +
            “This often indicates missing .AsSplitQuery(). Query: {Query}”,
            rowCount,
                command.CommandText);
    }

    return result;
}
}

Pattern 2: Change Tracking Impact Analysis

public class ChangeTrackingInterceptor : DbCommandInterceptor
{
private readonly ILogger<ChangeTrackingInterceptor> _logger;

public ChangeTrackingInterceptor(ILogger<ChangeTrackingInterceptor> logger)
{
    _logger = logger;
}

public override async ValueTask<int> NonQueryExecutedAsync(
    DbCommand command,
    CommandExecutedEventData eventData,
    int result,
    CancellationToken cancellationToken = default)
{
    // Flag UPDATE/DELETE statements without WHERE clauses (dangerous!)
    if ((command.CommandText.Contains(“UPDATE”) || command.CommandText.Contains(“DELETE”))
        && !command.CommandText.Contains(“WHERE”))
    {
        _logger.LogError(
            “DANGER: Unbounded UPDATE/DELETE detected! This will modify ALL rows! Query: {Query}”,
            command.CommandText);
    }

    return await base.NonQueryExecutedAsync(command, eventData, result, cancellationToken);
}
}

Catching unbounded UPDATE/DELETE statements before they hit production is exactly the kind of risk that regular security testing services are designed to catch earlier in the pipeline — for a broader look at how modern applications are getting hit, see Protecting Applications Against Modern Cyber Threats.

Architectural Takeaways & Integration Strategy

The Performance Pyramid

Think of database performance optimization as a pyramid:

                    Application Caching (Redis)
              ↓ (Rarely needed if DB is optimized)
       
            APM & Monitoring Tools
        ↓ (Tells you *what*, not *why*)
   
EF Core Query Optimization (The Foundation)
  ↓ (Eager loading, projections, interceptors)

When you do reach the caching layer — after query optimization is already solid — that’s the kind of infrastructure work covered under cloud application development services, since Redis and edge caching decisions tie directly into your broader cloud architecture.

The most common mistake: teams jump to Redis, APM tools, and query caching without first fixing their EF Core queries. Fix the root cause first. If you have N+1 queries, no cache layer will save you.

Integration Into Existing Codebases

Retrofitting performance patterns into a live legacy codebase without breaking existing functionality is exactly what dedicated maintenance and support services are built for — gradual, non-breaking changes to systems already in production.

If you’re retrofitting these patterns into a legacy codebase with thousands of existing queries, do it gradually:

Phase 1: Deploy the Interceptor (Non-Breaking)

Add the interceptor to production in a logging-only mode. Let it run for a week without any code changes. Analyze the logs. Identify the top 20 slow queries.

// Log to a structured logging sink (e.g., Application Insights, Splunk, ELK)
// That makes analysis easier than sifting through stderr

Getting that logging pipeline wired into your observability stack cleanly — without a manual, error-prone rollout — is where solid DevOps services and solutions pay off. 

Phase 2: Fix the Worst Offenders

Take the top 20 slow queries. Convert them to projections or add explicit .Include() statements. Test. Deploy.

Phase 3: Raise the Bar

Once your baselines are healthy, lower the slow query threshold from 500ms to 200ms. Fix those. Then 100ms. Incrementally.

Conclusion: Sleep Well, On-Call Engineer

We started this post with a 3 AM incident. By the end, you have the tools to prevent that incident from ever happening:

1.   Eliminate N+1 queries using strategic eager loading, split queries, and projections. Think about what your query needs before you write it.
2.   Audit performance relentlessly using EF Core Interceptors. Get visibility into your database layer without polluting your business logic. Make slow queries obvious.
3.   Integrate incrementally into existing codebases. Deploy the interceptor. Analyze. Fix the worst offenders. Repeat.

Entity Framework Core is powerful. It’s also easy to shoot yourself in the foot with. But with these patterns—aggressive projection, split queries, and performance auditing—you can build APIs that scale gracefully and keep your team sleeping soundly at night.

Now go refactor that repository method. Your database is waiting.

FAQs 

Q1. What causes the N+1 query problem in EF Core?
It happens when you fetch a parent entity and then access a navigation property (like an Items collection) without eager loading it first. EF Core fires a separate query for that related data on every iteration of a loop, turning one intended query into hundreds.

Q2. What are EF Core Interceptors used for?
EF Core Interceptors let you hook into the pipeline to observe or modify database commands before and after they execute — commonly used for logging slow queries, tracking performance, or flagging dangerous operations like unbounded UPDATE/DELETE statements, without adding logging code to every repository method.

Q3. How do you fix N+1 queries in ASP.NET Core APIs?
Use .Include() for simple eager loading, .AsSplitQuery() when multiple collections would cause a Cartesian explosion, or — best of all — project directly to a DTO with .Select() so EF Core generates one optimized query instead of materializing full entities.