Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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
     Select ID, FirstName, LastName, Gender, Salary, DepartmentId
     from Employees

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.
Using stored procedures with LINQ to SQL

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.
select stored procedure in linq to sql

Step 4 : Finally in the code-behind file, call GetEmployees() method on the DataContext class instance.
SampleDataContext dbContext = new SampleDataContext();
GridView1.DataSource = dbContext.GetEmployees();

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>
return type of select stored procedure in linq to sql

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>.
changing return type of select stored procedure in linq to sql

LINQ to SQL Tutorial

1 comment:

  1. Hi, Venkat. I designed a Login table with columns id and password.
    also created a stored procedure to check if the user entered Id and password are existing or not like check_login(@id,@password) while working with linq to sql classes. How to verify this?


It would be great if you can help share these free resources