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
    1. ALTER PROCEDURE [dbo].[sp_GelUserContactDetails]
    2. (
    3. @Id INT=0,
    4. @UserId int=0
    5. )
    6. AS
    7. BEGIN
    8. SELECT M.Id AS UserID, M.FirstName as Name,M.Designation,M.CompanyName FROM dbo.Flo_MemberShip M
    9. WHERE ID=@Id
    11. END
  2. Stored Procedure as Entity Function

Step 1: Import Stored Procedure

Stored Procedure

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

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.

  1. // Creating Stored Procedure that hold result of class MyContacts
  2. public class MyContacts
  3. {
  4. public int UserID { get; set; }
  5. public string Designation { get; set; }
  6. public string Name { get; set; }
  7. public string Organization { get; set; }
  8. public string Image { get; set; }
  9. public string RequestDate { get; set; }
  10. public string IsContact { get; set; }
  11. }
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.
  1. MyContacts obj = new MyContacts();
  2. using (var context = new FLOEntities())
  3. {
  4. string query = "sp_GelUserContactDetails @Id,@UserId";
  5. SqlParameter Id = new SqlParameter("@Id", selfId);
  6. SqlParameter UserId = new SqlParameter("@UserId", userId);
  7. obj = context.Database.SqlQuery<MyContacts>(query, Id, UserId).FirstOrDefault();
  8. }
  9. return obj;