Obviously, we should be running such a query using the ReadUncommitted transaction isolation level. However, in certain cases that is really not an option. In such a situation we need to add the NOLOCK hint explicitly to the generated query. This post is about adding the NOLOCK hint to any query generated by EntityFramework.
How Do They Do It!
The solution that I found was pretty elegant and nicely abstracted in one class. The solution described in this post is taken from this SO post. Here are the steps involved in getting the job done.
- Create subclass of DbCommandInterceptor
- Override two methods in this derived class ScalarExecuting and ReaderExecuting
- These methods will be called by EntityFramework just before executing the generated query.
- In these methods we get an opportunity to update the entity framework generated query and add the NOLOCK hint if required.
Heres the code that will get the job done.
Basically all we are doing here is, looking for a pattern which matches table names in the entity framework generated query and replacing all the table names with <TableName> WITH (NOLOCK) hint. Thats all that is needed really!
The thread static variable ApplyNoLock is kind of a switch that, controls whether the NOLOCK hint is to be applied or not.
The Interceptor can be added during the application start as follows
Thats all folks, its really a simple and elegant way to add the NOLOCK hint to any query generated by EntityFramework!
