Execute SQL Stored Procedure in Entity Framework

In this blog I will going to explain about how to execute stored procedure in entity framework, and Stored Procedure as Entity Function.

1. Create Stored Procedure
   

   ALTER PROCEDURE [dbo].[sp_GelUserContactDetails]
   (
       @Id INT=0,
       @UserId int=0
   )
   AS
      BEGIN
      SELECT M.Id AS UserID, M.FirstName as Name,M.Designation,M.CompanyName FROM dbo.Flo_MemberShip M
      WHERE ID=@Id
   
   END

2. Stored Procedure as Entity Function

Step 1: Import Stored Procedure



Step 2: select Stored Procedure and click on finish button.

 Add stored Procedure in Entity Framework

Step 3: Call Stored Procedure using SqlQuery<T> function

here T is a Entity here I am creating a Entity that`s name is MyContacts. Here the MyContacts properties name must be the same as the returned column of the select statement of the Stored Procedure.

// Creating Stored Procedure that hold result of class MyContacts
public class MyContacts
{
    public int UserID { get; set; }
    public string Designation { get; set; }
    public string Name { get; set; }
    public string Organization { get; set; }
    public string Image { get; set; }
    public string RequestDate { get; set; }
    public string IsContact { get; set; }
}

Step 4: Call Stored Procedure using SqlQuery<T> function

Using the following code we can call a Stored Procedure and retrieve data in entity form.

MyContacts obj = new MyContacts();
using (var context = new FLOEntities())
{
    string query = "sp_GelUserContactDetails @Id,@UserId";
    SqlParameter Id = new SqlParameter("@Id", selfId);
    SqlParameter UserId = new SqlParameter("@UserId", userId);
    obj = context.Database.SqlQuery<MyContacts>(query, Id, UserId).FirstOrDefault();
}
return obj;