Entity Framework – Show generated SQL

How to view the SQL Generated by Entity Framework

Entity Framework follows the following three steps.

  • Translate C# code into SQL statements
  • Execute SQL on a target database
  • Return values back to C# objects.

Now the question is how the generated SQL statements look like and how we can view the SQL executed on the database.

StackOverflow Related Questions
Answer

The simple way of viewing the SQL generated is to use reflection to create an ObjectQuery object and then call the ToTraceString() method to actually store the query results.

using (var context = new EntityContext())
{
    var query = context.Customers.Where(c => c.Id == 1); 
    var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();  
}
EF SQL Logging

Entity Framework team added support for interception and logging of generated SQL in EF6. The DbContext.Database.Log property can be set to a delegate for any method that takes a string.

Log SQL to the Console.
using (var context = new EntityContext())
{
    context.Database.Log = Console.Write; 
    // query here ....  
}
Log SQL to Visual Studio Output panel.
using (var context = new EntityContext())
{
    context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); 
    // query here ....  
}
Log SQL to an External File
using (var context = new EntityContext())
{
    using (var sqlLogFile = new StreamWriter("C:\\temp\\LogFile.txt"))
    {          
         context.Database.Log = sqlLogFile.Write;
         // query here ....
   }   
}