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

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

This should help, the EFTracingProvider

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

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

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.

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

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

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

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.