How To View LINQ To SQL Generated SQL Queries And Using Stored Procedures With LINQ To SQL

In this article, we will see two parts

• How to view LINQ to SQL generated SQL queries and
• How to use stored procedures with LINQ to SQL

This is the continuation of my previous article: insert, update and delete, using LINQ to SQL. We will use the same example --  download the sample from here

Download complete code of this article from https//code.msdn.microsoft.com/How-to-View-LINQ-To-SQL-a20d42c4

Building the Sample

For debugging purposes, it is necessary to view generated SQL statements. There are several ways we can do this. One of the easiest ways is to write the generated SQL queries to ASP.NET page or a console Window.

Thus, let’s flip to Visual Studio, which we will be using. The LINQ query, which we had here, will be translated into T-SQL by the underlying LINQ to SQL provider.

What we want to do is to write that generated SQL statement to ASP.NET Web page. To achieve this, we will use Logproperty of datacontextobject.



Let’s run the demo, see the output, and click on GetData button. 


Thus, there is employee data along with the generated SQL statement. Thus, this SQL statement will be executed in the underlying database.

Now, if it is a console Application and if you want to log the SQL statements to the console Window, then you will set the log property as given below.



Thus, one option is used as Log property of DataContext object, where we can also use ToString method to achieve the same thing, so let’s see it, as shown below. 



Here, we had written a LINQ query, created a variable and passed that LINQ query, which is nothing but using Tostring to display the query. Now, let’s check the output, as shown below. 



Notice that the generated SQL is again printed on the page.We are using ToString method to print the statement.

We have dbcontext got getcommand method, so let’s see how to use that method now.

You must be able to see dbconext.getcommand is returning IEnumenrable, so we are passing linqquery in the parameter. You must be able to notice that the linqquary is returning IEnumerable.



Now, lets run the app and see the output, as shown below. 

• The generated SQL is on the Webpage, so we have three options we can use
• Log Property of DBContext object OR
• We can use Commnad method of the datacontext object OR
• We can use ToString method
  
  There is another option, where we can use SQL Server Profiler to see the T-SQL Staments. Therefore, launch SQL Server Profiler. Run the form.
  
  
  

This is SQL that is generated.

This part of the article is where we will see how to retrieve the data, using stored procedures with LINQ to SQL. Again we will be using  the same example to demonstrate a stored procedure. First, we will create a simple procedure to select employees.

Let’s remove the log Trace from the code and run the app and run the Profiler. 



In SQL Server Profiler, we are getting T-SQL statements properly, which is executed in the underlying database.



Instead of this, select adhoc statement, where we want to select a stored procedure statement. Thus, let’s look at the steps involved.

First, we will create a simple stored procedure for employees, as shown below.



Refresh the stored procedure in the Server explorer Window.



Now, open Sample.dbml file in it, where we had dragged and dropped the tables. When you drag any tables to the file equivalent, class will generate. Similarly, when you drag and drop stored procedure, C# generates this method.



Notice, I have sp_GetEmployee method automatically created. Look at the name of the method, as it matches with the name of the stored procedure.

All that is left now is to invoke this method to our code back-end file.



Now, let’s run the Application and run the trace in SQL Server Profiler. Click on Get Data button, the data will be populated on the page.



Go to SQL Server Profiler and stop the trace, where you will see T-SQL statement of our stored procedure, as shown below.



Notice that GetEmployees stored procedure is executed.

Let’s inspect C# method, which is generated by right clicking on the method, and go to the definition. 



If you look at the return type of this method, the method is returning ISingleResult of GetEmployeeResult.

There are two things which we need to understand here. First of all, ISingleResult does not mean that it’s going to result in a single employee, which means it is going to return a single result set with the list of all the employees.

When you see sp_GetEmployeeResult, we do not define the class, so when this class comes from it, this is auto generated. Let's look at the naming convention, it follows sp_GetEmployee is our stored procedure name to the Result word, which is appended.

Is it possible to modify the return type of this method possible? There are two ways to do it. If you look at the sp_GetEmployee method return type, right click on the method -> Properties.



The return type is an auto generated type. Instead of it, select an employee type and click Yes, which will ask you to save the changes. Now, run the application to make sure everything is working properly.

Now, for the sp-employee method ->go to the definition.



Look at that return type, which is an employee, so ISingleResult of an employee is one way.

Conclusion

This was about how to view LINQ to SQL generated SQL queries and using stored procedures with LINQ to SQL. In the next article, we will see Insert, Update, and Delete, using stored procedure in LINQ to SQL.