solvia.ch – blog

How to view the SQL Generated by Entity Framework

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

• How do I view the SQL generated by the Entity Framework?

Answers

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();  
}

Entity Framework 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 generated 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 generated 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 ....
   }   
}

more can also be found here: https://docs.microsoft.com/en-us/ef/ef6/fundamentals/logging-and-interception