How to view SQL genarated by the Entity Framework Core with in-memory provider?

Question

I want to view SQL generated by Linq in EF Core, but when the database is in-memory, there are no string SQL data.

I use Npgsql.EntityFrameworkCore.PostgreSQL (7.0.4), Microsoft.EntityFrameworkCore (7.0.10), Visual Studio 2022.

.... 
// example of how I configure DbContext
DbContextOptions<CollectorContext> options = new DbContextOptionsBuilder<CollectorContext>()
                .UseInMemoryDatabase(dbName)


// example of how I query data
var builtInParameterGrQuary = db.RevitBuiltInParameterGroups
    .Where(x => !modelData.RevitBuiltInParameterGroups
            .Select(d => d.Name)
            .Contains(x.Name));
var str = builtInParameterGrQuary.ToQueryString();

The variable str shows me:

There is no query string because the in-memory provider does not use a string-based query language

The same message was in the DebugView of the EntityQueryable class.

DebugView for entity query

How to view SQL generated data when database is in-memory?

Answer 1

In memory database don't generate any SQL scripts. It is based on general data collection like List. You can only generate SQL if you are working against real DB like SQL server, SQL Lite ..etc . You can also log generated SQL to console.

1. Adjust dbcontext class to take db context options.

public class CollectorContext : DbContext
{
   .........
   .........

    public CollectorContext()
    {
        
    }
    public PublisherDBContext(DbContextOptions<CollectorContext> options)
        :base(options)
    {
        
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    { 
        if(!optionsBuilder.IsConfigured)
        {

        }

2. Generate db context with required logging option.

   var optionsBuilder = new DbContextOptionsBuilder<CollectorContext>()
                              .UseNpgsql("connection string")
                               .LogTo(Console.WriteLine
                               , new[] { DbLoggerCategory.Database.Command.Name }
                               , LogLevel.Information)
                                .EnableSensitiveDataLogging();

   db = new CollectorContext(optionsBuilder.Options);