Simple Talk is now part of the Redgate Community hub - find out why

Using SQL Server Query Hints with Entity Framework

Entity Framework (EF) is designed to work with a variety of data sources. Although this presents many advantages, there is a downside that many of the special features of a data source such as SQL Server are off-limits. Query Hints are an example: though often misused, they are occasionally important. Dennes Torres shows how you can use these in EF, using a command interceptor that will allow you to use any query hint with SQL Server.

Query Hints should be used carefully, because they restrict the possible optimizations during the query compilation, and they may, later, force the optimizer to use poor strategies when the data changes in size or distribution: There are, however, occasions when they are required. This is a problem for anyone using Entity Framework because there isn’t a built-in way to specify query hints. In fact, Entity Framework is agnostic about the database server, because it uses providers that translate the generic queries to the native database language, so it’s not easy to include a feature such as query hints that is specific to SQL Server. However, the query-translation process is extensible. We can intercept the query-translation using a DbCommandInterceptor and thereby change the query text to include the query hint.

In this article, I will give a step-by-step account of how to build a command interceptor that will allow you to use any query hint with SQL Server. You can analyze the entire walkthrough or skip the first part and check how to use this library in the example later in the article.

You can download the source code of this solution on github using this link: https://github.com/DennesTorres/HintsInterceptor

Why are hints important?

Occasionally, query hints become very important, such as when you need to lock records in a query. Unless we find a way to use hints with Entity Framework, we are forced to use stored procedures with Entity Framework, which will increase the dependency on synchronised changes in the database when refectoring data objects in the application.

Query hints are needed in the following situations:

  • We are filtering the records by a field whose values are unevenly distributed in the table. Such type of queries will need the Option Recompile hint or they will suffer terrible performance problems.
  • We need to block the access to a record we are reading until the business transaction finishes.

Planning our Command Interceptor

Am I forgetting something?

You may think I’m forgetting the most used query hint in many applications: NOLOCK. No, I’m not forgetting it but the developers should. Most developers think the only problem NOLOCK can cause is when a transaction is rolled back, causing dirty reads.

It’s not so simple. When using NOLOCK, the query will not respect even internal SQL Server operations, such as page splits. In a page split the records are moved from one page to another. If a query using NOLOCK is reading the pages, the result is unpredictable: it can read the same record twice or not read it at all.

This makes NOLOCK way more dangerous than most developers think and it should be avoided at all costs.

The solution for the contention problem, especially in reports, is the use of Read Commit Snapshot Isolation in the database, instead of using NOLOCK.

Once it has been configured in the Entity Framework context, a command interceptor is always executed, for every query. We can use this fact to develop the interceptor to apply a query hint to every query. We then just need a way to identify which hints we would like to apply to which queries.

A graceful solution would be to including the query hint in the LINQ expression, creating some extension method we could apply to our LINQ queries. However, the command interceptor doesn’t have access to the LINQ expression: it receives only the query text that will be sent to the database.

The solution will be to create a static collection of hints that we can fill before executing a query and clear just after the execution. It needs to be static to be accessible from anywhere in our code and it also needs to be thread safe, because two concurrent threads shouldn’t affect the hints of each other.

Our solution also needs to consider that there are two types of hints: Table hints, applied to a specific table and query hints, applied to the whole query. These hints are inserted in different parts of the query: The design of our solution needs to be suitably flexible for this.

The command interceptor will need to get the string representation of each hint in the collection, but the hints must to be separated according to whether they are query hints or table hints. We can create a class hierarchy that allows us to identify each hint type by their class type.

Finally, we can’t forget that some hints are incompatible. For example, we shouldn’t use UPDLOCK and SERIALIZABLE in the same query and table. The collection needs to check the compatibility between the hints and avoid having incompatible hints.

Environment for our solutions

We will need Visual Studio and SQL Server to build our solution, but any Visual Studio version from 2012 and any SQL Server version from 2008 will work.

We will also need the Northwnd sample database that you can download here: https://northwinddatabase.codeplex.com/

Creating the solution

Let’s do a walkthrough of the process of creating our new solution and project:

    1. Inside Visual Studio, click File->New-> Project menu
    2. In the right side of the ‘New Project’ window, select Installed->Templates->Visual C#->Windows in the tree
    3. In the middle of the ‘New Project’ window, select ‘Class Library’ project template
    4. In the ‘Solution Name’ textbox, type ‘slHints’
    5. In the ‘Name’ textbox, type ‘libHintsInterceptor’ and click ‘Ok’ button

    6. In the ‘Solution Explorer’ window, right-click the ‘Class1.cs’ file and click ‘Delete’ item in the context menu

Creating the HintBase class

Each hint has custom behaviors such as the string representation and compatibility check, so each hint will need to be a custom class. However, we need a base class to specify the common behaviors for all the hints.

For now, let’s create an empty base class:

    1. Create ‘HintBase’ class in the ‘libHintsInterceptor’ project
      1. In the ‘Solution Explorer’ window, right-click ‘libHintsInterceptor’ project and click ‘Add’->’New Folder’ in the context menu
      2. Type ‘Base’ as the name of the new folder
      3. Right-click ‘Base’ folder, click ‘Add’->’Class’ in the context menu
      4. Type ‘HintBase.cs’ in the ‘Name’ textbox and click ‘Ok’ button

      5. Replace the ‘HintBase.cs’ class with the code below. I’m using an abstract class, because some methods will need to be replaced in every new hint.

Creating the HintsCollection class

The collection of hints needs to check the compatibility of a new hint with the existing ones every time a new hint is included. Because of that, we need to build a custom collection, inheriting from Collection class, and intercept the methods to insert new items in the collection.

First, let’s create ‘HintsCollection’ class:

    1. Create the ‘HintsCollection’ class
      1. In the ‘Solution Explorer’ window, right-click the ‘libHintsInterceptor’ project and click ‘Add’->’Class’
      2. Type ‘HintsCollection.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
      3. Replace the ‘HintsCollection’ class with the following code:

      1. Add the following lines to the top of the ‘HintsCollection.cs’ file, after the ‘Using’s lines

Each time a new hint is added to the class, we need to check if this hint is compatible with the existing ones. The collection will start the compatibility check, but only the hint itself can tell whether it’s compatible or not.

The ‘HintBase’ class will need a method for this, let’s create it:

  1. Add the method ‘CheckCompatibility’ in the ‘HintBase’ class using the following code:

    Now we can create a private method in ‘HintsCollection’ class to call the ‘CheckCompatibility’:

  2. Create the method ‘ValidateHint’ in the class ‘HintsCollection’:

    We need to call the ‘ValidateHint’ method every time a new hint is included in the collection. Let’s override some Collection methods for this:

  3. Create the method ‘InsertItem’ in the class ‘HintsCollection’ using the following code:

  4. Create the method ‘SetItem’ in the class ‘HintsCollection using the following code:

    At this point, the code of the ‘HintsCollection’ class is like the code below:

    The collection will need to classify the hints by their type. The type of a hint will define how it will be inserted in the query, so the types must be implemented as classes in a hierarchy, with some methods to insert the hint into the query. Let’s implement the types as derived classes from ‘HintBase’

    Let’s create the ‘TableHint’ class and ‘QueryHint’ class:

  5. Create the ‘QueryHint’ class
    1. In the ‘Solution Explorer’ window, right-click the ‘Base’ folder and click ‘Add’->’Class’ context menu
    2. Type ‘QueryHint.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
    3. Replace the ‘QueryHint’ class with the following code:

  6. Create the ‘TableHint’ class
    1. In the ‘Solution Explorer’ window, right-click the ‘Base’ folder and click ‘Add’->’Class’ context menu
    2. Type ‘TableHint.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
    3. Replace the ‘TableHint’ class with the following code:

    ‘HintBase’ needs a new method to generate the hint as a string. The string representation of most of the hints will be exactly the name of the class, so, instead of creating an abstract method, I will create a virtual method, in a way that most hint classes will not have the need to override this method.

  7. Create the ‘Hint’ virtual method in the ‘HintBase’ class. The result will be as the following code:

    Using the ‘Hint’ method and the ‘QueryHint’ and ‘TableHint’ class, we will create a method in the ‘HintsCollection’ class to transform the hints into string according to their type.

  8. Create the ‘GenerateString’ method in the ‘HintsCollection’ class using the following code:

    The ‘HintsCollection’ class will become as the following:

    Creating the Hints

    Our main task when creating the hints is create the code to check the compatibility of the hint with any other hints. Several hints will use the same code to check the compatibility. For example, Serializable, RepeatableRead and UpdLock are hints that affect the lock behavior and they shouldn’t be used together.

    We will create one more derived class to implement this compatibility check.

  9. Create the ‘LockHint’ class
    1. In the ‘Solution Explorer’ window, right-click the ‘Base’ folder and click ‘Add’->’Class’ context menu
    2. Type ‘LockHint.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
    3. Replace the ‘LockHint’ class with the following code:

    Finally, the specific hints will be easy to create. I will create five specific hints as a demonstration, the complete solutions would need classes for all possible hints in SQL Server.

    Three of these five hints will inherit from ‘LockHint’ class because they need the same compatibility check we created before. The other two will inherit from the ‘TableHint’ and ‘QueryHint’ classes.

  10. Create the ‘Serializable’ hint
    1. In the ‘Solution Explorer’ window, right-click the ‘libHintsInspector’ project and click ‘Add’->’New Folder’ in the context menu
    2. Type ‘Hints’ as the name of the new folder
    3. In the ‘Solution Explorer’ window, right-click the ‘Hints’ folder and click ‘Add’->’Class’ in the context menu
    4. Type ‘Serializable.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
    5. Replace the ‘Serializable’ class with the following code:

    6. Add the following line to the top of the ‘Serializable.cs’ file, below the existing ‘Usings’s:

  11. Create the ‘RepeatableRead’ hint
    1. In the ‘Solution Explorer’ window, right-click the ‘Hints’ folder and click ‘Add’->’Class’ in the context menu
    2. Type ‘RepeatableRead.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
    3. Replace the ‘RepeatableRead’ class with the following code:

    4. Add the following line to the top of the ‘RepeatableRead.cs’ file, below the existing ‘Usings’s:

  12. Create the ‘UpdLock’ hint
    1. In the ‘Solution Explorer’ window, right-click the ‘Hints’ folder and click ‘Add’->’Class’ in the context menu
    2. Type ‘UpdLock.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
    3. Replace the ‘Serializable’ class with the following code:

    4. Add the following line to the top of the ‘UpdLock.cs’ file, below the existing ‘Usings’s:

  13. Create the ‘Recompile’ hint
    1. In the ‘Solution Explorer’ window, right-click the ‘Hints’ folder and click ‘Add’->’Class’ in the context menu
    2. Type ‘Recompile.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
    3. Replace the ‘Recompile’ class with the following code:

    4. Add the following line to the top of the ‘Recompile.cs’ file, below the existing ‘Usings’s:

  14. Create the ‘ReadPast’ hint
    1. In the ‘Solution Explorer’ window, right-click the ‘Hints’ folder and click ‘Add’->’Class’ in the context menu
    2. Type ‘ReadPast.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
    3. Replace the ‘ReadPast’ class with the code below. ReadPast is incompatible with Serializable

    4. Add the following line to the top of the ‘ReadPast.cs’ file, below the existing ‘Usings’s:

    The process of applying a hint

    The ‘HintsInterceptor’ class will receive the query text and has the task of processing all the hints inside the collection and inserting them in the query text. We need to understand in more detail how this class, ‘HintsInterceptor’ will generate the query text with the hints so we can create the correct methods needed for this task.

    Since we have two types of hints, query hints and table hints, the ‘HintsInterceptor’ will have to separate the two types of hints in the collection before we process them.

    Each hint has the method ‘Hint’ to generate its string representation. The ‘HintsInterceptor’ will need to call this method for each hint, concatenating the results in two different strings, one for the query hints and the other for the table hints, the two types of hints.

    Finally, the ‘HintsInterceptor’ needs to insert each resulting string in the query, but doesn’t know how to do it. Each hint type, query hint and table hint, knows how to insert the hints in the query, the ‘HintsInterceptor’ class only needs to call the correct method for this.

    This last method, which insert the hints in the query, will operate over the string generated from the hints collection, not over a specific hint. Due to that, the method doesn’t need to be an instance method of each hint, it can be a static method.

    Let’s start with the ‘QueryHint’ class, the easiest, because the query hints are added to the end of the query:

  15. Create the ‘BuildQuery’ method in the ‘QueryHint’ class. The code of the ‘QueryHint’ class is the following:

    The ‘TableHint’ class is more difficult, because the hints need to be just after the table name, in the middle of the query. We can solve this by using regular expressions.

  16. Create the ‘BuildQuery’ method in the ‘TableHint’ class. This method is responsible to insert the hints after the table name in the FROM clause in the Select statement. We will use a regular expression to find the correct position for the hints.
    1. In the ‘TableHint’ class, insert the code for the regular expression:

    2. At the top of ‘TableHint.cs’ file, below the ‘Using’s, add the following line:

    3. In the ‘TableHint’ class, insert the code for the ‘BuildQuery’ method as the following:

    After these changes, the ‘TableHint’ class will be the following:

    Creating the HintsInterceptor

    The ‘HintsInterceptor’ needs to inherit from the DbCommandInterceptor class, which is part of Entity Framework Nuget package. Because of that, we need to install the Entity Framework Nuget package before we start

  17. Install Entity Framework Nuget package in the ‘libHintsInterceptor’ project
    1. Right-Click the ‘libHintsInterceptor’ project inside the ‘Solution Explorer’ window and click ‘Manage Nuget Package’ in the context menu’
    2. Click ‘Browse’ item in the top menu
    3. Type ‘Entity Framework’ in ‘Search’ textbox and press ‘Enter’
    4. Select ‘Entity Framework’ result in the left panel
    5. Click ‘Install’ button in the right panel

    6. Click ‘Ok’ button in the ‘Preview’ window
    7. Click ‘I Accept’ button in the ‘License Acceptance’ window
  18. Create the ‘HintsInterceptor’ class
    1. In the ‘Solution Explorer’ window, right-click the ‘libHintsInterceptor’ project and click ‘Add’->’Class’ in the context menu
    2. Type ‘HintsInterceptor.cs’ in the ‘Name’ textbox and click the ‘Ok’ button
    3. Replace the ‘HintsInterceptor’ class with the following code:

    4. In the top of ‘HintsInterceptor.cs’ file, add the following line below the ‘Using’s:

  19. Create the ‘HintsCollection’ property in the ‘HintsInterceptor’ class. You may notice in the code below I used the ThreadStatic attribute to ensure the concurrency of the application: one thread will not affect another.

  20. Create the ‘BuildQuery’ method using the following code:

  21. Add the following code in the top of the ‘HintsInterceptor.cs’ file, after all the ‘Using’s:

  22. Override the ‘ScalarExecuting’ method using the following code:

  23. Override the ‘ReaderExecuting’ method using the following code:

  24. Create an ‘Add’ method in the ‘HintsInterceptor’ class using the following code:

  25. Create a ‘Clear’ method in the ‘HintsInterceptor’ class using the following code:

    After these steps, the code of the ‘HintsInterceptor’ class will be this:

    You may notice some details about this code:

    • It was necessary to override two different methods: ScalarExecuting and ReaderExecuting. These methods are required to process the query for two different execution methods, one returning a single value and the other returning a resultset.
    • Although we override two methods, the query processing is the same, due to the fact that it’s done in a private method
    • I created two more methods in the ‘HintsInterceptor’ class, ‘Add’ and ‘Clear’, to make the control of the collection of hints easier

    Testing the solution

    After building the ‘LibInterceptor’ project, we can now test this solution to check if everything is working. We need two additional projects in our solution: one for our Entity Framework model and another one to be the client, where we will use the interceptor.

    The client project can be a simple Console Application. We only need a few lines of code to test the behavior of our interceptor.

  26. Create the ‘libDados’ project
    1. In the ‘Solution Explorer’ window, right-click the ‘slHints’ solution and click ‘Add’->’New Project’ in the context menu
    2. In the right side of the ‘New Project’ window, select ‘Installed’->’Templates’->’Visual C#’->Windows in the tree
    3. In the middle of the ‘New Project’ window, select ‘Class Library’ project template
    4. In the ‘Name’ textbox, type ‘libDados’ and click ‘Ok’ button
    5. In the ‘Solution Explorer’ window, right-click the ‘Class1.cs’ file, below the ‘libDados’ project, and click ‘Delete’ in the context menu
  27. Create a new Entity Framework context
    1. In the ‘Solution Explorer’ window, right-click ‘libDados’ project and click ‘Add’->’New Item’ in the context menu
    2. In the ‘Add New Item’ window, select ‘ADO.NET Entity Data Model’
    3. In the ‘Name’ textbox, type ‘North’ and click ‘Add’ button
    4. In the ‘Entity Data Model Wizard’ window, select ‘EF designer from database’ and click ‘Next’ button

    5. In the ‘Choose your data connection’ step, build a connection to your ‘Northwnd’ database in your SQL Server
    6. Click the ‘Yes, include the sensitive data in the connection string’ radio button
    7. In the ‘Save connection settings in App.Config as’ textbox, type ‘NorthEntities’ and click ‘Next’ button

    8. In the ‘Choose your version’ step, select ‘Entity Framework 6.x’ radio button and click ‘Next’ button
    9. In the ‘Choose your database objects and settings’ step choose ‘Customers’ and ‘Orders’ table

    10. In the ‘Model Namespace’ textbox, type ‘NorthModel’ and click ‘Finish’ button

  28. Create the ‘winClient’ project
    1. In the ‘Solution Explorer’ window, right-click the ‘slHints’ solution and click ‘Add’->’New Project’ in the context menu
    2. In the right side of the ‘New Project’ window, select ‘Installed’->’Templates’->’Visual C#’->’Windows’ in the tree
    3. In the middle of the ‘New Project’ window, select ‘Console Application’ project template

    4. In the ‘Name’ textbox, type ‘winClient’ and click ‘Ok’ button
  29. In the ‘winClient’ project, add reference to the ‘libDados’ and ‘libHintsInterceptor’ project
    1. In the ‘Solution Explorer’ window, right-click the ‘winClient’ project and click ‘Add’->’Reference’ in the context menu
    2. In the left side of the ‘Reference Manager’ window, select ‘Projects’->’Solution’
    3. In the ‘Reference Manager’ window, click ‘libDados’ and ‘libHintsInterceptor’ checkboxes

    4. Click ‘Ok’ button
  30. Install Entity Framework nuget package in the ‘winClient’ project
    1. Right-Click the ‘winClient’ project inside the ‘Solution Explorer’ window and click ‘Manage Nuget Package’ in the context menu’
    2. Click ‘Browse’ item in the top menu
    3. Type ‘Entity Framework’ in ‘Search’ textbox and press ‘Enter’
    4. Select ‘Entity Framework’ result in the left panel
    5. Click ‘Install’ button in the right panel
    6. Click ‘Ok’ button in the ‘Preview’ window
    7. Click ‘I Accept’ button in the ‘License Acceptance’ window
  31. In the ‘Program.cs’ file, inside ‘winClient’ project, add the following lines to the top of the file, below the ‘Using’s:

  32. In the ‘Program.cs’ file, inside ‘winClient’ project, add the following code, replacing the existing ‘Main’:

    The query will be executed twice in this example: the first time without any hint and the 2nd time with query hints and table hints.

  33. Copy the ‘ConnectionStrings’ section from ‘App.Config’ file inside ‘libDados’ project to the ‘App.Config’ file inside ‘winClient’ project
  34. In the ‘App.Config’ file, inside ‘winClient’ project, add the following inside the ‘EntityFramework’ section to configure the hints interceptor:

    Executing the Test

    Using SQL Server Profiler we can capture the queries sent to SQL Server and examine the query hints. Let’s do it:

  35. Start a new trace in SQL Server Profiler
    1. Open SSMS (SQL Server Management Studio) and connect to your SQL Server
    2. Click in ‘Tools’->’SQL Server Profiler’ in the menu
    3. In SQL Server Profiler, complete the connection to your SQL Server
    4. In the ‘Trace Properties’ window, click on the ‘Events Selection’ page
    5. Click the ‘Show all Columns’ checkbox
    6. Locate the ‘DatabaseName’ column and check all checkboxes in this column
    7. Click the ‘Show all Columns’ checkbox again, unchecking the checkbox.
    8. Click the ‘Column Filters’ button
    9. In the ‘Edit Filter’ window, select ‘DatabaseName’ in the left side
    10. Inside the tree in the left side of ‘Edit Filter’ window, below the ‘Like’ node, type ‘Northwnd’
    11. Click the ‘Ok’ button
    12. Click the ‘Run’ button
  36. In Visual Studio, in the ‘Solution Explorer’ window, right-click ‘winClient’ project and click ‘Set as Startup Project’ in the context menu
  37. Execute the application clicking the ‘Start’ button in the toolbar
  38. In SQL Server Profiler, pause the capture clicking the ‘Pause’ button in the toolbar

    If everything goes well, the execution in step 45 will only open a command window and quickly closes it. In the SQL Profiler, two statements will be captured, we will notice two sets of SQL:BatchStarting/SQL:BatchCompleted and the second statement will have the hints, as in the images below:

    Improving the Hints

    As you may have noticed, the table hints were inserted for both tables in the query, ‘Customers’ and ‘Orders’. What if we would like the hint only in a single table, not in all tables?

    We need to do some changes in our code to achieve this. The following changes will be needed:

  39. Create a new property in the ‘TableHint’ class to store the table name

  40. Add a new sample queries in the project ‘WinClient’, file ‘program.cs’. These examples include two different situations: in the first one, there are only table hints for specific tables. In the second one there are two types of table hints, for specific table and for every table.

  41. In the ‘TableHint’ class, create a new overload for the ‘BuildQuery’ method that accepts the table name. This is a challenge for the regular expressions, because we can have some hints for all tables and some hints for specific tables in the same query. This means that sometimes the inclusion of a hint will find the table already with some hints, other times without any hint at all. The code needs to support both situations.

  42. Change the ‘BuildQuery’ method in the ‘HintsInterceptor’ class to process the table hints considering the existence of hints for specific tables. In this code below there are calls for two private methods, ‘GetTableHints’ and ‘BuildTableQuery’, used to process the table hints.

  43. Create the ‘GetTableHints’ method in the ‘HintsInterceptor’ class. This method needs to process the hints collection, creating a dictionary with the hints string for each table name, including a list of hints common for all the tables.

  44. Create the ‘BuilTableQuery’ method in the ‘HintsInterceptor’ class. This method will process the dictionary previously created and call the correct ‘BuildQuery’ overloads from the ‘TableHint’ class.

Executing the Test again

You need to start the trace again, in the same way that you did in step 43 and then again execute the application.

This time the application executes four statements. This image illustrates the result expected in the SQL Server Profiler:

We will find the following result in these statements:

1st: This query isn’t using any hint

2nd: This query is using two table hints for all tables plus a query hint

3rd: This query is using different table hints for each table, plus a query hint

4th: This query is using one table hint for both tables, another only for Customers table plus a query hint

Conclusion

This solution provides the means of using both query and table hints with Entity Framework when you are using the SQL Server provider, thereby avoiding the need to work around some problems for which hints are the obvious solution.

There is still room for improvement in this solution, especially to support all the other hints that are available in SQL Server, however some of the main hints you are likely to need are already in the solution and you can easily implement more if you need them.

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue