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:
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:
  1. using System;
  2. using System.Data.Common;
  3. using System.Data.Entity;
  4. using System.Data.Entity.Infrastructure.Interception;
  6. namespace SQLLogging.Model
  7. {
  9. public class MyFormatter : DatabaseLogFormatter
  10. {
  11. public MyFormatter(DbContext context, Action<string> writeAction)
  12. : base(context, writeAction)
  13. {
  14. }
  16. public override void LogCommand<TResult>(
  17. DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
  18. {
  19. Write(string.Format(
  20. "Context :'{0}'" + Environment.NewLine + "Executing command :'{1}'{2}",
  21. Context.GetType().Name,
  22. command.CommandText.Replace(Environment.NewLine, ""),
  23. Environment.NewLine));
  24. }
  25. public override void LogResult<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
  26. {
  27. }
  28. }
  29. }
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:
  1. using System.Data.Entity;
  3. namespace SQLLogging.Model
  4. {
  5. public class MyDbConfiguration : DbConfiguration
  6. {
  7. public MyDbConfiguration()
  8. {
  9. SetDatabaseLogFormatter(
  10. (context, writeAction) => new MyFormatter(context, writeAction));
  11. }
  12. }
  13. }
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:
  1. using System.IO;
  3. namespace SQLLogging.Model
  4. {
  5. public class FileWriter
  6. {
  7. public static void WriteSQL(string data)
  8. {
  9. string path = @"c:\SQLtrace.txt";
  10. File.AppendAllText(path, data);
  11. }
  12. }
  13. }
Now assign this custom function to the Database.Log property as a delegate.
The code is something like:
  1. namespace SQLLogging.Model
  2. {
  3. using System.Data.Entity;
  4. public partial class Model : DbContext
  5. {
  6. public Model()
  7. : base("name=EntityModel")
  8. {
  9. }
  11. public virtual DbSet<Employee> Employees { get; set; }
  12. public virtual DbSet<EmployeeDetail> EmployeeDetails { get; set; }
  14. protected override void OnModelCreating(DbModelBuilder modelBuilder)
  15. {
  16. Database.Log = FileWriter.WriteSQL;
  17. }
  18. }
  19. }
Test Code
  1. static void Main(string[] args)
  2. {
  3. using (SQLLogging.Model.Model context = new SQLLogging.Model.Model())
  4. {
  5. var data = context.Employees.Where(p => p.Code.StartsWith("p")).ToList();
  6. var data1 = (from e in context.Employees
  7. join d in context.EmployeeDetails on e.Id equals d.Id
  8. select new
  9. {
  10. Name = e.Name,
  11. Phone = d.PhoneNo
  12. }).ToList();
  13. }
  14. Console.ReadLine();
  15. }
Output
SQL logging

Summary

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