Get generated SQL for a DbContext.SaveChanges in Entity Framework Core

Question

In Entity Framework Core, is it possible to see the SQL that will be applied when the SaveChanges() method is called on the DbContext?

Answer 1

EF Core 5.0+

There are a simple builtin solution, add the following function to the DbContext class.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.LogTo(Console.WriteLine);

this will log both queries and commands.

See here for mote details: Simple Logging

Answer 2

It's a bit tricky to log EF Core's SQL in an ASP.NET MVC web app. Unlike Entity Framework, EF Core lacks the easy-to-use DBContext.Database.Log property. As @DharmaTurtle mentioned, you can use LoggerFactory.Create, and this can work, but it does create a separate ILoggerFactory than the one that the rest of the app uses for logging (one that is apparently not using appsettings.json for options.)

The following approach is required if you want to use the same log factory for DBContext that the rest of the ASP.NET MVC web app uses:

1. Create a derived class of DbContext (or, if this was already done, modify the existing class appropriately). Note: this example will only log SQL in Debug builds, not Release builds.

   public class DbContextWithLogging : Microsoft.EntityFrameworkCore.DbContext
   {
       ILoggerFactory _loggerFactory;
       IConfiguration _configuration; // Provides access to appsettings.json
   
       public DbContextWithLogging(ILoggerFactory loggerFactory, IConfiguration configuration)
           => (_loggerFactory, _configuration) = (loggerFactory, configuration);
   
       protected override void OnConfiguring(DbContextOptionsBuilder builder)
       {
           #if DEBUG
           builder.UseLoggerFactory(_loggerFactory);
           // This line causes parameter values to be logged:
           builder.EnableSensitiveDataLogging();
           #endif
       }
   }
   

   Note: this approach is not compatible with calling AddDbContext in Startup.ConfigureServices, so if there is already a call to AddDbContext, disable/remove it. In my case, the existing derived class of DbContext had a constructor that accepted (DbContextOptions<BarreleyeDbContext> options), which I removed.

2. In your Startup.ConfigureServices method, configure logging (e.g. to print to console) and enable the custom DbContext:

   public void ConfigureServices(IServiceCollection services)
   {
       services.AddLogging((ILoggingBuilder builder) => {
           builder.AddConsole();
       });
   
       // In ASP.NET Core apps, a Scope is created around each server request.
       // So AddScoped<X, Y>() will recreate class Y for each HTTP request.
       services.AddScoped<DbContext, DbContextWithLogging>();
   
       ... // leave the rest as before
   }
   

3. Whatever uses DbContext (e.g. controllers, or 'repositories' in the Repository pattern) should obtain it automagically via constructor injection.

4. EF Core uses LogLevel.Information when printing the SQL, so you need the Information level to be enabled. If you look at your appsettings.json file, you'll want to see something like this:

     "Logging": {
       "LogLevel": {
         "Default": "Information",
         "Microsoft": "Information",
         "Microsoft.Hosting.Lifetime": "Information"
       }
     },
   

   In particular, EF Core log filtering can be controlled with a key like

    "Microsoft.EntityFrameworkCore": "Information",
   

   but if this key is missing, the "Microsoft" key is used instead.

   This might not work! Look for a second file called appsettings.Development.json - watch out, Visual Studio might hide this file "inside" appsettings.json. If appsettings.Development.json exists, its contents override appsettings.json (at the granularity of individual keys).

Once it's working, you'll see log info that looks like this (yes, SELECT statements are logged as well as INSERT, UPDATE and DELETE):

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[@__p_0='297'], CommandType='Text', CommandTimeout='30']
      SELECT e.id, e.config, e.end_date, e.entity_name, e.entity_type, e.location, e.start_date
      FROM entities AS e
      WHERE e.id = @__p_0
      LIMIT 1

Answer 3

Here are the docs on creating a LoggerFactory in Core 3. In short:

var loggerFactory = LoggerFactory.Create(builder =>
{
    builder
        .AddFilter("Microsoft", LogLevel.Warning)
        .AddFilter("System", LogLevel.Warning)
        .AddFilter("LoggingConsoleApp.Program", LogLevel.Debug)
        .AddConsole()
        .AddEventLog();
});

You may need to add a reference to Microsoft.Extensions.Logging.Console.

Use the DbContextOptionsBuilder to enable logging for a context.

optionsBuilder.UseLoggerFactory(loggerFactory)

I'll repeat the warning from here:

It is very important that applications do not create a new ILoggerFactory instance for each context instance. Doing so will result in a memory leak and poor performance.

Therefore, they recommend using a singleton/global instance:

public static readonly ILoggerFactory MyLoggerFactory =
    LoggerFactory.Create(builder => { builder.AddConsole(); });

Answer 4

You can use DbContextOptionsBuilder.UseLoggerFactory(loggerFactory) method to log all sql output.By using constructor Injection like below

public class DemoContext : ObjContext
{
    private readonly ILoggerFactory _loggerFactory;

    public DemoContext() { }

    public DemoContext(ILoggerFactory loggerFactory)
    {
        _loggerFactory = loggerFactory;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder.UseLoggerFactory(_loggerFactory);
    }
}

using (var context = new DemoContext(_loggerFactory))
{
    var Employees = context.Employee.ToList();
}

Or

I suggest a few other ways of viewing the SQL generated is to use reflection to create an ObjectQuery object and then call the ToTraceString() method to actually store the query results.

using (var context = new EntityContext())
{
    var query = context.Customers.Where(c => c.Id == 1); 
    var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();  
}

Use SQL Logging

Using The DbContext.Database.Log property can be set to a delegate for any method that takes a string.

Log SQL to the Console.

using (var context = new EntityContext())
{
    context.Database.Log = Console.Write; 
}

Log SQL to Visual Studio Output panel.

using (var context = new EntityContext())
{
    context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); 

}

Answer 5

you can use console logger "EF Core logging automatically integrates with the logging mechanisms of .NET Core " you can read about here : https://www.entityframeworktutorial.net/efcore/logging-in-entityframework-core.aspx