How To Call Stored Procedure In Entity Framework

Introduction

Entrity Framework is a data access layer ORM library. Entity Framework (EF) uses ADO.NET for data access and connectivity and supports LINQ as the language to read and write data back to a data source. It was originally shipped as a part of .NET Framework, but recently, Entity Framework 6 or later is its own standalone project available on Github. The latest version of EF is EF Core. EF Core supports connections with several databases, including SQL Server, SQL Azure, SQLite, MySQL, PostgreSQL, and Azure Cosmos DB.

When building data-driven software applications, you may want to call an existing stored procedure in Entity Framework, and there are different ways to do that. In this article, I will demonstrate how to call a Stored Procedure in Entity Framework.

There are many ways to call a Stored Procedure from Entity Framework code.

Prerequisites

The prerequisite for running these examples are the following sample tables with test data and a Stored Procedure. The following script helps 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  

Stored Procedure as Entity Function

The first method of calling a stored procedure in Entity Framework is as an Entity Function. Entity Framework has the capability of importing a Stored Procedure as a function in your existing project/class. We can also map the result of the function back to any entity type or complex type in the project. Let's look at how we can do it.

The following is the procedure to import and use a Stored Procedure in Entity Framework.

Step 1. Import Stored Procedure in Entity Framework

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) Entities. From this screen, we can create a Complex Entity as well.

Now, we can call the Stored Procedure 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();  
}  

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();  
}  

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 translation 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 one of the above methods, we can call a Stored Procedure in the Entity Framework and retrieve data as a scalar or complex value.