Double logging of SQL when using EF and Npgsql in DI

[roji]

Since we have NpgsqlDataSource, the "best practice" way of setting up EFCore.PG in an ASP.NET application is as follows:

builder.Services.AddNpgsqlDataSource(@"Host=localhost;Username=test;Password=test");
builder.Services.AddDbContextPool<BlogContext>(o => o.UseNpgsql());

In other words, the NpgsqlDataSource is added separately of EF (that's the lower-level ADO.NET layer), and then EFCore.PG automatically finds that in DI (that's UseNpgsql()).

This works really well, allows clear layer separation and configurability of each layer. Unfortunately, both layers automatically look for an ILoggerFactory in DI and log through it. And since both components log SQLs at Info level, the user gets to see their SQL twice. This can be worked around by setting the default log-level for Npgsql.Command to Warn, preferring the EF logs (which generally provide more information).

Long-term, the solution is likely related to #2542: we already want to allow users to do NpgsqlDataSource configuration via the EF UseNpgsql API, so that EF-level configs can also take care of ADO-level configs. For example, since NodaTime support at the EFCore.PG layer relies on NodaTime support at the Npgsql layer, doing UseNodaTime() in EFCore.PG's UseNpgsql() would implicitly turn on NodaTime on the NpgsqlDataSource as well (today users need to do UseNodaTime() twice).

If we implement this, then the EFCore.PG UseNpgsql() could use an API to tell NpgsqlDataSourceBuilder to not log SQL, since that's already taken care of by EF.

/cc @ajcvickers @adamsitnik @davidfowl

Full source for minimal app

builder.Services.AddNpgsqlDataSource(@"Host=localhost;Username=test;Password=test");
builder.Services.AddDbContextPool(o => o.UseNpgsql());

var app = builder.Build();

app.MapGet("/", async (BlogContext context) => "Hello World!: " + await context.Blogs.CountAsync());

app.Run();

public class BlogContext : DbContext
{
public BlogContext(DbContextOptions options)
: base(options)
{
}

public DbSet<Blog> Blogs { get; set; }

}

public class Blog
{
public int Id { get; set; }
public string? Name { get; set; }
}

</details>

[adamsitnik]

This can be worked around by setting the default log-level for Npgsql.Command to War

Is it possible to do that with NpgsqlDataSourceBuilder or any other existing API?

So far the best thing I found is:

builder.Configuration.AddInMemoryCollection(new KeyValuePair<string, string>[1]
{
    new KeyValuePair<string, string>("Logging:LogLevel:Npgsql.Command", "Warning")
});