Call Stored Procedure From Entity Framework

Introduction

In this article, I will demonstrate how to call a Stored Procedure from Entity Framework. There are many ways to call Stored Procedures from Entity Framework.

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.