Executing Stored Procedure using Entity Framework DataContext

Introduction

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

Background

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