Microsoft has published, some time ago, a set of Entity Framework providers, for adding caching and tracing capabilities to Entity Framework. One of these providers, tracing, is now available as a NuGet package. This offers an interesting functionality: the ability to see all SQL that Entity Framework is sending to the database, and its results. The provider works with “classic” Entity Framework as well as with Code First, and I am going to focus on the latter and show you how you can use it.
Open up your Code First project and add the CommunityEFProviderWrappers.EFTracingProvider NuGet package:
Now, there are a couple of ways by which you can implement this. I chose to have a static method called CreateTracingContext that returns an instance of my context configured for tracing. For this to work, we need to have in our context a constructor that takes a DbConnection, like the one in DbContext, and just calls the base implementation:
Next, we need a couple of methods that create a connection wrapper by calling our protected constructor:
And the static method for creating the tracing context:
The parameters to the CreatingTracingContext are:
The name of a registered connection string, or the connection string itself;
An action delegate that will be executed for each command sent to the database;
If the tracing provider should log the commands to the console;
The name of an optional file to which the tracing provider should write all commands.
This leverages on the ADO.NET extensibility, namely, on the possibility to have multiple connection providers. We are almost there! The only thing that’s left is the registration of the new provider. This can either be done on the XML configuration:
Or by code:
If you use the code approach, be warned, this must be called very early, that is, before any other ADO.NET call.
Now, you can start tracing:
This will call the delegate two times for each command: one with the SQL and parameters that are about to be sent to the database, and the second, with its results (or the error). You can tell which is which by looking at the Status property, get the parameters from Command.Parameters, the SQL from Command.CommandText, the time that the execution took from Duration, and the actual result from the Result property of the lambda parameter.
If the third parameter of CreateTracingContext, logToConsole, is set to true, all SQL will be output to the console, and if logToFile has something not null or empty, all of the output will be sent to a file of the same name. Of course, you can achieve all of this and more just by using the delegate.
Let me know if you find this useful!