c# - Entity Framework Core: Log queries for a single db context instance

Question

Using EF Core (or any ORM for that matter) I want to keep track of the number of queries the ORM makes to the database during some operation in my software.

I've used SQLAlchemy under Python earlier, and on that stack this is faily easy to set up. I typically have unit tests that assert on the number of queries made for a scenario, against an in-memory SQLite database.

Now I want to do the same thing using EF Core, and have looked at the Logging documentation.

In my test setup code I do as the documentation says:

using (var db = new BloggingContext())
{
    var serviceProvider = db.GetInfrastructure<IServiceProvider>();
    var loggerFactory = serviceProvider.GetService<ILoggerFactory>();
    loggerFactory.AddProvider(new MyLoggerProvider());
}

But I run into problems that I suspect are the results of the following (also from the docs):

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.

The problems I see in my tests indicates that my logger implementation is shared across multiple contexts (this is in accordance with the docs as I read them). And since a) my test runner runs tests in parallell and b) my entire test suite creates hundreds of db contexts - it does not work very well.

Question/issues:

• Is what I want possible?
• I.e. can I register a logger with a db context that is only used for that db context instance?
• Are there other ways to accomplish what I am trying to do?

Answer 1

Call DbContextOptionsBuilder.UseLoggerFactory(loggerFactory) method to log all SQL output of a particular context instance. You could inject a logger factory in the context's constructor.

Here is a usage example:

//this context writes SQL to any logs and to ReSharper test output window
using (var context = new TestContext(_loggerFactory))
{
    var customers = context.Customer.ToList();
}

//this context doesn't
using (var context = new TestContext())
{
    var products = context.Product.ToList();
}

Generally, I use this feature for manual testing. To keep the original context class clean, a derived testable context is declared with overridden OnConfiguring method:

public class TestContext : FooContext
{
    private readonly ILoggerFactory _loggerFactory;

    public TestContext() { }

    public TestContext(ILoggerFactory loggerFactory)
    {
        _loggerFactory = loggerFactory;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder.UseLoggerFactory(_loggerFactory);
    }
}

It's enough to log SQL queries. Don't forget to attach a suitable logger (like Console) to loggerFactory before you pass it to context.

Part II: Pass logs to xUnit output and ReSharper test output window

We can create a loggerFactory in a test class constructor:

public class TestContext_SmokeTests : BaseTest
{
    public TestContext_SmokeTests(ITestOutputHelper output)
        : base(output)
    {
        var serviceProvider = new ServiceCollection().AddLogging().BuildServiceProvider();

        _loggerFactory = serviceProvider.GetService<ILoggerFactory>();

        _loggerFactory.AddProvider(new XUnitLoggerProvider(this));
    }

    private readonly ILoggerFactory _loggerFactory;
}

The test class is derived from BaseTest which supports the writing to xUnit output:

public interface IWriter
{
    void WriteLine(string str);
}

public class BaseTest : IWriter
{
    public ITestOutputHelper Output { get; }

    public BaseTest(ITestOutputHelper output)
    {
        Output = output;
    }

    public void WriteLine(string str)
    {
        Output.WriteLine(str ?? Environment.NewLine);
    }
}

The most tricky part is to implement a logging provider accepting IWriter as a parameter:

public class XUnitLoggerProvider : ILoggerProvider
{
    public IWriter Writer { get; private set; }

    public XUnitLoggerProvider(IWriter writer)
    {
        Writer = writer;
    }
    public void Dispose()
    {
    }

    public ILogger CreateLogger(string categoryName)
    {
        return new XUnitLogger(Writer);
    }

    public class XUnitLogger : ILogger
    {
        public IWriter Writer { get; }

        public XUnitLogger(IWriter writer)
        {
            Writer = writer;
            Name = nameof(XUnitLogger);
        }

        public string Name { get; set; }

        public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception,
            Func<TState, Exception, string> formatter)
        {
            if (!this.IsEnabled(logLevel))
                return;

            if (formatter == null)
                throw new ArgumentNullException(nameof(formatter));

            string message = formatter(state, exception);
            if (string.IsNullOrEmpty(message) && exception == null)
                return;

            string line = $"{logLevel}: {this.Name}: {message}";

            Writer.WriteLine(line);

            if (exception != null)
                Writer.WriteLine(exception.ToString());
        }

        public bool IsEnabled(LogLevel logLevel)
        {
            return true;
        }

        public IDisposable BeginScope<TState>(TState state)
        {
            return new XUnitScope();
        }
    }

    public class XUnitScope : IDisposable
    {
        public void Dispose()
        {
        }
    }
}

We've done here! All the SQL logs will be shown in Rider/Resharper test output window.