Ways to Determine Generated SQL Statements when running a LINQ to SQL Statement

Introduction

When writing LINQ TO SQL statements, it is always useful to see the generated SQL queries while running the LINQ statements. In this article, we will explore the different ways by which we can see the generated SQL Statements.

Initial Data Set up

Let us try the different approaches to see the SQL statements using the following scenario.

For this demo, I have created a new table in the database by name 'Employees' having the following columns and then populated it with some dummy data.

Employees
`EmployeeID`
`EmployeeName`
`Country`
`Title`
`HireDate`

Next, I have added a LINQ to SQL class file in my solution and dragged the Employees table on to the .dbml file. Now, I want to write a LINQ Statement that will give me names of all the employees whose 'Title' is 'Consultant' and want to see the actual SQL Query generated as a result of executing the LINQ statement.

Option 1 - Log Property of Data Context Class

The simplest way we can see the generated SQL is by using the log property of Data Context which could then be displayed to a console window or an Output Window.

EmployeesDataContext dc = new EmployeesDataContext();
            dc.Log = Console.Out;
            var consultants = from employee in dc.Employees
                              where employee.Title == "Consultant"
                              select employee.EmployeeName;

            foreach (var empname in consultants)
            {
                Console.WriteLine(empname);
            }
            Console.ReadLine();

And here's the output as a result of running the above code:

Option 2 - Using GetCommand() Method of the Data Context Class

Another way to see the generated SQL is to use the GetCommand method of the DataContext class. Here the GetCommand() method returns an instance of DCcommand class and then we use the CommandText property to generate the SQL statement.

EmployeesDataContext dc = new EmployeesDataContext();
            var consultants = from employee in dc.Employees
                              where employee.Title == "Consultant"
                              select employee.EmployeeName;

            System.Data.Common.DbCommand cmd = dc.GetCommand(consultants);
            Console.WriteLine(cmd.CommandText);
            Console.WriteLine();
            Console.WriteLine("Employees with Title as Consultant :");
            foreach (var empname in consultants)
            {
                Console.WriteLine(empname);
            }
            Console.ReadLine();

And here's the output as a result of running the above code:

Option 3 - Using SQL Server Query Visualizer

Another way to see the generated SQL is using the 'SQL Server Query Visualizer'. Please note that the visualizer is not built-in to VS 2008, instead it has to be downloaded to use. Once we download this visualizer, we can simply hover over the 'consultants' variable after the query expression has been assigned.

Also as seen in the above figure, we have a magnifying glass when we hover over the expression. If we click that, it will launch the 'SQL Server Query Visualizer' to inspect the SQL statement as shown in the figure below: