How to see EF SaveChanges() generate what sql

Question

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.

yes i saw value was showing one by one when i was debugging but i want to see complete insert statement with value from Log function. so tell me how to customize as a result my Log function always show sql with field name and their values. thanks — Mou, Nov 04 '16 at 12:58
Well log is just a log - bunch of text lines. This is exactly how EF executes those queries (using parameters), so it logs those queries this way. You can only try to manually parse those lines one by one and replace parameter names with values, though it might not be very easy. — Evk, Nov 04 '16 at 13:07

score 0 · Accepted Answer · edited May 23 '17 at 10:32

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.

can u plzz give me full code. now you code has function called EFCustomLogFormatter, NonQueryExecuted which is almost empty. — Mou, Nov 04 '16 at 13:50

How to see EF SaveChanges() generate what sql

I created class

1 Answers1