CODEWITHSUNDEEP
sqlsql-serverloggingentity-framework-6
Madhur Maurya
Madhur Maurya

Reputation: 1070

Logging the SQL generated by LINQ to SQL in Entity Framework in .net

I am designing a testing framework that makes extensive use of SQL Sever Database. I am using Entity Framework 6 of .NET to felicitate it. I want to log the Underlying SQL query each time when I run a test case. I am using LINQ to SQL for querying Database.

I am having a hard time logging the SQL. LINQ to SQL generates some uncooked SQL which needs to be converted into SQL by filling in the parameters which I want to avoid.

Is there a better approach which will log all the SQL which I can directly feed to my SQL Server without doing any changes in Query ?

Upvotes: 4

Views: 4559

Answers (1)

Markus Safar
Markus Safar

Reputation: 6592

According to Entity Framework Logging:

The DbContext.Database.Log property can be set to a delegate for any method that takes a string. Most commonly it is used with any TextWriter by setting it to the “Write” method of that TextWriter. All SQL generated by the current context will be logged to that writer. For example, the following code will log SQL to the console:

using (var context = new BlogContext()) 
{ 
    context.Database.Log = Console.Write; 

    // Your code here... 
}

in the above way you should be able to log everything.

The following gets logged:

When the Log property is set all of the following will be logged:

  • SQL for all different kinds of commands. For example:
    • Queries, including normal LINQ queries, eSQL queries, and raw queries from methods such as SqlQuery
    • Inserts, updates, and deletes generated as part of SaveChanges
    • Relationship loading queries such as those generated by lazy loading
  • Parameters
  • Whether or not the command is being executed asynchronously
  • A timestamp indicating when the command started executing
  • Whether or not the command completed successfully, failed by throwing an exception, or, for async, was canceled
  • Some indication of the result value
  • The approximate amount of time it took to execute the command. Note that this is the time from sending the command to getting the result object back. It does not include time to read the results.

Looking at the example output above, each of the four commands logged are:

  • The query resulting from the call to context.Blogs.First
    • Notice that the ToString method of getting the SQL would not have worked for this query since “First” does not provide an IQueryable on which ToString could be called
  • The query resulting from the lazy-loading of blog.Posts
    • Notice the parameter details for the key value for which lazy loading is happening
    • Only properties of the parameter that are set to non-default values are logged. For example, the Size property is only shown if it is non-zero.
  • Two commands resulting from SaveChangesAsync; one for the update to change a post title, the other for an insert to add a new post
    • Notice the parameter details for the FK and Title properties
    • Notice that these commands are being executed asynchronously

Upvotes: 2

Related Questions