Entity Framework 6.0 SQL Logging: Changing the Content and Formatting

Introduction

 

My previous article explained how to log SQL to a console application or another text writer using the DbContext.Database.Log function. This article explains how to change the formatting of the output.

 

Entity Framework uses the default log formatter when some action is set to the Database.Log property. The name of the default formatter class is:

 

System.Data.Entity.Infrastructure.Interception.DatabaseLogFormatter

We can apply our own formatting for logging SQL by creating a class that inherits from this class and override some of the methods. The most common methods to override are:

• LogCommand: It called to log a command that is about to be executed. This method can be overridden to change how the command is logged to the "WriteAction" method.
   
• LogResult: It is called to log the result of the executing command. Override this method to change how the results are logged to the WriteAction method.
   
• LogParameter: It is called by the LogCommand method to log each parameter. It can be overridden to change the way that parameters are logged to the WriteAction method.

Example

 

Suppose I want to log the context name in one line and a single-line SQL query before each command is sent to the database. For that, I must write my own formatter class that is derived from the DatabaseLogFormatter class and need to override the two methods LogCommand and LogResult. Override LogCommand to format and write the SQL log and override LogResult with nobody.

 

The code is something like:

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

namespace SQLLogging.Model
{

    public class MyFormatter : DatabaseLogFormatter
    {
        public MyFormatter(DbContext context, Action<string> writeAction)
            : base(context, writeAction)
        {
        }

        public override void LogCommand<TResult>(
            DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
        {
        Write(string.Format(
            "Context :'{0}'" + Environment.NewLine + "Executing command :'{1}'{2}",
             Context.GetType().Name,
             command.CommandText.Replace(Environment.NewLine, ""),
             Environment.NewLine));
        }
        public override void LogResult<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
        {
        }
    }
}

Once a new DatabaseLogFormatter class has been created it must be registered in Entity Framework. The DbConfiguration class has a method called SetDatabaseLogFormatter to set the custom formatter. To create a new class that is inherited from DbConfiguration in the same assembly and call SetDatabaseLogFormatter in the constructor of the newly created class and register the custom formatter.

 

The code is something like:

using System.Data.Entity;

namespace SQLLogging.Model
{
    public class MyDbConfiguration : DbConfiguration
    {
        public MyDbConfiguration()
        {
            SetDatabaseLogFormatter(
                (context, writeAction) => new MyFormatter(context, writeAction));
        }
    }
}

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.

 

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.

 

The code is something like:

using System.IO;

namespace SQLLogging.Model
{
    public class FileWriter
    {
        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.

 

The code is something like:

namespace SQLLogging.Model
{
    using System.Data.Entity;
    public partial class Model : DbContext
    {
        public Model()
            : base("name=EntityModel")
        {
        }

        public virtual DbSet<Employee> Employees { get; set; }
        public virtual DbSet<EmployeeDetail> EmployeeDetails { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            Database.Log = FileWriter.WriteSQL;
        }
    }
}

Test Code

static void Main(string[] args)
{
    using (SQLLogging.Model.Model context = new SQLLogging.Model.Model())
    {
        var data = context.Employees.Where(p => p.Code.StartsWith("p")).ToList();
        var data1 = (from e in context.Employees
                        join d in context.EmployeeDetails on e.Id equals d.Id
                        select new
                        {
                            Name = e.Name,
                            Phone = d.PhoneNo
                        }).ToList();
    }
    Console.ReadLine();
}

Output

 

 

Summary

 

Using the method described above we can format text for SQL logging (change the content as well as change the formatting).