How can I register the generated SQL from DbContext.SaveChanges () in my program?

Question

According to this thread, we can write the generated SQL through EF , but what about DbContext.SaveChanges() ? Is there an easy way to do this work without any additional frameworks?

Answer 1

In essence, framework 6.0, the Database class has the Action<string> Log property. therefore setting up logging is as simple as:

 context.Database.Log = Console.WriteLine; 

For more advanced needs, you can configure an interceptor . Additional wiki entity framework information

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 mpc application using First code, POCO DbContext, Entity Framework 5.

The context class for the application comes from DbContext:

 public class MyDbContext : DbContext 

The context constructor triggers the SavingChanges event (I only want to make an expensive reflection for debug collections):

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

The save change event writes the generated sql to the output window. The code I copied from Mr. Cook, converts DbParameter to SqlParamter, which I leave as is, because I click on Sql Server, but I assume that the conversion will fail if you click on some other 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()); } 

Answer 3

For short-term logging, I simply insert into the DbContext constructor:

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

Quite quickly add / remove SQL logging. For long-term use, you can wrap in checks with

 #IFDEF DEBUG // or something similar 

Answer 4

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

Build 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 do this in an ASP.NET application, make sure that you do this only once, otherwise you will intercept the same request several times.

DAO example

 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 5

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

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

stack overflow

Answer 6

Tom Regan 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 7

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

Answer 8

That should help, EFTracingProvider

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