How to Log the Generated SQL from Dbcontext.Savechanges() in My Program

How can I log the generated SQL from DbContext.SaveChanges() in my Program?

In entity framework 6.0, the Database class has a property Action<string> Log. so setting up logging is as easy as:

context.Database.Log = Console.WriteLine;

For more advanced needs you can set up an interceptor.

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

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

How do I view the SQL generated by the Entity Framework?

You can do the following:

IQueryable query = from x in appEntities
             where x.id == 32
             select x;

var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

or in EF6:

var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
            .ToTraceString();

or in EF6.3+:

var sql = ((dynamic)flooringStoresProducts).Sql;

That will give you the SQL that was generated.

Entity Framework How to see SQL statements for SaveChanges method

In general you can hook up the built-in tracer or any logger by simple

context.Database.Log = msg => Trace.WriteLine(msg);

in the DbContext constructor.
See more in MSDN. Some other approaches from MS are here (all based on DataContext.Log property).

Talking about the Clutch solution mentioned by Nate, it doesn't work with EF v6 (see this bug-report).

REFERENCES

1. Logging and Intercepting Database Operations (EF6 Onwards)
2. Logging and Intercepting Database Operations

---

Entity Framework 6 - How can I view the SQL that will be generated for an insert before calling SaveChanges

Another option (if I understand your question correctly), would be to use an IDbCommandInterceptor implementation, which seemingly allows you to inspect SQL commands before they are executed (I hedge my words as I have not used this myself).

Something like this:

public class CommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(
        DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        // do whatever with command.CommandText
    }
}

Register it using the DBInterception class available in EF in your context static constructor:

static StuffEntities()
{
    Database.SetInitializer<StuffEntities>(null); // or however you have it
    System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new CommandInterceptor());
}

How do you show underlying SQL query in EF Core?

Update for .NET 6: EF logging is enabled by default in development.

Just add "Microsoft.EntityFrameworkCore.Database.Command": "Information" to appsettings.Development.json so it's only logged in dev mode. You typically don't want to log every query in a production app.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=MyDB-2;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
     ,"Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  },
  "AllowedHosts": "*"
}

The SQL output shows in the command window or VS output window.

See SQL Logging of Entity Framework Core in the official docs. It's a bug that it doesn't log by default, see this GitHub issue.

How to see EF SaveChanges() generate what sql

Fortunately I had to do something similar a few years ago. What you have to to do is create a new class derived from DatabaseLogFormatter, override the NonQueryExecuted method and work with the DbCommand argument. This object will hold the the SQL command generate in the CommandText property and the parameters with their values in Parameters. Of course, you will have to proceess the command text and parameters and create a new sql command string that will look like an ordinaly sql statement.

class EFCustomLogFormatter:: DatabaseLogFormatter
{ 
    public EFCustomLogFormatter(DbContext context, Action<string>  writeAction): base(context, writeAction)
    {

    }
    public override void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
       //process your command and text
       Write(processedCommand);
    }

 }

After you all of the above, you need to hookup the new logger to you DbContext. You have to add to the DbContex namespace a new class

 public class NewLoggerForEF: DbConfiguration
    {
        public NewLoggerForEF()
        {
            SetDatabaseLogFormatter((context, writeAction) => new   EFCustomLogFormatter(context, writeAction));
        }
    }

When you will use the

db.Database.Log = command => MyLogger.Log("EFApp", command)

the command string will be properly formatted.

Hope this helps.

The implementation is not that tought so it should have been easy for you to do it. But since I am new to Stackoverflow I will give you an example of the implemented method. The ReplaceWholeWord function is taken from Way to have String.Replace only hit "whole words"

 public override void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {

        foreach (DbParameter parameter in command.Parameters)
        {
            command.CommandText = StringExtendsionsMethods.ReplaceWholeWord(command.CommandText, parametru.ParameterName, "'" + parametru.Value.ToString() + "'");
        }

        string filterR = command.CommandText.Replace('\r', ' ');
        string filterN = faraR.Replace('\n', ' ');
        string cleanSQLCommand = filterN.Replace('`', ' ');

        Write(string.Format(cleanSQLCommand));
    }

Again, it might need some tweaks so test it thoroughly.

In EF 4.1 DbContext how to trace generated SQL

The MVC-Mini-Profiler is a pwerful tool, not ony trace generated sql, but also profiling tool.

Using mvc-mini-profiler database profiling with Entity Framework Code First

How does DbContext.SaveChanges() work in C# when updating a table?

Entity Framework uses a ChangeTracker internally. This keeps the state of an entity as you add, update or delete it from the DbContext.

So, when calling SaveChanges() the change tracker knows what entities and what updates to send.

More info:

https://entityframework.net/change-tracker
or
https://learn.microsoft.com/en-us/ef/core/querying/tracking

---

Related Topics