C# 如何在我的程序中记录从 DbContext.SaveChanges() 生成的 SQL？

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

提问by Masoud

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

根据这个线程，我们可以记录生成的SQLvia EF，但是呢DbContext.SaveChanges()？有没有什么简单的方法可以在没有任何额外框架的情况下完成这项工作？

采纳答案by Lorentz Vedeler

In entity framework 6.0, the Database class has a property Action<string> Log. so setting up logging is as easy as:

在实体框架 6.0 中，Database 类有一个属性Action<string> Log。所以设置日志记录很简单：

context.Database.Log = Console.WriteLine;

For more advanced needs you can set up an interceptor.

对于更高级的需求，您可以设置拦截器。

回答by Jay Jay Jay

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

您可以使用 SQL Server Profiler 并针对您要连接的数据库服务器运行它。

回答by Wiktor Zychla

This should help, the EFTracingProvider

这应该会有所帮助，EFTracingProvider

http://code.msdn.microsoft.com/EFProviderWrappers

http://code.msdn.microsoft.com/EFProviderWrappers

回答by Tom Regan

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.

请参阅http://www.codeproject.com/Articles/499902/Profiling-Entity-Framework-5-in-code。我使用 Code First、POCO DbContext、Entity Framework 5 在 asp.net mvc 应用程序中实现了库克先生的想法。

The context class for the application derives from DbContext:

应用程序的上下文类派生自 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):

上下文的构造函数连接了 SavingChanges 事件（我只想为调试构建进行昂贵的反射）：

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.

保存更改事件将生成的 sql 写入输出窗口。我从库克先生那里复制的代码将 DbParameter 转换为 SqlParamter，我保持原样是因为我正在访问 Sql Server，但我假设如果您访问其他类型的数据库，转换会失败。

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

回答by Rocklan

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.

如果您想使用拦截器捕获使用 EF6 生成的实际 SQL（可能稍后播放），您可以执行以下操作。

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.

而且您还需要注册您的拦截器。如果您在 ASP.NET 应用程序中执行此操作，请确保只执行一次，否则您最终将多次拦截相同的请求。

Example DAO

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

回答by Brandon Barkley

Tom Regan's code updated for EF6.

为 EF6 更新了 Tom Regan 的代码。

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

回答by TPAKTOPA

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

对于短期日志记录，我只是放入了 DbContext 构造函数：

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

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

添加/删除 SQL 的日志记录非常快。对于长期使用，可以用支票包裹

#IFDEF DEBUG // or something similar

回答by Travis Peterson

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.

这做同样的事情，但每次使用上下文时，它都会在输出窗口中编写 sql 查询。不同之处在于它不在发行版中编译。

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

This StackOverflow Explains the Difference between Trace and Debug.

此 StackOverflow 解释了跟踪和调试之间的区别。