How To Call Stored Procedure In Entity Framework

Jignesh Trivedi

2y819k20100Article

Entity Framework Store ProcedureDbDataReaderEntity FunctionExecuteStoreQuery Function

20 Comments

Join the conversation! Your thoughts help the community grow.

Tahir Ansari3y ago
Please share the stored procedure with mutliple paramaters using EntiyFramework
RobertoCarlos Melgar8y ago
Thank you very muchplease can you provide us with an example for an insert procedureexampleinsertcustomer @ param1 nvarchar, @ param2 nvarchar, @ param3 datetime please, I just can not find out what is the best way to do it this with sql server and mvc 5 and entity framework Thank you, Bolivia
- Jignesh Trivedi8y ago
  This is example of database first approach. are you using which approach: db first or code first?
- RobertoCarlos Melgar7y ago
  EF Designe a partir de una base de datos existente Thank you very much for reading my questions, I want to use my stored procedures since I want to migrate from winforms to mvc please help
Ajay Antala8y ago
Also useful this article but how to use without using this funcation how can i do in mysql (c#).please help me.
Ajay Antala8y ago
Hi, I'm using entity framework in asp.net and now i want to call mysql stored-procedure,ok it's call but it return int valu(0-1),but i want to return list,that use to bind gridview. search in google but only suggestion import funcation and call it,but my requirement is call without use this function,how to solve this query,,any one expert can suggestion me, Thank you.
Vinay Phadke10y ago
Thanks for the nice article. I have a question- Why does SP called from EF(I'm using version 6.1.3) takes time as compared toSP execution time of SQLCommand's ExecuteNoNQuery() & Datareader? I'm making an Async call using EF like below- public async Task<IList<T>> ExecSPAsync<T>(string query) { return await Context.Database.SqlQuery<T>(query).ToListAsync(); } Use of SQLCommand/SQLDatareader seems to be much efficient compared with SP call using EF. What would be the efficient way of calling SP using EF?
Ajeet Mishra10y ago
Nice
Upendra Pratap Shahi10y ago
nice
Vignesh Mani10y ago
Good one
george genner10y ago
IEnumerable<Configuration> empDetails = context.Database.SqlQuery<Configuration>("exec sampleproc").ToList(); Remove null in the above line when procedure does not accept Parameters Otherwise we will be getting an error like parameters can not be null
Rupali Shinde10y ago
useful article on mvc with stored procedure ..
Gaurav Gupta11y ago
But if we have so many stored procedures in my database, then for each SP Entity framework creates a custom entity in my application model.???
- Gaurav Gupta11y ago
  But if stored procedure return some specific fields, then???
- Jignesh Trivedi11y ago
  No you can also map existing entity to stored procedure result
rajaedp12y ago
I have tried using this example, but am getting compile error saying that " Translate method doesn't exist". I am using EF5.0. Any reason for this? Pls help.
- Jignesh Trivedi12y ago
  hi,
- Jignesh Trivedi12y ago
  this code is for EF 4.0 and With EF5.0 there is DbContext so you have to cast from dbcontext to object context
- Jignesh Trivedi12y ago
  IEnumerable<EmployeeDetail> empDetails = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)Context).ObjectContext.Translate<EmployeeDetail>(sqlReader).ToList();
Mukesh Kumar Tiwari12y ago
Good one bro......

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

Choose DB

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".

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.

Stored Prcedure Name

Function Import Name

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.