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.

Aleks · Accepted Answer · 2019-08-24 06:11:49Z

2

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

answered Aug 24, 2019 at 6:11

Aleks

1,68914 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Qwertie Over a year ago

The argument to new ConsoleLoggerProvider now has type IOptionsMonitor<ConsoleLoggerOptions>, so that code does not compile. There is an explanation of how to create the object at 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).

Old Fox · Accepted Answer · 2019-08-23 23:11:51Z

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, 2019 at 23:11

answered Aug 23, 2019 at 22:50

Old Fox

8,7355 gold badges36 silver badges54 bronze badges

2 Comments

Scott Baker Over a year ago

That's half of half of the question - where can I get the SQL statement that was used?

Old Fox Over a year ago

@ScottBaker How do I view the SQL generated by the Entity Framework? and Get SQL code from an EF Core query

Collectives™ on Stack Overflow

EF Core Logging SQL Queries when Unit Testing

2 Answers 2

1 Comment

Edit:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Edit:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related