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

Question

According this thread, we can log the generated SQL via EF, but what about DbContext.SaveChanges()? Is there any easy way to do this job without any extra frameworks?

Answer 1

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.

Answer 2

See http://www.codeproject.com/Articles/499902/Profiling-Entity-Framework-5-in-code. I implemented Mr. Cook's idea in an asp.net mvc application using a Code First, POCO DbContext, Entity Framework 5.

The context class for the application derives from DbContext:

public class MyDbContext : DbContext 

The constructor for the context hooks up the SavingChanges event (I only want to do the expensive reflection for debug builds):

public MyDbContext(): base("MyDbContext") { #if DEBUG     ((IObjectContextAdapter)this).ObjectContext.SavingChanges += new EventHandler(objContext_SavingChanges); #endif } 

The saving changes event writes the generated sql to the output window. The code I copied from Mr. Cook converts the DbParameter to a SqlParamter, which I leave as-is because I'm hitting a Sql Server, but I'm assuming that conversion would fail if you are hitting some other kind of database.

public void objContext_SavingChanges(object sender, EventArgs e)     {         var commandText = new StringBuilder();          var conn = sender.GetType()              .GetProperties(BindingFlags.Public | BindingFlags.Instance)              .Where(p => p.Name == "Connection")              .Select(p => p.GetValue(sender, null))              .SingleOrDefault();         var entityConn = (EntityConnection)conn;          var objStateManager = (ObjectStateManager)sender.GetType()               .GetProperty("ObjectStateManager", BindingFlags.Instance | BindingFlags.Public)               .GetValue(sender, null);          var workspace = entityConn.GetMetadataWorkspace();          var translatorT =             sender.GetType().Assembly.GetType("System.Data.Mapping.Update.Internal.UpdateTranslator");          var translator = Activator.CreateInstance(translatorT, BindingFlags.Instance |             BindingFlags.NonPublic, null, new object[] {objStateManager,workspace,             entityConn,entityConn.ConnectionTimeout }, CultureInfo.InvariantCulture);          var produceCommands = translator.GetType().GetMethod(             "ProduceCommands", BindingFlags.NonPublic | BindingFlags.Instance);          var commands = (IEnumerable<object>)produceCommands.Invoke(translator, null);          foreach (var cmd in commands)         {             var identifierValues = new Dictionary<int, object>();             var dcmd =                 (DbCommand)cmd.GetType()                    .GetMethod("CreateCommand", BindingFlags.Instance | BindingFlags.NonPublic)                    .Invoke(cmd, new[] { translator, identifierValues });              foreach (DbParameter param in dcmd.Parameters)             {                 var sqlParam = (SqlParameter)param;                  commandText.AppendLine(String.Format("declare {0} {1} {2}",                                                         sqlParam.ParameterName,                                                         sqlParam.SqlDbType.ToString().ToLower(),                                                         sqlParam.Size > 0 ? "(" + sqlParam.Size + ")" : ""));                  commandText.AppendLine(String.Format("set {0} = '{1}'", sqlParam.ParameterName, sqlParam.SqlValue));             }              commandText.AppendLine();             commandText.AppendLine(dcmd.CommandText);             commandText.AppendLine("go");             commandText.AppendLine();         }          System.Diagnostics.Debug.Write(commandText.ToString());     }