Part 4 - Using stored procedures with LINQ to SQL

Suggested Videos
Part 1 - LINQ to SQL
Part 2 - Insert Update Delete using LINQ to SQL
Part 3 - How to view LINQ to SQL generated SQL queries



In this video, we will discuss retrieving data using stored procedures with LINQ to SQL. This is continuation to Part 3. Please watch Part 3 before proceeding.



Here are the steps for using stored procedure with LINQ to SQL
Step 1 : Create the stored procedure

Create procedure GetEmployees

as

Begin

     Select ID, FirstName, LastName, Gender, Salary, DepartmentId

     from Employees

End

Step 2 : In Visual Studio, click on the "View" menu and select "Server Explorer". Right click on "Data Connection" and  select "Refresh". Expand "Stored Procedures" folder. Here you should find "GetEmployees" stored procedure.


Step 3 : Drag "GetEmployees" stored procedure from the Server Explorer window and drop it on the LINQ to SQL class designer. This will automatically create a method with the same name as the stored procedure. Tables and Views are converted to classes. Stored procedures and functions are converted to methods.


Step 4 : Finally in the code-behind file, call GetEmployees() method on the DataContext class instance.

SampleDataContext dbContext = new SampleDataContext();

GridView1.DataSource = dbContext.GetEmployees();

GridView1.DataBind();

At this point, run SQL profiler and start a new trace. Run the application and click Get Data button. In the SQL profiler trace notice that the stored procedure is called as expected.

Modifying the Return Type of GetEmployees() method
Hover the mouse over GetEmployees() method and notice the Return Type. This method returns ISingleResult<GetEmployeesResult>


There are 2 things that we need to understand here
1. GetEmployeesResult is an auto-generated type and follows the
"[StoredProcedureName]Result" naming pattern.
2. ISingleResult implies that we are getting back a single result set and not multiple result sets. 

Can we change the Return Type
Yes, on the LINQ to SQL designer, right click on the GetEmployees() method and select properties. In the properties window set the return type to the type you are expecting. In this example, I have set it to Employee. So the return type now is ISingleResult<Employee>.

Another way to do this is, when dragging and dropping the stored procedure on the designer surface, make sure to drop it on the Employee entity. Doing so will also set the return type to ISingleResult<Employee>.