Entity Framework: Check SQL Queries and Commands

Retrieve SQL Query generated from Linq to Entities

You can see the generated query for any query which was not yet executed (it is still an IQueryable), by calling the "ToString()" method:

using(var dbContext = new NetContext())
{
    var videoDetails = dbContext.VideoDetails.Where(v => !v.Document.IsDeleted && !v.Document.MarkedForDeletion).Select(v => new VideoDto
    {
        Id = v.DocumentId,
        FileSize = v.Document.FileSize,
        FullPath = v.Document.RelativePath,
        Title = v.Title,
        Description = v.Description,
        MimeType = v.Document.MimeType,
        Duration = v.Duration,
        Date = v.Document.LastWriteTime
    });

    string videoQuery = videoDetails.ToString();
}

This will give you the query which would be executed if you would call videoDetails.ToList() or use videoDetails in a foreach statement. Note: the string contains \r\n instead new lines, so you need to replace the special characters if you want to copy/paste the query and run it in SQL.

E.g.

"SELECT \r\n    1 AS [C1], \r\n    [Extent1].[DocumentId] AS [DocumentId], \r\n    [Extent2].[FileSize] AS [FileSize], \r\n    [Extent2].[RelativePath] AS [RelativePath], \r\n    [Extent1].[Title] AS [Title], \r\n    [Extent1].[Description] AS [Description], \r\n    [Extent2].[MimeType] AS [MimeType], \r\n    [Extent1].[Duration] AS [Duration], \r\n    [Extent2].[LastWriteTime] AS [LastWriteTime]\r\n    FROM  [dbo].[VideoDetails] AS [Extent1]\r\n    INNER JOIN [dbo].[Documents] AS [Extent2] ON [Extent1].[DocumentId] = [Extent2].[Id]\r\n    WHERE ([Extent2].[IsDeleted] <> 1) AND ([Extent2].[MarkedForDeletion] <> 1)"

Visual Studio Trick: 

If you are in debugging mode, you can get the actual command, without the \r\n characters, by hovering the videoQuery variable, and pressing the small lens icon, like in the image below

Advantages:

• Easy to use and provides the query for a specific context
• Can be used during debugging, or to log the queries in the context they were run

Disadvantages:

• Limited to SELECT operations
• Cannot return queries generated by .Count, .FirstOrDefault, .Single etc.

Intercept Entity Framework traffic between your application and the database

In EF 6+ you can intercept all the traffic between your application and the database. To do this, you need to create/enable an interceptor.

Entity Framework 6.1

Add the highlighted code to the application configuration file (Web.config or App.config):

 <entityFramework>
    <providers>
       <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
    <interceptors>
       <interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework">
          <parameters>
             <parameter value="C:\Logs\EF.txt"/>
          </parameters>
       </interceptor>
    </interceptors>
 </entityFramework>

This will log all the commands and queries sent by EF to your SQL server, regardless if they are Read or Write operations. Can be enabled without recompiling the application and can be disabled right after we gathered the information needed to investigate further. Enabling this will also help us see exactly what requests to the database were done during a specific operation (we can notice, for example, if we are doing too many requests because of lazy loading relations in a foreach).

Disadvantage: there is no context information. The output is the way it is, in the order of execution, but if multiple users are using your application at the same time, it won't be easy to know which SQL Queries are related to which operations/sessions/users.

Entity Framework 6.0

EF 6 supports similar functionality, but you need to build your own interceptor (by implementing ​IDbCommandInterceptor​ interface).
You may also use dbContext.Database.Log, to assign a delegate which will write all the queries.

The disadvantage compared to EF 6.1 version is that you need code changes and you need to implement your own interceptor. However, you may want to write your own interceptor anyway, such that you can use the desired logging framework and turn on/off logging as desired. If the interceptor is built in a separate assembly, it can be enabled in Web.config without recompiling the full application.

Monitor Database server using SQL Server Profiler

Another option to trace out SQL Queries is to monitor the queries directly on the database server. This tool can be started using Microsoft SQL Server Management Studio, by accessing Tools -> SQL Server Profiler:

When the tool is started, you need to log in to the server you want to monitor, and click "Run". Leave the monitor running until you reproduced the steps you want to get Query information about, then pause or stop it and search for your queries. Unfortunately, there is no easy way to trace down the queries, but you can use filters like LoginName, ApplicationName or even TextData (use CTRL+F to search for some text you are sure it will be there).

The advantage of this way of monitoring is that you don't need to alter anything related to the application code or configuration. However, you need direct access to the database and proper access rights. This method is limited to MSSQL Server, while the interceptor and the ToString method will work with other databases as well. 

Which method is better?

Well, use the one that fits your needs. The important thing is to know what are the available options and implement as much as your project requires. Do you know other options? Feel free to let me know in the comments below.

Comments