Entity Framework 6.0 Logging and database Operations Interception

Introduction

Entity Framework 6.0 introduced a new feature called "Logging SQL". While working with Entity Framework, it sends commands or an equivalent SQL query to the database to do a CRUD operation and this command can be intercepted by application code of Entity Framework.

How to enable the SQL logging

The Database Log property of DbContext can be set to a delegate of any method that accepts a string as the parameter. Using this method, all SQL generated by the current context will be logged.

Syntax:

            public DbContextEntities() : base("name=EntitiesConnections")

        {

            Database.Log = delegate for method which accepts string as parameter;

        }

 

Example:

For instance, if we have two database entities, Employee and Department, and we want to log, the SQL generated by the Entity Framework.

The DbContext.DataBase.Log property accepts a delegate for the method that has a string parameter. The Console.WriteLine function has the same type of delegate, so we can assign this function to the Database.Log property of DbContext.

         public IntegraContext() : base("csharpnaija")

        {

            Database.Log = Console.WriteLine;

 

        }

 Code:

public ActionResult Index()

        {

            using (DbContextConnection context = new DbContextConnection())

            {

                var data = context.Employees.Where(p => p.Name.StartsWith("p")).ToList();

                var data1 = (from e in context.Employees

                             join d in context.Departments on e.DepartmentId equals d.DeparmentId

                             select new

                             {

                                 Name = e.Name,

                                 Department = d.Name

                             }).ToList();

            }

            Console.ReadLine();

            return View();

        }

Writing SQL Log into text file

We can also pass the SQL query to any other function. Only the condition is a function that must have the same delegate as the DataBase.Log property accepts.

For example suppose I want to write all SQL into a text file, so I create a custom function that accepts a string and writes it into the text file.

Custom function definition

 

namespace CsharpnaijaSqlLoggingTutorial.Models

{

    public class WriteToFile

    {

        public static void LogSQL(string sql)

        {

            var filePath = @"c:\SQLLog.txt";

            File.AppendAllText(filePath, sql);

        }

    }

}

 

Now assign this custom function to the Database.Log property as a delegate.

 

namespace CsharpnaijaSqlLoggingTutorial.Models

{

    public class DbContextConnection : DbContext

    {

        public DbContextConnection() : base("DefaultConnection")

        {

            Database.Log = s => WriteToFile.LogSQL(s);

        }

        public DbSet<Employee> Employees { get; set; }

        public DbSet<Department> Departments { get; set; }

    }

}

What did it Log?

The following things are logged when the Database.Log property is set:

• All SQL Commands
  
  
  
• Equivalent SQL query for a normal LINQ query, eSQL and raw query (query that has a from clause).
• Insert, update and delete command generated as a part of the SaveChanges method of context.
• Relationship loading queries
  
  
• All the Query Parameters
• Date and Time when the command is started to execute
• The approximate amount of time it takes to execute the command. This is the time required to send the command and get results back. It does not include time to read the result.

Summary

Using this functionality we can log the SQL commands that are generated and executed by Entity Framework. This feature can be used with only Entity Framework version 6.0 and above.