EF Core Logging SQL Queries when Unit Testing

Question

I'm running some unit tests (NUnit) on my DbContext (to an Oracle database) in EF Core 2.2 and I'd like to see the SQL queries it's constructing for each unit test, preferably

in the Debug window and
in the Test Explorer pane's detail view of each test.

I have a Unit Test similar to:

[SetUp]
public void Setup()
{
    var options = new DbContextOptionsBuilder<MyContext>()
        .UseOracle("some connection string")
        .Options;

    _context = new MyContext(options);
}
[Test]
public void We_can_count_all_the_things()
{
    var count = _context.Things.Count();

    Assert.That(count, Is.GreaterThan(0));

    // something like this for Test output:
    Assert.Pass($"SQL QUERY:{???}")
}

... or maybe I can use an ILogger to direct output to the test result or some magic I'm unaware of.

Any assistance is appreciated.

score 2 · Answer 1 · answered Aug 24 '19 at 06:11

There's an answer here that shows how to log EF Core SQL queries using the Microsoft.Extensions.Logging.Console package...

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseLoggerFactory(MyLoggerFactory) // Warning: Do not create a new ILoggerFactory instance each time
        .UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=EFLogging;Trusted_Connection=True;ConnectRetryCount=0");

With something like this:-

public static readonly LoggerFactory MyLoggerFactory
    = new LoggerFactory(new[] {new ConsoleLoggerProvider((_, __) => true, true)});

Further info here

The argument to `new ConsoleLoggerProvider` now has type `IOptionsMonitor`, so that code does not compile. There is an explanation of how to create the object at https://mikehadlow.com/posts/standalone-consoleloggerprovider/, but after fixing the error and using code similar to that above (plus `optionsBuilder.EnableSensitiveDataLogging()`), my unit tests still produce no output (neither in Test Explorer nor in the Output panel). The SQL only appears when running standalone in a console (via Main() => Startup). — Qwertie, Oct 28 '21 at 17:27

Old Fox · Accepted Answer · 2019-08-23T23:11:51.140

1

NUnit's TestContext.WriteLine is what you are looking for:

...
TestContext.WriteLine($"SQL QUERY:{???}");

Edit:

To get the generated SQL:

EF core: Get SQL code from an EF Core query

EF 6: How do I view the SQL generated by the Entity Framework?

edited Aug 23 '19 at 23:11

answered Aug 23 '19 at 22:50

Old Fox

8,629
4
34
52

1

That's half of half of the question - where can I get the SQL statement that was used? – Scott Baker Aug 23 '19 at 22:56
1

@ScottBaker [How do I view the SQL generated by the Entity Framework?](https://stackoverflow.com/a/1412902/4332059) and [Get SQL code from an EF Core query](https://stackoverflow.com/a/44180537/4332059) – Old Fox Aug 23 '19 at 22:58

EF Core Logging SQL Queries when Unit Testing

2 Answers2

Edit: