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
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.