EF tags and interceptors

Daily Knowledge Drop

Tags, in conjunction with an IInterceptor implementation, can be used to intercept SQL commands before being executed on the database, and modify them to fix the tag requirements.

Setup

In the examples below, we are using DbContext setup the standard way, with a Blog DbSet.

As per normal, to get a list of all Blogs:

var normalQuery = context.Blogs.ToList();

Next we'll look at how to tag the queries with a nolock tag, and then how to intercept the query, look presence of the tag, and modify the query to include nolock.

Tagging queries

The first step is to tag the query with the nolock tag - this is relatively straightforward:

var nolockQuery = context
    .Blogs
    .TagWith("Nolock")
    .ToList();

If the generated SQL is inspected, one will see the query is now tagged as follows:

-- Nolock

SELECT [b].[Id], [b].[DateCreated], [b].[Description], [b].[Title]
FROM [Blog] AS [b]

Step 1 completed!

Interceptor

The next step is to intercept the query before it's executed on the database and modify it - luckily this is also fairly straightforward, as Entity Framework has a built in mechanism for this.

To create an interceptor (in this example) all that is required is to inherit from DbCommandInterceptor:

// inherit DbCommandInterceptor
public class NoLockInterceptor : DbCommandInterceptor
{
    // overwrite the ReaderExecuting method
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result)
    {
        // if the query generated by EF starts with "nolock"
        if(command.CommandText.StartsWith("-- Nolock", StringComparison.Ordinal))
        {
            // append "nolock" to the end
            command.CommandText += " (NOLOCK)";
        }

        return result;
    }
}

The ReaderExecuting method is executed before the query is about to be executed - in this method, the query is (crudely) checked to see if it has been tagged for nolock, and if so, the NOLOCK keyword is added to the end of the query.

This is very crude and only for demo purposes - it is not production ready. Ror example, this does not take into account any WHERE statements in the SQL.

DbContext

Now that we have the interceptor defined, the next step is to register it with EF.

In this demo, this is done in the OnConfiguring method of the DbContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(
        @"Server=.\SQLEXPRESS;Database=EFTagging;Integrated Security=True;TrustServerCertificate=True");

    // add the interceptor
    optionsBuilder.AddInterceptors(new NoLockInterceptor());
}

Configuration is now completed!

Generated SQL

When querying (without an predicate in this sample) and tagging the query, the nolock keyword is automatically added:

var context = new DemoContext();

// query without tagging
var normalQuery = context.Blogs.ToList();

//query with the tag
var nolockQuery = context.Blogs.TagWith("Nolock").ToList();

The SQL generated by the two queries.

Without the tag:

SELECT [b].[Id], [b].[DateCreated], [b].[Description], [b].[Title]
FROM [Blog] AS [b]

With the tag:

-- Nolock

SELECT [b].[Id], [b].[DateCreated], [b].[Description], [b].[Title]
FROM [Blog] AS [b] (NOLOCK)

Notes

A useful and interesting way to combine two EF features - the tagging and interception features. Manually modifying SQL generated by EF can be dangerous as there are a lot of options and variations to the SQL generated - so do it with caution and test extensively.

References

Dave Callan Tweet

Daily Drop 221: 13-12-2022

At the start of 2022 I set myself the goal of learning one new coding related piece of knowledge a day.
It could be anything - some.NET / C# functionality I wasn't aware of, a design practice, a cool new coding technique, or just something I find interesting. It could be something I knew at one point but had forgotten, or something completely new, which I may or may never actually use.

The Daily Drop is a record of these pieces of knowledge - writing about and summarizing them helps re-enforce the information for myself, as well as potentially helps others learn something new as well.