Logging Database Commands in Entity Framework

Back to: Entity Framework Tutorials For Begineers and Professionals

Logging Database Commands in Entity Framework

In this article, I am going to discuss Logging Database Commands in Entity Framework Database First Approach with Examples. Please read our previous article where we discussed Change Tracking in Entity Framework Database First Approach. We are going to work with the same example that we created in our Introduction to Entity Framework Database First Approach article. Please read our Introduction to Entity Framework Database First Approach article before proceeding to this article.

Logging Database Commands in Entity Framework

Let us understand, how to log commands and queries which is generated and sent to the database by Entity Framework 6. Prior to Entity Framework 6, we used the database tracing tool or third-party tracing tools like SQL Profiler to trace database queries and commands sent by Entity Framework. Now, Entity Framework 6 provides the DbContext.Database.Log property to log the SQL generated by the DbContext object.

1. public Action<string> Log { get; set; }: Set this property to log the SQL generated by the DbContext object to the given delegate. For example, to log into the console set this property to System.Console.Write(System.String).

The Log property type is Action<string>, so you can attach a delegate method with takes a string parameter and return void. The following example shows how to use the DbContext.Database.Log property to log the SQL generated by the DbContext object in the Console window. In the below example, we are setting the Log Property with Console.Write and this is accepted because the Write method is a method which is taking a string input parameter and its return type is void.

using System;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
            {
                //Log the Generated SQL in the Console
                context.Database.Log = Console.Write;

                //Fetch the Student whose Id is 1
                var student = context.Students.Find(1);

                //Update the First Name
                student.FirstName = "FirstName Updated";

                //Call the SaveChanges method to save the updated information in the database
                context.SaveChanges();
            }

            Console.Read();
        }
    }
}

Output:

As you can see in the output window, it logs all the database activities performed by Entity Framework. That is opening and closing the connection, creating the transaction, and committing the transaction. Executing the SQL Statement within the transaction and the completion time.

You can also attach a method of your own custom class to the Log property. That method that you want to attach should follow the Action<string> delegate signature. That is the method should accept a string input parameter and the return type of the method must be void. For a better understanding, please have a look at the below example. Here, we are using our custom class method to log the Database activities performed by the Entity Framework. The following example code is self-explained, so please go through the comment lines.

using System;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
            {
                //Log the Generated SQL in the custom DBLogger.DBLog Method
                context.Database.Log = DBLogger.DBLog;

                //Fetch the Student whose Id is 1
                var student = context.Students.Find(1);

                //Update the First Name
                student.FirstName = "FirstName Updated";

                //Call the SaveChanges method to save the updated information in the database
                context.SaveChanges();
            }

            Console.Read();
        }
    }

    public class DBLogger
    {
        //Method taking one string parameter and returning void
        public static void DBLog(string message)
        {
            //Here, you can log the message in a text file, in the database, or you can send the message to email
            Console.WriteLine($"Entity Framework Database Activities: {message} ");
        }
    }
}

Output:

Intercept Database Command in Entity Framework

Now, we will understand how to intercept database commands executed by the DbContext object in Entity Framework 6 database-first approach.

Entity Framework 6 provides the ability to intercept the context object by implementing the IDbCommandInterceptor interface. The IDbCommandInterceptor interface includes methods that intercept an instance of a DbContext object and allow us to execute our own custom logic before or after a context object executes database commands or queries. The DbContext Object executes commands and queries using ADO.NET methods such as ExecuteNonQuery, ExecuteScalar, and ExecuteReader, which we can intercept by implementing methods such as NonQueryExecuted and NonQueryExecuting, etc.

To intercept an instance of DbContext Object, first, create a custom class and implement IDbCommandInterceptor as follows.

using System;
using System.Data.Entity.Infrastructure.Interception;

namespace DBFirstApproach
{
    public class EntityFrameworkCommandInterceptor : IDbCommandInterceptor
    {
        public void NonQueryExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            string commandInfo = $" IsAsync: {interceptionContext.IsAsync}, Command Text: {command.CommandText}";
            LogInfo("NonQueryExecuted", commandInfo);
        }

        public void NonQueryExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            string commandInfo = $" IsAsync: {interceptionContext.IsAsync}, Command Text: {command.CommandText}";
            LogInfo("NonQueryExecuting", commandInfo);
        }

        public void ReaderExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
        {
            string commandInfo = $" IsAsync: {interceptionContext.IsAsync}, Command Text: {command.CommandText}";
            LogInfo("ReaderExecuted", commandInfo);
        }

        public void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
        {
            string commandInfo = $" IsAsync: {interceptionContext.IsAsync}, Command Text: {command.CommandText}";
            LogInfo("ReaderExecuting", commandInfo);
        }

        public void ScalarExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            string commandInfo = $" IsAsync: {interceptionContext.IsAsync}, Command Text: {command.CommandText}";
            LogInfo("ScalarExecuted", commandInfo);
        }

        public void ScalarExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            string commandInfo = $" IsAsync: {interceptionContext.IsAsync}, Command Text: {command.CommandText}";
            LogInfo("ScalarExecuting", commandInfo);
        }

        private void LogInfo(string command, string commandInfo)
        {
            Console.WriteLine($"\nIntercepted on: {command} :- {commandInfo} ");
        }
    }
}

As you can see in the above class, the IDbCommandInterceptor interface provides six methods for implementation. Here, we have implemented all six methods with our own custom logic. You can also write logic as per your requirements. Next, we need to configure the interceptor in the config file. Let us configure the interceptor in app.config file, as follows:

<entityFramework>
    <interceptors>
        <interceptor type="DBFirstApproach.EntityFrameworkCommandInterceptor, DBFirstApproach">
        </interceptor>
    </interceptors>
</entityFramework>

With the above changes in place, now we can log commands whenever an instance of DbContext class executes a command or query. For a better understanding, please have a look at the following example.

using System;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
            {
                //Fetch the Student whose Id is 1
                var student = context.Students.Find(1);
            }

            Console.Read();
        }
    }
}

Output:

As you can see in the above output, it is now using ReaderExecuting and ReaderExecuted methods. Now, modify the Main method of the Program class as follows:

using System;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            Student student = new Student() { FirstName = "James", LastName = "Smith", StandardId = 1 };
            using (EF_Demo_DBEntities context = new EF_Demo_DBEntities())
            {
                context.Students.Add(student);
                context.SaveChanges();
            }
            
            Console.Read();
        }
    }
}

Output:

In the next article, I am going to start Code-Based Configuration in Entity Framework Database First Approach with Examples. Here, in this article, I try to explain Logging Database Commands in Entity Framework Database First Approach with Examples. I hope you enjoy this Logging Database Commands in Entity Framework Database First Approach article.