EF Core – intercepting database queries for fun and profit

Published on January 31, 2017 by Kieren

Posted in Technology

Entity Framework (EF) Core is Microsoft’s new ORM and successor to Entity Framework. It’s a rewrite from the ground up, and the feature set is currently quite different than EF 6 – take a quick look at the official comparison docs to see that it’s missing quite a few handy features.

However, it’s built with some of the great new architectural concepts that can be found across the new “Core” suite, which offer new tools for extension and injection.

As a part of one of our frameworks, we wanted to spike out a tool for monitoring and reporting on database queries executed as a part of API calls. Measuring the time taken and number of queries is fairly straightforward – indeed even the query text for those coming from user code is all available via the new .NET Core logging interfaces (although it’s a little magical to use the primarily text-based logging system for this purpose). However, we were keen to see if it was possible to automatically measure the volume of results, too – the number of rows and columns returned from queries.

With this, we can get the usual per-action stats such as # of queries, query duration, but also the total volume retrieved from the DB, or do an analysis of data read vs how much is returned from the service/API call. In this way we get a new kind of efficiency indicator – how much work did we manage to unload to the database, and how efficiently?

This is potentially useful since EF (and other ORMs) have a trade-off between power and efficiency which means it’s possible to write innocent-looking LINQ queries which download reams of data, with a load of extra data either to be processed and refined in the subsequent code, or not used at all!

For this, we needed to hook into individual DbDataReader-derived objects, the low-level components which grab results from a relational connection. In terms of ownership/creation of these objects, the classes to consider in order are IRelationalConnectionIDatabaseProviderServices (cast to IRelationalDatabaseProviderServices) → DbConnectionDbCommandDbDataReader.

Without further ado, intercepting relational database queries in EF Core. Note that these are just snippets in the interest of brevity:

1. IRelationalConnection

However your database context is set up, grab the DbContextOptionsBuilder and we’ll use the handy built-in ReplaceService<>() to inject our custom wrapper class:

// in builder
ourOptBuilder.ReplaceService<IRelationalConnection, MyRc>();

// class definition
public class MyRc : IRelationalConnection
    public MyRc(IDatabaseProviderServices services)
        var relServices = services as IRelationalDatabaseProviderServices;
        _inner = relServices.RelationalConnection;

The rest of the class should be a straight-up wrapper of the _inner object. The exception being the DbConnection property, which in this case we instantiate lazily and cache to a _dbConnection property:

public DbConnection DbConnection => _dbConnection ?? (_dbConnection = new MyDbCon(_inner.DbConnection));

With this, any time a DbConnection is requested by EF Core, an instance of our wrapping MyDbCon is used.

2. DbConnection

As above, our wrapper here delegates everything to its inner DbConnection object, with the exception of CreateDbCommand(), which will return a wrapped version:

protected override DbCommand CreateDbCommand()
    return new MyDbCmd(_inner.CreateCommand());

Again, now any time a DbCommand is requested, a wrapping MyDbCmd will be used.

3. DbCommand

Nearly there! Now we’re on to MyDbCmd, which derives DbCommand. There are a few more methods to implement to wrap the inner command here, but here we finally have some meat. There are 3 ways to execute a query, the first two don’t return too much:

public override int ExecuteNonQuery()
    // here we have _inner.CommandText, _inner.Parameters
    var res = _inner.ExecuteNonQuery();
    // we also know the result - res, which may be number of rows affected if applicable
    return res;

public override object ExecuteScalar()
    // again we have _inner.CommandText and _inner.Parameters here
    var res = _inner.ExecuteScalar();
    // ...and query result in "res"
    return res;

Finally we wrap the data reader method:

protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
    // _inner.CommandText, _inner.Parameters define the command
    var res = _inner.ExecuteReader(behavior);
    var wrap = new MyDbReader(res); // delegate to wrapper
    return wrap;

4. DbDataReader

There are lots more methods to wrap here – but we only really need to hook into Read():

public override bool Read()
    var res = _inner.Read();
    if (!res) return false;

    // stats: we have another result row!

    // re-use internal array and get values into it for analysis (ensuring there's enough space)
    if (_data == null || _data.Length < _inner.FieldCount) _data = new object[_inner.FieldCount];
    var got = GetValues(_data);

    // stats: we have another "got" columns of data
    for (int i = 0; i < got; i++)
        // stats: we have a data result, _data[i]

    return true;

That's it - a little hacky, but gets the job done and can give some valuable insight! This will incur a slight performance hit potentially, although it's unlikely to be noticeable in development/debugging scenarios. There wasn't much material out there at the time of writing and doing the research behind this, but feel free to reply on my original StackOverflow post (here) with any questions or comments.