Entity Framework - Command Logging

In Entity Framework 6.0, a new feature is introduced which is known as Logging SQL. While working with Entity Framework, it sends commands or an equivalent SQL query to the database to do a CRUD (Create, Read, Update, and Delete) operations.

This feature of the Entity Framework is to capture an equivalent SQL query generated by Entity Framework internally and provide it as output.

Before Entity Framework 6, whenever there was a need to trace database queries and command, the developer had no option but to use some third party tracing utipty or database tracing tool.

In Entity Framework 6, this new feature provides a simple way by logging all the operations performed by Entity Framework.

All the activities which are performed by Entity Framework are logged using DbContext.Database.Log.

Let’s take a look at the following code in which a new student is added to the database.

class Program {

   static void Main(string[] args) {

      using (var context = new UniContextEntities()) {

         context.Database.Log = Console.Write;

         // Create a new student and save it

         context.Students.Add(new Student {
            FirstMidName = "Salman", 
            LastName = "Khan", 
            EnrollmentDate = DateTime.Parse(DateTime.Today.ToString())
         });

         context.SaveChanges();
         Console.ReadKey();
      }
   }
}

When the above code is executed, you will receive the following output, which is actually the log of all the activities performed by EF in the above code.

Opened connection at 10/28/2015 6:27:35 PM +05:00
Started transaction at 10/28/2015 6:27:35 PM +05:00
INSERT [dbo].[Student]([LastName], [FirstMidName], [EnrollmentDate])
VALUES (@0, @1, @2)
SELECT [ID]
FROM [dbo].[Student]
WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()
-- @0:  Khan  (Type = String, Size = -1)
-- @1:  Salman  (Type = String, Size = -1)
-- @2:  10/28/2015 12:00:00 AM  (Type = DateTime)
-- Executing at 10/28/2015 6:27:35 PM +05:00
-- Completed in 5 ms with result: SqlDataReader
Committed transaction at 10/28/2015 6:27:35 PM +05:00
Closed connection at 10/28/2015 6:27:35 PM +05:00

When the Log property is set the following activities are logged −

SQL for all different kinds of commands e.g. Queries, including inserts, updates, and deletes generated as part of SaveChanges

Parameters

Whether or not the command is being executed asynchronously

A timestamp indicating when the command started executing

The command completed successfully or failed

Some indication of the result value

The approximate amount of time it took to execute the command

Logging to Other Place

If you already have some logging framework and it defines a logging method then you can also log it to other place.

Let’s take a look at the following example in which we have another class MyLogger.

class Program {

   static void Main(string[] args) {

      using (var context = new UniContextEntities()) {

         context.Database.Log = s ⇒ MyLogger.Log("EFLoggingDemo", s);

         // Create a new student and save it

         context.Students.Add(new Student {
            FirstMidName = "Salman", 
            LastName = "Khan", 
            EnrollmentDate = DateTime.Parse(DateTime.Today.ToString())
         });

         context.SaveChanges();
         Console.ReadKey();
      }
   }
}

pubpc class MyLogger {

   pubpc static void Log(string apppcation, string message) {
      Console.WriteLine("Apppcation: {0}, EF Message: {1} ",apppcation, message);
   }
}

We recommend that you execute the above example in a step-by-step manner for better understanding.