Sending Correlation ID from Code to SQL Server

Question

Is there a way to send a correlation ID from C# code to SQL Server at the command level? For instance, using x-correlation-id is an accepted way to track a request down to all parts of the system. we are looking for a way to pass this string value to stored procedure calls in SQL Server. I spent sometime reading thru documents and posts but I was not able to find anything useful. Can someone please let me know if there is a way to do this? The goal is to be able to track a specific call thru all services (which we can now) and DB calls (which we cannot and looking for a solution.)

Answer 1

I know the answer here is one year later. But in case, somebody has the same question.

Since EF core 2.2, MS provides a new method called "TagWith()" which you could pass your own annotation with the EF query into SQL server. In this way, you could easily track the SQL query with the same correlation id generated in your C# code. https://learn.microsoft.com/en-us/ef/core/querying/tags

Unfortunately, this new feature is not available in EF 6. But it is not only us in this situation. If you just need a simple solution, you could check the thread here and MS documents.

If you need a more stable solution, you could check this NuGet plugin for EF 6 as well.

Answer 2

To pass your correlation id to SQL Server you have two options:

• explicitly pass it as a parameter to your queries & stored procedures.
  • This is annoying as it requires work to change all your db calls to have a parameter like @correlationId, and often doesn't make sense having that parameter for simple data-retrieval queries. Perhaps you decide to only pass it for data-modification operations.
  • But on the positive side it's really obvious where the correlation info comes from (i.e. nobody reading the code will be confused) and doesn't require any additional db calls.
  • If all your data-modification is done using stored procs I think this is a good way to go.
• use SQL Server's SESSION_CONTEXT(), which is a way you can set session state on a connection that can be retrieved from within stored procs etc.
  • You can find a way to inject it into your db layer (e.g. this) so the session context is always set on a connection before executing your real db calls. Then within your procs/queries you get the correlation id from the SESSION_CONTEXT and write to wherever you want to store it (e.g. some log table or as a column on tables being modified)
  • This can be good as you don't need to change each of your queries or procs to have the @correlationId parameter.
  • But it's often not so transparent how the session context is magically set. Also you need to be sure it's always set correctly which can be difficult with ORMs and connection pooling and other architectural complexities.
  • If you're not already using stored procs for all data modification, and you can get this working with your db access layer, and you don't mind the cost of the extra db calls this is a good option.

I wish this was easier.

Another option is to not pass it to SQL Server, but instead log all your SQL calls from the tier that makes the call and include the correlation id in those logs. That's how Application Insights & .NET seems to do it by default: logging SQL calls as a dependency along with the SQL statement and the correlation id.