How can I log the generated SQL from DbContext.Sav

Question

This question already has an answer here:

• Entity Framework 4.0: How to see SQL statements for SaveChanges method 6 answers

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. More info on the entity framework wiki

Answer 2

Tom Regan's code updated for EF6.

    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 = (System.Data.Entity.Core.Objects.ObjectStateManager)sender.GetType()
              .GetProperty("ObjectStateManager", BindingFlags.Instance | BindingFlags.Public)
              .GetValue(sender, null);

        var workspace = entityConn.GetMetadataWorkspace();

        var translatorT =
            sender.GetType().Assembly.GetType("System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator");

        var entityAdapterT =
            sender.GetType().Assembly.GetType("System.Data.Entity.Core.EntityClient.Internal.EntityAdapter");
        var entityAdapter = Activator.CreateInstance(entityAdapterT, BindingFlags.Instance |
            BindingFlags.NonPublic | BindingFlags.Public, null, new object[] { sender }, System.Globalization.CultureInfo.InvariantCulture);

        entityAdapterT.GetProperty("Connection").SetValue(entityAdapter, entityConn);

        var translator = Activator.CreateInstance(translatorT, BindingFlags.Instance |
            BindingFlags.NonPublic | BindingFlags.Public, null, new object[] { entityAdapter }, System.Globalization.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 =
                (System.Data.Common.DbCommand)cmd.GetType()
                   .GetMethod("CreateCommand", BindingFlags.Instance | BindingFlags.NonPublic)
                   .Invoke(cmd, new[] { identifierValues });

            foreach (System.Data.Common.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());
    }

Answer 3

You can use SQL Server Profiler and run it against the database server you are connecting to.

Answer 4

For short-term logging, I just put into DbContext constructor:

Database.Log = x => Debug.WriteLine(x);

Pretty fast to add/remove logging of SQL. For long-use term, can be wrapped in checks with

#IFDEF DEBUG // or something similar

Answer 5

If you want to capture the actual SQL that has been generated using EF6 (maybe to play back later) using an interceptor, you can do the following.

Create your interceptor

public class InsertUpdateInterceptor : IDbCommandInterceptor
{
    public virtual void NonQueryExecuting(
        DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        logCommand(command);
    }

    public virtual void ReaderExecuting(
        DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        // this will capture all SELECT queries if you care about them..
        // however it also captures INSERT statements as well 
        logCommand(command);
    }

    public virtual void ScalarExecuting(
     DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        logCommand(command);
    }


    private void logCommand(DbCommand dbCommand)
    {
        StringBuilder commandText = new StringBuilder();

        commandText.AppendLine("-- New statement generated: " + System.DateTime.Now.ToString());
        commandText.AppendLine();

        // as the command has a bunch of parameters, we need to declare
        // those parameters here so the SQL will execute properly

        foreach (DbParameter param in dbCommand.Parameters)
        {
            var sqlParam = (SqlParameter)param;

            commandText.AppendLine(String.Format("DECLARE {0} {1} {2}",
                                                    sqlParam.ParameterName,
                                                    sqlParam.SqlDbType.ToString().ToLower(),
                                                    getSqlDataTypeSize(sqlParam));

            var escapedValue = sqlParam.SqlValue.replace("'", "''");
            commandText.AppendLine(String.Format("SET {0} = '{1}'", sqlParam.ParameterName, escapedValue ));
            commandText.AppendLine();
        }

        commandText.AppendLine(dbCommand.CommandText);
        commandText.AppendLine("GO");
        commandText.AppendLine();
        commandText.AppendLine();

        System.IO.File.AppendAllText("outputfile.sql", commandText.ToString());
    }

    private string getSqlDataTypeSize(SqlParameter param)
    {
        if (param.Size == 0)
        {
            return "";
        }

        if (param.Size == -1)
        {
            return "(MAX)";
        }

        return "(" + param.Size + ")";
    }


    // To implement the IDbCommandInterceptor interface you need to also implement these methods like so

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

    public void ReaderExecuted(
        DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ScalarExecuted(
        DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }
}

And you also need to register your interceptor. If you're doing this within an ASP.NET application make sure you only do it once, otherwise you'll end up intercepting the same request multiple times.

Example DAO

public class MyDataDAO
{
    private static bool isDbInterceptionInitialised = false;

    public MyDataDAO()
    {
        if (!isDbInterceptionInitialised)
        {
            DbInterception.Add(new InsertUpdateInterceptor());
            isDbInterceptionInitialised = true;
        }
    }

    public void Insert(string dataToInsert)
    {
        using (myentities context = new myentities())
        {
            MyData myData = new MyData();
            myData.data = dataToInsert;

            // this will trigger the interceptor
            context.SaveChanges();
        }
    }
}

Answer 6

This does the same thing, but for every time you use your context it will write the sql query in the output window. The difference is that it does not compile in release.

public MyEntitities()
    : base()
{
    Database.Log = s => System.Diagnostics.Trace.WriteLine(s);
}

This StackOverflow Explains the Difference between Trace and Debug.