none
Logging sql statements?

    Question

  • Perhaps I am missing something obvious, but how does one log (or view) sql statements being issued to the database? LINQ to SQL has this feature handy (DataContext.Log property).
    Thursday, May 03, 2007 8:36 PM

Answers

  • We haven’t finalized that story yet.

     

    Currently there is a complex API pattern that allows you to get the collection of store DbCommand’s. (One Entity SQL query may be decomposed to multiple store queries.) Then you can get the .CommandText of each DbCommand. I don’t even want to post that pattern.

     

    We are considering exposing the store text right off EntityCommand but we haven’t gotten to the details yet. Either way, we will provide a way for you to get the store command text.

     

     

    Zlatko Michailov

    Program Manager, Data Programmability

    Microsoft Corp.

     

    Thursday, May 03, 2007 9:54 PM
    Moderator
  • Yes. Both EntityCommand and ObjectQuery have a method ToTraceString() that returns the native command text.

    Friday, March 07, 2008 5:11 PM
    Moderator

All replies

  • We haven’t finalized that story yet.

     

    Currently there is a complex API pattern that allows you to get the collection of store DbCommand’s. (One Entity SQL query may be decomposed to multiple store queries.) Then you can get the .CommandText of each DbCommand. I don’t even want to post that pattern.

     

    We are considering exposing the store text right off EntityCommand but we haven’t gotten to the details yet. Either way, we will provide a way for you to get the store command text.

     

     

    Zlatko Michailov

    Program Manager, Data Programmability

    Microsoft Corp.

     

    Thursday, May 03, 2007 9:54 PM
    Moderator
  • Miha,

     

    I am running my experiments against a SQL server, and I just turn on a profiling session to see what SQL queries are run on the server.

     

     

    Morten

     

    Wednesday, May 23, 2007 9:03 AM
  • Sure, but it is annoying...
    Wednesday, May 23, 2007 9:17 AM
  • Has there been an update to this issue?  Sadly, I don't have permissions to run profiler.

     

    Friday, March 07, 2008 12:45 AM
  • Yes. Both EntityCommand and ObjectQuery have a method ToTraceString() that returns the native command text.

    Friday, March 07, 2008 5:11 PM
    Moderator
  • But is there possibility to track ALL statements generated and executed (in LinqToSql it is done by DataContext.Log property) by EF ObjectContext object?

     

    I'm specially interested in tracking all SQL statements executed as a result of calling ObjectContext.SaveChanges() method - where I don't have access to EntityCommand instances being executed.

     

    Wednesday, August 13, 2008 4:03 PM
  • In EF V1 there is no easy way to track update statements issued during SaveChanges(). This kind of tracing is possible to implement at the provider level - but it requires some significant work. We are hoping to publish a sample that will demonstrate how to achieve this using a reusable intercepting provider that sits between EF and actual store provider.

     

    We are definitely thinking on improving this experience for EF V2, so that having a special provider is no longer required.

     

    Pozdrawiam, Jarek

    Wednesday, August 13, 2008 4:27 PM
    Moderator
  • You can try the following extension methods. I am using them for debugging since SQL Server CE doesen't have a profiler.
    Keep in mind that the code uses reflection so it may not be as good for production purposes.

    You call the ToTraceString() method on an ObjectContext and it returns the SQL that will be executed once SaveChanges() is called.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Reflection;
    using System.Data.Objects;
    using System.Data.Common;
    using System.Data.EntityClient;
    using System.Collections;

    namespace EntityExtensionMethods
    {
        public static class CustomExtensions
        {
            private static readonly string entityAssemblyName =
                "system.data.entity, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";

            public static string ToTraceString(this IQueryable query)
            {
                System.Reflection.MethodInfo toTraceStringMethod = query.GetType().GetMethod("ToTraceString");

                if (toTraceStringMethod != null)
                    return toTraceStringMethod.Invoke(query, null).ToString();
                else
                    return "";
            }

            public static string ToTraceString(this ObjectContext ctx)
            {
                Assembly entityAssemly = Assembly.Load(entityAssemblyName);

                Type updateTranslatorType = entityAssemly.GetType(
                    "System.Data.Mapping.Update.Internal.UpdateTranslator");

                Type functionUpdateCommandType = entityAssemly.GetType(
                    "System.Data.Mapping.Update.Internal.FunctionUpdateCommand");

                Type dynamicUpdateCommandType = entityAssemly.GetType(
                    "System.Data.Mapping.Update.Internal.DynamicUpdateCommand");

                object[] ctorParams = new object[]
                            {
                                ctx.ObjectStateManager,
                                ((EntityConnection)ctx.Connection).GetMetadataWorkspace(),
                                (EntityConnection)ctx.Connection,
                                ctx.CommandTimeout
                            };

                object updateTranslator = Activator.CreateInstance(updateTranslatorType,
                    BindingFlags.NonPublic | BindingFlags.Instance, null, ctorParams, null);

                MethodInfo produceCommandsMethod = updateTranslatorType
                    .GetMethod("ProduceCommands", BindingFlags.Instance | BindingFlags.NonPublic);
                object updateCommands = produceCommandsMethod.Invoke(updateTranslator, null);

                List<DbCommand> dbCommands = new List<DbCommand>();

                foreach (object o in (IEnumerable)updateCommands)
                {
                    if (functionUpdateCommandType.IsInstanceOfType(o))
                    {
                        FieldInfo m_dbCommandField = functionUpdateCommandType.GetField(
                            "m_dbCommand", BindingFlags.Instance | BindingFlags.NonPublic);

                        dbCommands.Add((DbCommand)m_dbCommandField.GetValue(o));
                    }
                    else if (dynamicUpdateCommandType.IsInstanceOfType(o))
                    {
                        MethodInfo createCommandMethod = dynamicUpdateCommandType.GetMethod(
                            "CreateCommand", BindingFlags.Instance | BindingFlags.NonPublic);

                        object[] methodParams = new object[]
                        {
                            updateTranslator,
                            new Dictionary<long, object>()
                        };

                        dbCommands.Add((DbCommand)createCommandMethod.Invoke(o, methodParams));
                    }
                    else
                    {
                        throw new NotSupportedException("Unknown UpdateCommand Kind");
                    }
                }


                StringBuilder traceString = new StringBuilder();
                foreach (DbCommand command in dbCommands)
                {
                    traceString.AppendLine("=============== BEGIN COMMAND ===============");
                    traceString.AppendLine();

                    traceString.AppendLine(command.CommandText);
                    foreach (DbParameter param in command.Parameters)
                    {
                        traceString.AppendFormat("{0} = {1}", param.ParameterName, param.Value);
                        traceString.AppendLine();
                    }

                    traceString.AppendLine();
                    traceString.AppendLine("=============== END COMMAND ===============");
                }

                return traceString.ToString();
            }
        }
    }

    Wednesday, August 13, 2008 10:42 PM
  • Thanks all of you, g_yordanov's code works just fine.

     

    I need statements logging for testing and debugging only, so reflection with its security and performance issues is not a problem.

     

    Dziękuję za pozdrowienia i pozdrawiam :-).

     

     

    Friday, August 15, 2008 7:38 PM
  • Hi, your code helped me a lot! Thank you.

    I made a modification to you code to make the it ready to copy/paste it to the SQL Management Studio:

                    var traceString = new StringBuilder();
                    foreach (DbCommand command in dbCommands)
                    {
                        traceString.AppendLine("--=============== BEGIN COMMAND ===============");
                        traceString.AppendLine();
    
                        foreach (DbParameter param in command.Parameters)
                        {
                            traceString.AppendFormat("declare {0} {1} set {0} = '{2}'", param.ParameterName, GetSqlDbType(param), param.Value);
                            traceString.AppendLine();
                        }
                        traceString.AppendLine();
                        traceString.AppendLine(command.CommandText);
    
                        traceString.AppendLine();
                        traceString.AppendLine("go");
                        traceString.AppendLine();
                        traceString.AppendLine("--=============== END COMMAND ===============");
                    }
    
                    return traceString.ToString();

    Notice I Declare and Set the variables. Also notice I call Go at the end so that you can copy/paste subsequent commands without having to worry about the name of the variables.

    You need this method to convert from DbType to SqlDbType:

            private static string GetSqlDbType(IDataParameter param)
            {
                string result;
                var parm = new SqlParameter(); 
                try
                {
                    parm.DbType = param.DbType;
                    result = parm.SqlDbType.ToString();
                } catch (Exception) {
                    result = param.DbType.ToString();
                }
                return result;
            }
    Hope it helps! :)
    Friday, January 15, 2010 9:13 PM
  • Yes...that was extremely useful.  FYI, I am using this code on VS2010 targeting .NET 3.5 on a 32-bit machine.  I had to make one change to get it to work.    I was getting the following exception:

    System.ArgumentException: Object of type 'System.Collections.Generic.Dictionary`2[System.Int64,System.Object]' cannot be converted to type 'System.Collections.Generic.Dictionary`2[System.Int32,System.Object]'.

    I had to change the methodParams to use "new Dictionary<int, object>()" instead of "new Dictionary<long, object>()".

    Thanks again,

    David

    Thursday, April 15, 2010 12:30 PM
  • This works great if you are inserting or selecting.

    If you run a delete of a 1 to Many Child it fails.  It says:

     

           Exception has been thrown by the target of an invocation
           Inner Exception: Unable to insert or update an entity because the principal end of the 'MyFK_RelationshipHere' relationship is deleted.


    • Edited by Vaccanoll Tuesday, January 10, 2012 10:59 PM
    Tuesday, January 10, 2012 10:57 PM