dotnet core 3.0 + Entity Framework Tip – 如何印出底層自動產生的SQL

在寫 dotnet core / .net MVC 的專案時，通常都會搭配 Entity Framework (簡稱EF)來存取資料庫，如 MS SQL、MySQL 等等。

以我們這次的案子來說，會了讓同仁能更了解 EF的底層機制，所以想說在 dotnet core 3.0 的程式裏，加上設定，方便觀察各個指令會對應到的SQL Script.

以 dotnet core 3.0來說，我們要做的設定動作有
一、產生對應 LoggerFactory (*比如說 : 是要寫到Console、或是 Event 、DB 等其它目的地)
二、在程式連結資料庫(DBContext)的時候，把上面產生的 Logger也一併綁定，方便系統輸出。

 public partial class InvoiceWebContext : DbContext
 {

        public static readonly ILoggerFactory loggerFactory =
            LoggerFactory.Create(builder => {builder.add();});

        public InvoiceWebContext()
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder
                .UseLoggerFactory(loggerFactory)  //tie-up DbContext with LoggerFactory object
                .EnableSensitiveDataLogging()
                .UseSqlServer("Server=;Database=;");
        }
    }
}

public partial class InvoiceWebContext : DbContext

{

public static readonly ILoggerFactory loggerFactory =

LoggerFactory.Create(builder => {builder.add();});

public InvoiceWebContext()

{

}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

{

if (!optionsBuilder.IsConfigured)

{

optionsBuilder

.UseLoggerFactory(loggerFactory) //tie-up DbContext with LoggerFactory object

.EnableSensitiveDataLogging()

.UseSqlServer("Server=;Database=;");

}

如果是用 dotnet Core 2.x的話，
DBContext 裏的設定如下


IServiceCollection serviceCollection = new ServiceCollection();
serviceCollection.AddLogging(builder => builder
    .AddConsole()
    .AddFilter(level => level >= LogLevel.Information)
);
var loggerFactory = serviceCollection.BuildServiceProvider().GetService&lt;ILoggerFactory>();

IServiceCollection serviceCollection = new ServiceCollection();

serviceCollection.AddLogging(builder => builder

.AddConsole()

.AddFilter(level => level >= LogLevel.Information)

);

var loggerFactory = serviceCollection.BuildServiceProvider().GetService<ILoggerFactory>();

參考資料 : https://stackoverflow.com/questions/53690820/how-to-create-a-loggerfactory-with-a-consoleloggerprovider

Facebook Comments