Logging and Intercepting Database Operations With Entity Framework 6.0

Introduction

Entity Framework 6.0 introduced the 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. This feature of the Entity Framework is to capture an equivalent SQL query generated by Entity Framework internally and provide it as output.

How to enable SQL logging

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

Syntax:

public Entities() : base("name=Entities")
{
Database.Log = delegate for method which accepts string as parameter;
}

Example:

Suppose I have two database entities, EmployeeMaster and DepartmentMaster, and I want to log, the SQL generated by the Entity Framework.

database entity

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 Entities() : base("name=Entities")
{
Database.Log = Console.WriteLine;
}

Test Code:

static void Main(string[] args)
{
using (Entities context = new Entities())
{
var data = context.EmployeeMasters.Where(p => p.Code.StartsWith("p")).ToList();
var data1 = (from e in context.EmployeeMasters
join d in context.DepartmentMasters on e.DepartmentId equals d.DepartmentId
select new
{
Name = e.Name,
Department = d.Name
}).ToList();
}
Console.ReadLine();
}

Output:

program output

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 CodeFirstTest
{
public class WriteFile
{
public static void WriteSQL(string data)
{
string path = @"c:\SQLtrace.txt";
File.AppendAllText(path, data);
}
}
}

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

public Entities() : base("name=Entities")
{
Database.Log = s=> WriteFile.WriteSQL(s);
}
// Other way to pass function
public partial class Entities
{
public Entities(): base("name=Entities")
{
Database.Log = WriteFile.WriteSQL;
}
}

Output:

SQL Trace

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.