Entity Framework Core, manual DbContext instantiation and SQL logging

DmitryShunkov

When a connection is selected in a dropdown, LinqPad shows SQL that was generated by a provider in SQL tab (through internal interception mechanism). But, if DbContext is instantiated in code, is there is a way to configure LinqPad or adjust code to be able to view SQL queries generated by EF in the same manner?

I have only found a way through custom LoggerFactory and ILogger implementation with Util.SqlOutputWriter.WriteLine call and passing this factory instance to UseLoggerFactory(Logger.MyLoggerFactory) method call in DbContext.OnConfiguring method. But this SQL output has a different format and this is not a very convenient approach, because every time I need to view SQL for my code with manual DbContext instantiation I should add this bulky Logger configuration.

JoeAlbahari

Good point - I'll add a EnableLINQPadLogging() extension method to the next build.

DmitryShunkov

Hello, Joe! I checked out the latest beta 6.10.10 with this extension method. Thank you for adding it! But I have some comments:

It would be nice if the EnableLINQPadLogging method is accessible even if no connection selected in the dropdown. There are situations when people may need to test some logic on DbContext derived class declared in code without any assembly reference;

It seems that EFLINQPadProvider added every time I run the query because logs for every database query multiplied by the count of previous running until I click "Cancel and Reset All Queries";

When the connection with custom assembly selected in the dropdown and context from this assembly instantiated in the code: after I click "Cancel and Reset All Queries" and click "Run query" - there is an error: No database provider has been configured for this DbContext. There is no error after I click "Run query" again.

JoeAlbahari

What version of EF Core are you using?

DmitryShunkov

Hello!

So, I tested in LinqPad beta 6.10.13 the following query with two connections selected in the dropdown (one with custom assembly referencing Microsoft.EntityFrameworkCore, Version=2.2.4.0 and one with custom assembly referencing Microsoft.EntityFrameworkCore, Version=3.1.4.0).

public class Company
{
    public Guid Id { get; set; }
    public string Name { get;set; }
}

public class DatabaseContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder
            .EnableSensitiveDataLogging()
            .UseNpgsql(
                "Host=localhost;Database=test.linqpadlogging;" 
                + $"Username={Util.GetPassword("docker.postgres.username")};Password={Util.GetPassword("docker.postgres.password")}");
    }

    public DbSet<Company> Companies { get; set; }
}

void Main()
{
    var assemblies = System.AppDomain.CurrentDomain.GetAssemblies().Where(x => x.FullName.Contains("EntityFrameworkCore")).Select(p => new
    {
        p.FullName,
    }).Dump();

    using (var db = new DatabaseContext())
    {
        db.EnableLINQPadLogging();

        db.Database.EnsureDeleted();
        db.Database.EnsureCreated();

        db.Companies.Add(new Company { Name = "Test company" });

        db.SaveChanges();

    }
}

Please, notice that code doesn't use references from selected custom assemblies (except EF Core transitive dependencies).

Results:

• First connection:

Microsoft.EntityFrameworkCore, Version=2.2.4.0, Culture=neutral, PublicKeyToken=adb9793829ddae60 
Microsoft.EntityFrameworkCore.Abstractions, Version=2.2.4.0, Culture=neutral, PublicKeyToken=adb9793829ddae60 
Npgsql.EntityFrameworkCore.PostgreSQL, Version=2.2.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7 
Microsoft.EntityFrameworkCore.Relational, Version=2.2.4.0, Culture=neutral, PublicKeyToken=adb9793829ddae60 
Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite, Version=2.2.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7 
Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime, Version=2.2.0.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7 

Log entries multiplied by the count of "Run query" clicks, and No database provider has been configured for this DbContext exception after the first run.

• Second connection:

Microsoft.EntityFrameworkCore, Version=3.1.4.0, Culture=neutral, PublicKeyToken=adb9793829ddae60 
Microsoft.EntityFrameworkCore.Abstractions, Version=3.1.4.0, Culture=neutral, PublicKeyToken=adb9793829ddae60 
Npgsql.EntityFrameworkCore.PostgreSQL, Version=3.1.4.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7 
Microsoft.EntityFrameworkCore.Relational, Version=3.1.4.0, Culture=neutral, PublicKeyToken=adb9793829ddae60 

Log entries are ok, no exception.

I can further investigate to get a reproduce code of specific configuration in the custom assembly with EF Core 2.2.4.0 that causes this behavior. But what I'm proposing is that the EnableSensitiveDataLogging extension method would be accessible without the need to select any connection like for Util methods, is it possible?

JoeAlbahari

That makes sense - EF Core 2.x lets you add the same logger over and over. They fixed this in EF Core 3.x.

But what I'm proposing is that the EnableSensitiveDataLogging extension method would be accessible without the need to select any connection like forUtil methods, is it possible

That's not easy. It can't be part of the LINQPad runtime, otherwise every query that you write (with or without a connection) would take a dependency on EF Core.