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

c# dbcontext entity-framework logging sql

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?

1
81
5/23/2017 11:47:12 AM

Accepted Answer

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.

126
9/23/2019 1:48:38 PM

Popular Answer

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());
    }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow