Click here to Skip to main content
15,743,713 members
Articles / Programming Languages / C# 5.0
Posted 26 Jun 2015


6 bookmarked

Executing Stored Procedure using Entity Framework DataContext

Rate me:
Please Sign up or sign in to vote.
3.75/5 (4 votes)
26 Jun 2015CPOL
Executing Stored Procedure that returns resultset using Entity Framework DataContext


Executing Stored Procedure that accepts parameters and returns resultset using Entity Framework DataContext with code first or database first approach.


Most of the times, we need to execute stored procedures than get the result set even though we decide to use entity framework. It's pretty straight forward to leverage ADO.NET and DataSet to retrieve the result returned from the Stored Procedure. It's not that common to do this using Entity Framework DataContext.

Using the Code

Below are the steps and key points:

//create parameters to pass to the stored procedure  
//First input Parameter
var param1 = new SqlParameter { ParameterName = "@paramName1", 
SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = 1 }; 

//Second input parameter
var param2 = new SqlParameter { ParameterName = "@paramName2", 
SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = "Test Input" };                 

//third out parameter
var param3 = new SqlParameter { ParameterName = "@paramName3", 
SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Output, Size = 255 }; 

//compose the SQL
var SQLString = "EXEC [dbo].[name of the Stored Proc] @paramName1, @paramName2, @paramName3"; 

//Execute the stored procedure 
var employees= DataContext.Employee.SqlQuery(SQLString, param1, param2, param3); 

//or you can execute the SP using below 
//var employees = DataContext.Database.SqlQuery<Employees>
(SQLString, param1, param2, param3)

Points of Interest

  1. In the above sample, DataContext would be the name of your DataContext Instance variable
  2. Employee would be name entity (POCO class) mapping to your database
  3. If the resultset returned by the SP would be mapped to the IEnumerable collection of Entity - The above sample would return IEnumerable<Employee>
  4. We can use <DataContext>.DataBase.SqlQuery method or <DataContext>.<EntityName>.SqlQuery
  5. Your out param declared would have value returned by SP
  6. The connect sting should have MARS = true - MultipleActiveResultSets=true


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
India India
Hands on Architect with 15 + years of experience in Microsoft technologies and worked for various clients across the globe. Has Insightful experience in Architecture, Design and Development of projects based on Microsoft Technologies. Worked with multiple fortune 500 customers. Possesses good knowledge on design patterns and best practices in .NET technologies.

Comments and Discussions

-- There are no messages in this forum --