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

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. 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. 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.
         1 2 3

         public abstract class HintBase       {       }

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. 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. 1. 1. Add the following lines to the top of the ‘HintsCollection.cs’ file, after the ‘Using’s lines
         1 2	using System.Collections.ObjectModel;   using libHintsInterceptor.Base;

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:
   1 2 3 4

   public abstract class HintBase       {           public abstract bool CheckCompatibility(HintsCollection hints);       }

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

2. Create the method ‘ValidateHint’ in the class ‘HintsCollection’:
   1 2 3 4 5 6 7 8 9 10 11 12 13

   public class HintsCollection : Collection<HintBase>       {           /// <summary>           /// Method to validate a new hint           /// against the existing ones           /// </summary>           /// <param name="newhint"></param>           /// <returns></returns>           private bool ValidateHint(HintBase newhint)           {               return newhint.CheckCompatibility(this);           }       }

   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:
   1 2 3 4 5 6 7 8 9 10 11 12

   /// <summary>           /// Intercepts the insert to call the validation           /// </summary>           /// <param name="index"></param>           /// <param name="item"></param>           protected override void InsertItem(int index, HintBase item)           {               if (ValidateHint(item))                   base.InsertItem(index, item);               else                   throw new ApplicationException("hint not compatible");           }

4. Create the method ‘SetItem’ in the class ‘HintsCollection using the following code:
   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

   /// <summary>           /// Intercepts the SetItem to call the validation           /// </summary>           /// <param name="index"></param>           /// <param name="item"></param>           protected override void SetItem(int index, HintBase item)           {               HintBase itemOld = this[index];               this.RemoveAt(index);               if (ValidateHint(item))                   base.InsertItem(index, item);               else               {                   base.InsertItem(index, itemOld);                   throw new ApplicationException("hint not compatible");               }           }

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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

   public class HintsCollection : Collection<HintBase>       {           /// <summary>           /// Method to validate a new hint           /// against the existing ones           /// </summary>           /// <param name="newhint"></param>           /// <returns></returns>           private bool ValidateHint(HintBase newhint)           {               return newhint.CheckCompatibility(this);           }           /// <summary>           /// Intercepts the insert to call the validation           /// </summary>           /// <param name="index"></param>           /// <param name="item"></param>           protected override void InsertItem(int index, HintBase item)           {               if (ValidateHint(item))                   base.InsertItem(index, item);               else                   throw new ApplicationException("hint not compatible");           }           /// <summary>           /// Intercepts the SetItem to call the validation           /// </summary>           /// <param name="index"></param>           /// <param name="item"></param>           protected override void SetItem(int index, HintBase item)           {               HintBase itemOld = this[index];               this.RemoveAt(index);               if (ValidateHint(item))                   base.InsertItem(index, item);               else               {                   base.InsertItem(index, itemOld);                   throw new ApplicationException("hint not compatible");               }           }       }

   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:
      1 2 3

      public abstract class QueryHint : HintBase       {       }

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:
      1 2 3

      public abstract class TableHint : HintBase       {       }

   ‘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:
   1 2 3 4 5 6 7 8 9 10 11 12 13

   public abstract class HintBase       {           public abstract bool CheckCompatibility(HintsCollection hints);           /// <summary>           /// Returns the hint as a string           /// that will be included in the query           /// </summary>           /// <returns></returns>           public virtual string Hint()           {               return this.GetType().Name;           }       }

   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:
   1 2 3 4 5 6 7 8 9 10 11 12

   /// <summary>           /// Generate a string with all the hints of a type in the collection           /// This string will be used in the query           /// </summary>           /// <returns></returns>           public string GenerateString<T>() where T:HintBase           {               var res = (from x in this.OfType<T>()                          select x.Hint()).ToList();               var sList = string.Join(",", res);               return sList;           }

   The ‘HintsCollection’ class will become as the following:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

   public class HintsCollection : Collection<HintBase>       {           /// <summary>           /// Method to validate a new hint           /// against the existing ones           /// </summary>           /// <param name="newhint"></param>           /// <returns></returns>           private bool ValidateHint(HintBase newhint)           {               return newhint.CheckCompatibility(this);           }           /// <summary>           /// Intercepts the insert to call the validation           /// </summary>           /// <param name="index"></param>           /// <param name="item"></param>           protected override void InsertItem(int index, HintBase item)           {               if (ValidateHint(item))                   base.InsertItem(index, item);               else                   throw new ApplicationException("hint not compatible ");           }           /// <summary>           /// Intercepts the SetItem to call the validation           /// </summary>           /// <param name="index"></param>           /// <param name="item"></param>           protected override void SetItem(int index, HintBase item)           {               HintBase itemOld = this[index];               this.RemoveAt(index);               if (ValidateHint(item))                   base.InsertItem(index, item);               else               {                   base.InsertItem(index, itemOld);                   throw new ApplicationException("hint not compatible ");               }           }           /// <summary>           /// Generate a string with all the hints of a type in the collection           /// This string will be used in the query           /// </summary>           /// <returns></returns>           public string GenerateString<T>() where T:HintBase           {               var res = (from x in this.OfType<T>()                          select x.Hint()).ToList();               var sList = string.Join(",", res);               return sList;           }       }

   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:
      1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

      public class LockHint : TableHint       {           public override bool CheckCompatibility(HintsCollection hints)           {               if ((from x in hints.OfType<LockHint>()                    select x).Any())               {                   return false;               }               else               {                   return true;               }           }       }

   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:
       1 2 3

       public class Serializable : LockHint       {       }

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

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:
       1 2 3

       public class RepeatableRead : LockHint       {       }

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

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:
       1 2 3

       public class UpdLock : LockHint       {       }

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

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:
       1 2 3 4 5 6 7

       public class Recompile : QueryHint       {           public override bool CheckCompatibility(HintsCollection hints)           {               return true;           }       }

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

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
       1 2 3 4 5 6 7 8

       public class ReadPast : TableHint       {           public override bool CheckCompatibility(HintsCollection hints)           {               return !(from x in hints.OfType<Serializable>()                       select x).Any();           }   }

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

    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:
    1 2 3 4 5 6 7

    public abstract class QueryHint : HintBase       {           public static string BuildQuery(string Query, string hints)           {               return Query + " OPTION (" + hints + ")";           }       }

    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:
       1 2 3

       private static readonly Regex TableHRegex =               new Regex(@"(?<table>AS \[Extent\d+\])",                   RegexOptions.Multiline | RegexOptions.IgnoreCase);

    2. At the top of ‘TableHint.cs’ file, below the ‘Using’s, add the following line:
       1	using System.Text.RegularExpressions;

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

       public static string BuildQuery(string Query, string hints)           {               return TableHRegex.Replace(Query,   string.Format("${{table}} WITH ({0})", hints));           }

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

    1 2 3 4 5 6 7 8 9 10 11

    public abstract class TableHint : HintBase       {           private static readonly Regex TableHRegex =               new Regex(@"(?<table>AS \[Extent\d+\])",                   RegexOptions.Multiline | RegexOptions.IgnoreCase);           public static string BuildQuery(string Query, string hints)           {               return TableHRegex.Replace(Query,   string.Format("${{table}} WITH ({0})", hints));           }       }

    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:
       1 2 3

       public class HintsInterceptor : DbCommandInterceptor       {       }

    4. In the top of ‘HintsInterceptor.cs’ file, add the following line below the ‘Using’s:
       1	using System.Data.Entity.Infrastructure.Interception;

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.
    1 2 3 4 5 6 7 8 9

    [ThreadStatic]           private static HintsCollection _hints = new HintsCollection();           public static HintsCollection Hints           {               get               {                   return _hints;               }           }

20. Create the ‘BuildQuery’ method using the following code:
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

    private string BuildQuery(string Query)           {               if (Hints.Count != 0)               {                   string sTHints = "";                   string sQHints = "";                   sTHints = Hints.GenerateString<TableHint>();                   sQHints = Hints.GenerateString<QueryHint>();                   if (sQHints != string.Empty)                   {                       Query = QueryHint.BuildQuery(Query, sQHints);                   }                   if (sTHints != string.Empty)                   {                       Query = TableHint.BuildQuery(Query, sTHints);                   }               }               return Query;           }

21. Add the following code in the top of the ‘HintsInterceptor.cs’ file, after all the ‘Using’s:
    1 2	using libHintsInterceptor.Base;   using System.Data.Common;

22. Override the ‘ScalarExecuting’ method using the following code:
    1 2 3 4 5 6

    public override void ScalarExecuting(DbCommand command,                           DbCommandInterceptionContext<object>   interceptionContext)           {               command.CommandText = BuildQuery(command.CommandText);           }

23. Override the ‘ReaderExecuting’ method using the following code:
    1 2 3 4 5

    public override void ReaderExecuting(DbCommand command,               DbCommandInterceptionContext<DbDataReader> interceptionContext)           {               command.CommandText = BuildQuery(command.CommandText);           }

24. Create an ‘Add’ method in the ‘HintsInterceptor’ class using the following code:
    1 2 3 4 5 6

    #region Collection           public static void Add(HintBase hint)           {               Hints.Add(hint);           }           #endregion

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

    public static void Clear()           {               Hints.Clear();           }

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

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

    public class HintsInterceptor : DbCommandInterceptor       {           private static readonly Regex nolockRegex =               new Regex(@"(?<table>AS \[Extent\d+\])",                   RegexOptions.Multiline | RegexOptions.IgnoreCase);           [ThreadStatic]           private static Hints.HintsCollection _hints = new Hints.HintsCollection();           public static Hints.HintsCollection Hints           {               get               {                   return _hints;               }           }           public override void ScalarExecuting(DbCommand command,                           DbCommandInterceptionContext<object> interceptionContext)           {               command.CommandText=BuildQuery(command.CommandText);           }           public override void ReaderExecuting(DbCommand command,               DbCommandInterceptionContext<DbDataReader> interceptionContext)           {               command.CommandText = BuildQuery(command.CommandText);           }           #region colecao           public static void Add(HintBase hint)           {               Hints.Add(hint);           }           public static void Clear()           {               Hints.Clear();           }           #endregion       }

    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:
    1 2 3

    using libHintsInterceptor;   using libHintsInterceptor.Hints;   using System.Data.Entity;

32. In the ‘Program.cs’ file, inside ‘winClient’ project, add the following code, replacing the existing ‘Main’:
    1 2 3 4 5 6 7 8 9 10 11 12

    static void Main(string[] args)       {           libDados.NORTHEntities ct = new libDados.NORTHEntities();           var res = (from x in ct.Customers.Include(x => x.Orders)                      select x);           var res2 = res.ToList();           HintsInterceptor.Add(new ReadPast());           HintsInterceptor.Add(new RepeatableRead());           HintsInterceptor.Add(new Recompile());           var res3 = res.ToList();           HintsInterceptor.Clear();       }

    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:
    1 2 3 4

    <interceptors>         <interceptor type="libHintsInterceptor.HintsInterceptor, libHintsInterceptor">         </interceptor>       </interceptors>

    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
    1 2 3 4

    public string TableName           {               get; set;           }

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.
    1 2 3 4 5 6 7 8 9 10

    HintsInterceptor.Add(new ReadPast() { TableName = "Orders" });               HintsInterceptor.Add(new RepeatableRead() { TableName = "Customers" });               HintsInterceptor.Add(new Recompile());               var res4 = res.ToList();               HintsInterceptor.Clear();               HintsInterceptor.Add(new ReadPast());               HintsInterceptor.Add(new RepeatableRead() { TableName = "Customers" });               HintsInterceptor.Add(new Recompile());               var res5 = res.ToList();               HintsInterceptor.Clear();

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.
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

    public static string BuildQuery(string Query, string hints, string TableName)           {               string exp = @"(?<all>\[" + TableName +                           @"\] AS \[Extent\d+\] WITH \()(?<hints>[\w,]*)\)";               Regex NameTableRegEx = new Regex(exp,                   RegexOptions.Multiline | RegexOptions.IgnoreCase);               var mat = NameTableRegEx.Matches(Query);               if (mat.Count == 0)               {                   string exp2 = @"(?<all>\[" + TableName + @"\] AS \[Extent\d+\])";                   Regex TableClean = new Regex(exp2, RegexOptions.Multiline |                                       RegexOptions.IgnoreCase);                   return TableClean.Replace(Query,                               string.Format("${{all}} WITH ({0})", hints));               }               else               {                   var sHints = mat[0].Groups[2].Value;                   hints = sHints + "," + hints;                   return NameTableRegEx.Replace(Query,                           string.Format("${{all}} {0})", hints));               }           }

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.
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

    private string BuildQuery(string Query)           {               if (Hints.Count != 0)               {                   string sQHints = "";                   sQHints = Hints.GenerateString<QueryHint>();                   var tHints = GetTableHints();                   if (sQHints != string.Empty)                   {                       Query = QueryHint.BuildQuery(Query, sQHints);                   }                   if (tHints.Count > 0)                   {                       Query = BuildTableQuery(tHints, Query);                   }               }               return Query;           }

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.
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

    private Dictionary<string, string> GetTableHints()           {               var res = (from x in Hints.OfType<TableHint>()                          group x by x.TableName into g                          select new { TableName = g.Key, Hints = g.ToList() });               Dictionary<string, string> tHints = new Dictionary<string, string>();               res.ToList().ForEach(x =>               {                   string hints = "";                   x.Hints.ForEach(y => hints += (hints == string.Empty ? "" : ", ")                                       + y.Hint());                   var key = String.IsNullOrEmpty(x.TableName) ?   "Empty" : x.TableName;                   tHints.Add(key, hints);               });               return tHints;           }

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.
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

    private string BuildTableQuery(Dictionary<string, string> tHints,                                           string Query)           {               if (tHints.ContainsKey("Empty"))               {                   Query = TableHint.BuildQuery(Query, tHints["Empty"]);               }               foreach (var item in tHints)               {                   if (item.Key != "Empty")                   {                       Query = TableHint.BuildQuery(Query, item.Value, item.Key);                   }               }               return Query;           }

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:

1^st: This query isn’t using any hint

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

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

4^th: 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.