Mastering Database Interceptors in .NET Core Web API (Beginner to Hero)

1. Introduction

When building modern .NET Web API applications, database operations are at the heart of everything. But have you ever wondered:

• What SQL queries are being executed?
• How can I log or measure query performance?
• Can I stop dangerous commands before they hit the database?

The answer lies in database interceptors.

In this article, we’ll explore database interception in Entity Framework Core (EF Core) across .NET 6, 7, 8, and 9.
We’ll start at a beginner level (just logging queries) and work our way up to hero-level scenarios like performance monitoring, auditing, and blocking harmful SQL.

---

2. Prerequisites

A working .NET 6, 7, 8, or 9 Web API project
Basic knowledge of Entity Framework Core

Installed EF Core NuGet packages:

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer

---

3. Beginner Level: Setting Up a Web API with EF Core

Let’s start with a simple User entity and DbContext.

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<User> Users { get; set; }
}

In Program.cs, register the context:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();

app.MapGet("/users", async (AppDbContext db) => await db.Users.ToListAsync());

app.Run();

---

4. Creating a Simple Logging Interceptor

An interceptor lets us hook into SQL commands before they are sent to the database.

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

public class LoggingInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        Console.WriteLine($"[SQL LOG] ReaderExecuting: {command.CommandText}");
        return base.ReaderExecuting(command, eventData, result);
    }

    public override InterceptionResult<int> NonQueryExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        Console.WriteLine($"[SQL LOG] NonQueryExecuting: {command.CommandText}");
        return base.NonQueryExecuting(command, eventData, result);
    }
}

---

5. Intermediate Level: Measuring Performance

We can go beyond logging and measure query execution time.

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

public class PerformanceInterceptor: DbCommandInterceptor
{
    public override DbDataReader ReaderExecuted(
        DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
    {
        var elapsed = eventData?.Duration.TotalMilliseconds ?? 0;
        Console.WriteLine($"[PERF] Query took {elapsed} ms. SQL: {Truncate(command.CommandText)}");
        return base.ReaderExecuted(command, eventData, result);
    }

    public override int NonQueryExecuted(
        DbCommand command, CommandExecutedEventData eventData, int result)
    {
        var elapsed = eventData?.Duration.TotalMilliseconds ?? 0;
        Console.WriteLine($"[PERF] NonQuery took {elapsed} ms. SQL: {Truncate(command.CommandText)}");
        return base.NonQueryExecuted(command, eventData, result);
    }

    private string Truncate(string s, int len = 200) =>
        string.IsNullOrEmpty(s) ? s : (s.Length <= len ? s : s.Substring(0, len) + "...");
}

---

6. Advanced Level: Blocking Dangerous SQL

What if someone accidentally writes a DELETE without WHERE? We can block it!

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

public class SecurityInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<int> NonQueryExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        var sql = (command.CommandText ?? string.Empty).TrimStart();

        // Case-insensitive check and ignore leading comments/whitespace
        if (sql.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase)
            && !sql.IndexOf("WHERE", StringComparison.OrdinalIgnoreCase).Equals(-1) == false) // no WHERE present
        {
            // Block dangerous DELETE without WHERE
            Console.WriteLine("[SECURITY] Blocked dangerous DELETE without WHERE.");
            throw new InvalidOperationException("Blocked dangerous DELETE without WHERE clause.");
        }

        return base.NonQueryExecuting(command, eventData, result);
    }
}

---

7. Hero Level: Auditing User Actions

We can use interceptors for auditing who ran which query.

using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.AspNetCore.Http;

public class AuditSaveChangesInterceptor : SaveChangesInterceptor
{
    private readonly IHttpContextAccessor _httpContextAccessor;

    public AuditSaveChangesInterceptor(IHttpContextAccessor httpContextAccessor)
    {
        _httpContextAccessor = httpContextAccessor;
    }

    public override InterceptionResult<int> SavingChanges(DbContextEventData eventData, InterceptionResult<int> result)
    {
        var userName = _httpContextAccessor?.HttpContext?.User?.Identity?.Name ?? "anonymous";
        Console.WriteLine($"[AUDIT] User '{userName}' is calling SaveChanges at {DateTime.UtcNow:O}");
        return base.SavingChanges(eventData, result);
    }
}

---

8. Registering the Interceptor in Program.cs

Program.cs (minimal hosting; ensure interceptors are registered as IInterceptor)

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddHttpContextAccessor();

// Register interceptors as IInterceptor so GetServices<IInterceptor>() returns them
builder.Services.AddScoped<IInterceptor, LoggingInterceptor>();
builder.Services.AddScoped<IInterceptor, PerformanceInterceptor>();
builder.Services.AddScoped<IInterceptor, SecurityInterceptor>();
builder.Services.AddScoped<IInterceptor, AuditSaveChangesInterceptor>();

// Add DbContext and inject resolved interceptors into EF Core options
builder.Services.AddDbContext<AppDbContext>((serviceProvider, options) =>
{
    var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") ??
                           "Server=(localdb)\\mssqllocaldb;Database=EfInterceptorsDemo;Trusted_Connection=True;";

    options.UseSqlServer(connectionString);

    // Resolve all registered IInterceptor implementations
    var interceptors = serviceProvider.GetServices<IInterceptor>().ToArray();
    if (interceptors.Any())
    {
        options.AddInterceptors(interceptors);
    }

    // Helpful for debugging — optional in production
    options.EnableSensitiveDataLogging();
    options.LogTo(Console.WriteLine, LogLevel.Information);
});

builder.Services.AddControllers();
var app = builder.Build();

app.MapControllers();
app.Run();

---

9. UsersController (controller + simple business logic)

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly AppDbContext _db;

    public UsersController(AppDbContext db) => _db = db;

    [HttpGet]
    public async Task<IActionResult> GetUsers()
    {
        var users = await _db.Users.ToListAsync(); // triggers ReaderExecuting / ReaderExecuted
        return Ok(users);
    }

    [HttpPost]
    public async Task<IActionResult> AddUser([FromQuery] string name)
    {
        if (string.IsNullOrWhiteSpace(name)) return BadRequest("Name required");
        _db.Users.Add(new User { Name = name });
        await _db.SaveChangesAsync(); // triggers SaveChangesInterceptor + NonQueryExecuting/NonQueryExecuted
        return Ok();
    }

    [HttpDelete("dangerous")]
    public IActionResult AttemptDangerousDelete()
    {
        // This intentionally sends 'DELETE FROM Users' without WHERE to trigger the security interceptor
        _db.Database.ExecuteSqlRaw("DELETE FROM Users");
        return Ok("Attempted dangerous delete");
    }
}

---

10. How to test (step-by-step)

i. Run the app (F5 or dotnet run). Watch console.

ii. Create DB / Apply migrations (or ensure DB exists). Example quick seed:

• Use EF migrations or ensure Users table exists. For quick test you can create with SQL Server LocalDB or dotnet ef migrations add Init && dotnet ef database update.

iii. Test Logging + Performance

• GET http://localhost:5000/api/users
• Expected console output (example):

[SQL LOG] ReaderExecuting: SELECT [u].[Id], [u].[Name] FROM [Users] AS [u]
[PERF] Query took 12 ms. SQL: SELECT [u].[Id], [u].[Name] FROM [Users] AS [u]

iv. Test Audit

POST http://localhost:5000/api/users?name=Alice

Expected console:

[AUDIT] User 'anonymous' is calling SaveChanges at 2025-09-01T...
[SQL LOG] NonQueryExecuting: INSERT INTO ...
[PERF] NonQuery took 15 ms. SQL: INSERT INTO ...

v. Test Security (blocked DELETE)

DELETE http://localhost:5000/api/users/dangerous

Expected API response: 500 Internal Server Error

Console:

[SECURITY] Blocked dangerous DELETE without WHERE.

The exception thrown by the interceptor will be visible in logs and will prevent row deletion.

---

11. Best Practices

• Keep interceptors lightweight (avoid long-running logic).
• Use structured logging (Serilog, Seq, Application Insights) instead of Console.WriteLine.
• Separate concerns into multiple interceptors (logging, auditing, security).
• Test performance impact before deploying to production.

---

12. Conclusion

Database Interceptors in Entity Framework Core (EF Core) are an underused but powerful feature that can transform how you interact with your database layer. They allow you to observe, manipulate, and enhance database operations seamlessly — all without altering your application logic directly.

Here’s why interceptors are so valuable:

Log SQL Queries

• Capture every executed SQL statement.
• Helps with debugging, troubleshooting, and understanding query patterns.
• Ensures visibility into how EF Core translates LINQ into SQL.

Monitor Performance

• Measure query execution time in real-time.
• Detect slow or inefficient queries.
• Optimize database performance with minimal overhead.

Block Risky Commands

• Intercept and prevent accidental destructive operations like TRUNCATE or DROP.
• Enforce business rules and security policies at the database level.
• Reduce risks of data corruption or loss.

Add Auditing

• Track user activity, including inserts, updates, and deletes.
• Automatically store metadata such as timestamps, user IDs, and IP addresses.
• Ensure compliance with auditing and regulatory requirements.

In short, database interceptors elevate you from beginner-level logging to hero-level auditing, monitoring, and security. They give developers deep insight, precise control, and safer interactions with the database — all while keeping the application code clean and maintainable.

---

13. Code Download

The code developed during this article can be found here.