You can only catch so many errors when working with databases by looking at the code you write. It’s only when you start using your code against production data sets do you realize there’s an obvious mistake. Sure, your app may still technically work, but it’s limping along, performing inefficient queries and ultimately providing a less-than-ideal experience to your users.

I’ve recently written a blog post about Entity Framework Core Interceptors, but I thought I’d revisit the topic because I had an idea. Is there enough information given to an interceptor to warn us of poor querying decisions? In fact, there is!

In this post, you’ll see how to implement a specific type of interceptor to catch greedy queries that can be the source of potential performance issues.

What’s an Interceptor

With the latest release of Entity Framework Core, the team has focused on extensibility points for the EF Core user base.

Entity Framework Core (EF Core) interceptors enable interception, modification, and/or suppression of EF Core operations. This includes low-level database operations such as executing a command, as well as higher-level operations, such as calls to SaveChanges. – Microsoft Docs

In this post, you’ll use a specific kind of interceptor, IDbCommandInterceptor and implement the DataReaderClosing method. Note that all interfaces have a default implementation, so your IDE tooling won’t prompt you to implement any methods. Instead, you’ll need to learn and discover which methods are essential to your use case.

The Problem

Typically, folks forget to limit their queries to a reasonably-sized set of results. Instead, they may accidentally pull ALL the data and filter it on the client. Inefficient querying can lead to performance issues down the road; as data sets grow, more clients hit your querying code paths, and resources dwindle.

You can catch this issue by implementing an IDbCommandInterceptor, but first, let’s look at an offending code snippet.

using var db = new CustomerContext();
var customers = db.Customers.ToList();

While an analyzer may say there’s an issue here, it can never fully understand the scope of the data in the database. For example, you may only ever have five customers or 5000 customers. The only way to detect these issues is to run the query against your database.

Let’s write the interceptor to look at the ReadCount of any DataReader created by EF Core. If results exceed your expectations, warn developers using the configured logging mechanisms.

public class MaxCountExceededInterceptor : IDbCommandInterceptor
{
    private readonly int _maxCount;

    public MaxCountExceededInterceptor(int maxCount = 100)
    {
        _maxCount = maxCount;
    }

    public InterceptionResult DataReaderClosing(
        DbCommand command, 
        DataReaderClosingEventData eventData,
        InterceptionResult result)
    {
        if (eventData.ReadCount > _maxCount)
        {
            var factory = eventData.Context!.Database.GetService<ILoggerFactory>();
            var logger = factory.CreateLogger<MaxCountExceededInterceptor>();
            logger.LogWarning("Result count exceeded max of {MaxCount} with query {CommandText}",
                _maxCount,
                eventData.Command.CommandText
            );
        }

        return result;
    }
}

The code uses the ILoggerFactory registered with the DbContext implementation. It’s critical that you register a logger factory. The use of .LogTo will not log any messages..

Let’s look at how you might register this interceptor.

public class CustomerContext : DbContext
{
    private static readonly 
        MaxCountExceededInterceptor 
        MaxCountExceededInterceptor
        = new(maxCount: 100);
    
    public DbSet<Customer> Customers => Set<Customer>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseLoggerFactory(LoggerFactory.Create(b => {
                b.AddConsole();
            }))
            .AddInterceptors(MaxCountExceededInterceptor)
            .UseSqlite("Data Source = customers.db");
}

The above code uses the package Microsoft.Extensions.Logging.Console to log messages to the console, but any of the many other packages will work with the interceptor implementation.

Running the query sample calling for a list of customers will show the new warning message in the console output.

warn: MaxCountExceededInterceptor[0]
      Result count exceeded max of 100 with query SELECT "c"."Id", "c"."Name", "c"."PhoneNumber"
      FROM "Customers" AS "c"

Awesome!

Conclusion

Interceptors have an opportunity to catch issues that static analysis might miss. In general, when working with databases, you never truly get a complete picture of your system’s performance profile until you work with production datasets. You’ll also notice the interceptor uses information provided by the interceptor pipeline, keeping object allocations and resource utilization to a minimum. This approach can help save you and your team from problems early on in staging environments and even when authoring queries locally.

Please share it with friends and coworkers, and if you have a chance, be sure to follow me on Twitter at @buhakmeh for even more #dotnet content. As always, thank you for reading.