Mou Mou - 1 month ago 11
C# Question

How to see EF SaveChanges() generate what sql

I am interested to see what sql is generated by

EF SaveChanges()
. so I search google and found one easy trick to do it.

I created class



public class MyLogger
{
public static void Log(string component, string message)
{
Console.WriteLine("Component: {0} Message: {1} ", component, message);
}
}


also hook log function this way

using (var db = new TestDBContext())
{
db.Database.Log = s => MyLogger.Log("EFApp", s);

var existingCustomer = db.Customer
.Include(a => a.Addresses.Select(x => x.Contacts))
.FirstOrDefault(p => p.CustomerID == 5);

existingCustomer.FirstName = "Test Customer123";
db.SaveChanges();
}


and saw the partial insert sql statement generated as below

INSERT [dbo].[Addresses]([Address1], [Address2], [IsDefault], [SerialNo], [CustomerID])
VALUES (@0, @1, @2, @3, @4)
SELECT [AddressID]
FROM [dbo].[Addresses]
WHERE @@ROWCOUNT > 0 AND [AddressID] = scope_identity()


why i said partial because there was no value. so please tell me how could see insert or update statement generated by
SaveChanges()
function with proper values.

Answer

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.