Prerequisites
The prerequisite for running these examples are the following sample tables with test data and a Stored Procedure. The following script help to generate the table with test data and a Stored Procedure.
--First we create Department Master and Employee Master tables.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DepartmentMaster]') AND type in (N'U'))
DROP TABLE [dbo].[DepartmentMaster]
GO
CREATE TABLE [dbo].[DepartmentMaster](
[DepartmentId] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](50) NULL,
[Status] [tinyint] NULL,
CONSTRAINT [PK_DepartmentMaster] PRIMARY KEY CLUSTERED
(
[DepartmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeMaster]') AND type in(N'U'))
DROP TABLE [dbo].[EmployeeMaster]
GO
CREATE TABLE [dbo].[EmployeeMaster](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](100) NULL,
[DepartmentID] [int] NULL,
[Status] [tinyint] NULL,
CONSTRAINT [PK_EmployeeMaster] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--Stored Procedure that return Employee Details i.e Employee ID, Employee Name and Department Name
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEmployeeData]') AND type in(N'P', N'PC'))
DROP PROCEDURE [dbo].[GetEmployeeData]
GO
CREATE PROCEDURE [dbo].[GetEmployeeData]
AS
BEGIN
SELECT EmployeeID,EmployeeName,DepartmentName FROM EmployeeMaster E
INNER JOIN DepartmentMaster D ON E.DepartmentID = D.DepartmentId
END
--Inserting some Dummy Data.
SET IDENTITY_INSERT [dbo].[DepartmentMaster] ON
INSERT [dbo].[DepartmentMaster] ([DepartmentId], [DepartmentName], [Status]) VALUES (1, N'Maths', 0)
INSERT [dbo].[DepartmentMaster] ([DepartmentId], [DepartmentName], [Status]) VALUES (2, N'English', 0)
INSERT [dbo].[DepartmentMaster] ([DepartmentId], [DepartmentName], [Status]) VALUES (3, N'Physics', 0)
SET IDENTITY_INSERT [dbo].[DepartmentMaster] OFF
SET IDENTITY_INSERT [dbo].[EmployeeMaster] ON
INSERT [dbo].[EmployeeMaster] ([EmployeeID], [EmployeeName], [DepartmentID], [Status]) VALUES (1, N'Tejas',1, 0)
INSERT [dbo].[EmployeeMaster] ([EmployeeID], [EmployeeName], [DepartmentID], [Status]) VALUES (2,N'Rakesh', 1, 0)
INSERT [dbo].[EmployeeMaster] ([EmployeeID], [EmployeeName], [DepartmentID], [Status]) VALUES (3,N'Jignesh', 2, 0)
INSERT [dbo].[EmployeeMaster] ([EmployeeID], [EmployeeName], [DepartmentID], [Status]) VALUES (4, N'Kunal',3, 0)
SET IDENTITY_INSERT [dbo].[EmployeeMaster] OFF
1. Stored Procedure as Entity Function
The Entity Framework has the capability of importing a Stored Procedure as a function. We can also map the result of the function back to any entity type or complex type.
The following is the procedure to import and use a Stored Procedure in Entity Framework.
Step 1: Import Stored Procedure 
When we finish this process, the selected Stored Procedure is added to the model browser under the Stored Procedure Node.
Step 2: Right-click Stored Procedure and select "Add Function Import".
Step 3: Here, we can map a returned object of our Stored Procedure. The return type may be a scalar value or a collection of Model Entities or a collection of Complex (Custom) Entity. From this screen we can create a Complex Entity as well.
Now, we can call the Stored Procedure as an entity function using the following code. The entity function returns a complex type called "EmployeeDetails".
using (Entities context = new Entities())
{
IEnumerable<EmployeeDetails> empDetails = context.GetEmployeeData();
}
2. Call Stored Procedure using ExecuteStoreQuery<T> function
"ExecuteStoreQuery<T>" should be used to query data. This method only works if T has a Default Constructor and also a Property name is the same as the returned column names. "T" can be any generic class or any data type and it might not be a part of an EF generated entity.
The following is the procedure to retrieve data using the "ExecuteStoreQuery<T>" method from a Stored Procedure.
Step 1:
The method "T" can be anything, it may be an EF Generated entity or it may be a Custom Entity, so first I am creating a Custom Entity "EmployeeDetail". Here the EmployeeDetail properties name must be the same as the returned column of the select statement of the Stored Procedure.
// Creating Custom class to hold result of Stored Procedure
public class EmployeeDetail
{
public int EmployeeID { get; set; }
public string EmployeeName { get; set; }
public string DepartmentName { get; set; }
}
// using Object Context (EF4.0)
using (Entities context = new Entities())
{
IEnumerable<EmployeeDetails> empDetails = context.ExecuteStoreQuery<EmployeeDetails>
("exec GetEmployeeData").ToList();
}
// using DBContext (EF 4.1 and above)
using (Entities context = new Entities())
{
IEnumerable<EmployeeDetails> empDetails = context. Database.SqlQuery
< EmployeeDetails >("exec GetEmployeeData ", null).ToList();
}
3. Call Stored Procedure using DbDataReader
We can also retrieve data or call a Stored Procedure using a SQL Connection Command and DbDataReader. The Object Context has a translate method that translates the entity data from DbDataReader into the requested type object. This method enables us to execute a standard ADO.Net query against a data source and return data rows into entity objects. Using the following code we can call a Stored Procedure and retrieve data in entity form.
using (Entities context = new Entities())
{
string ConnectionString = (context.Connection as EntityConnection).StoreConnection.ConnectionString;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.ConnectTimeout = 2500;
SqlConnection con = new SqlConnection(builder.ConnectionString);
System.Data.Common.DbDataReader sqlReader;
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "GetEmployeeData";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
sqlReader = (System.Data.Common.DbDataReader)cmd.ExecuteReader();
IEnumerable<EmployeeDetail> empDetails = context.Translate<EmployeeDetail>(sqlReader).ToList();
}
}
Conclusion
Using the methods described above, we can call a Stored Procedure and retrieve data as a scalar or complex value.
