Joshua Chini

Tags: EFCore, ASPNETCore, Prefix, DBMonitor

In my last article I talked about getting started with Entity Framework Core. In this article I want to cover the diagnostics aspect of Entity Framework Core. We will look at enabling logging in our application and how to use a profiler to get more in-depth information about Entity Framework.

Logging

Logging enables you to see a lot of what Entity Framework is doing behind the scenes, but most importantly it displays the SQL being executed against the database. My logging framework of choice is Serilog, but there are a lot of great logging frameworks that support the ILoggerProvider implementation.

To add Serilog to our previous sample project we need to add the Nuget packages. In Visual Studio, open the Package Manager Console and type:

Install-Package Microsoft.Extensions.Logging
Install-Package Serilog
Install-Package Serilog.Extensions.Logging
Install-Package Serilog.Sinks.Literate

Add the following using statements to Program.cs

using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Serilog;

Add the following code to Program.cs at the top of the Main method

var log = new LoggerConfiguration()
                .MinimumLevel.Information()
                .WriteTo.LiterateConsole()
                .CreateLogger();

Now we need to create an instance of our context and register our logger. Add this code directly below the code we just added in the Main method.

Note: You only need to register the logger with a single context instance. Once you have registered it, it will be used for all other instances of the context in the same AppDomain.

using (var db = new PersonContext())
            {
                var serviceProvider = db.GetInfrastructure<IServiceProvider>();
                var loggerFactory = serviceProvider.GetService<ILoggerFactory>();
                loggerFactory.AddSerilog(log);
            }

Now when we run our application we should see the SQL being executed written to the console. We could increase the information being logged by changing the loglevel to MinimumLevel.Debug() or MinimumLevel.Verbose().

Profilers

Now that we have logging working in our application lets discuss profilers. There are a lot of great profiling tools in the .NET ecosystem here are just a few I am familar with ANTS Performance Profiler, dotTrace, MiniProfiler, and Prefix. I have heard lots of great things about ANTS, but have only played with the trial version in the past. We have Resharper Ultimate licenses at work which includes the dotTrace profiler. I downloaded a copy of dotTrace and got it to attach to my application, but each time I tried to get it to attach in the SQL profiler mode it would crash. I did some searching through their documentation and forums, but didn’t find a solution so I moved to the next tool. Next on my list was MiniProfiler which is a tool provided by the great folks at Stack Overflow. Unfortunately Core support is still an open issue on their backlog, but it does appear that it is being actively worked on. Next on my list was Prefix which comes from Stackify. I had heard about the tool from the guys at .NET Rocks and a post on Scott Hanselman’s blog, but haven’t had the opportunity to try it out until now. I downloaded Prefix and started up the application. I then started reading the documentation on getting started. I quickly realized that getting started with Prefix and an ASP.NET Core application was a one liner in the Startup.cs file versus having to add code around each method I wanted to trace in a .NET Core console application. So I quickly created a new ASP.NET Core MVC application and copied over the code we had in our console application. I added the StackifyMiddleware nuget package and added this line of code to the Configure() method in Startup.cs. It must be added before you add MVC via the app.UseMvc() method.

app.UseMiddleware<StackifyMiddleware.RequestTracerMiddleware>();

This worked great for the MS SQL Server application, but when I tried to go through the same process for the Oracle solution which is using Devart for the provider I couldn’t get it to work. Well I went to the Stackify support and documentation area and quickly came across this article What Does Prefix For .NET Profile? which states that “Unfortunately, Devart libraries are not currently supported.”

So did a quick google of Devart profilers and ended up on Devart’s own site. They have a free tool called dbMonitor that “performs per-component tracing of database events such as commit, rollback, SQL statement execute, creating and destroying components, connection pooling events etc.”. It sounded like what I was looking for so I downloaded it and read through the getting started documentation.

You need to add the following using statement to Program.cs

using Devart.Data.Oracle;

You then need to add the following to Program.cs at the top of the Main method

OracleMonitor myMonitor = new OracleMonitor();
myMonitor.IsActive = true;

Now run dbMonitor and then run our application. You should see something like the image below in dbMonitor.

Summary

In this article we showed how to add logging to a .NET Core application using Entity Framework Core so that it logged the SQL being executed. We also showed how to use a profiler to to get more in-depth information about our application.

Related Links

• Entity Framework Core Logging Documentation
• Entity Framework Core Console Logging Example
• Serilog
• Serilog.Extensions.Logging
• List of available sinks for Serilog
• Entity Framework/Core and LINQ to Entities (3) Logging and Tracing Queries
• dbMonitor
• Using dbMonitor
• Prefix
• Using Prefix With ASP.NET Core/Kestrel
• Overview Of Tracking Non Web Transactions
• MiniProfiler
• dotTrace