How to use a stored procedure in ADO.NET Entity Framework

Question

I have 3 tables; I write a stored procedure in ADO.NET Entity Framework.

ALTER PROCEDURE [dbo].[sp_GetDepartmanData]
(@departman nvarchar(50))
BEGIN
  SELECT  
    d.ID, d.Name as DepartmanName,  
    sb.Salary, sb.email,
    sp.Name, sp.SurName, sp.Phone, sp.Married, sp.Address
  FROM         
    Departman d 
  INNER JOIN StaffsBusiness sb ON d.ID = sb.StaffsPersonelDepartmanID
  INNER JOIN StaffsPersonel sp ON sb.StaffsPersonelID = sp.ID 
  WHERE
    d.Name = @departman
END

---

I need a stored procedure function I write below:

var staffPersonel = staffContext.GetPersonelInformationWithDepartmanID("Yazılım");

gvPersonel.DataSource = staffPersonel;
gvPersonel.DataBind();

GetPersonelInformationWithDepartmanID function I write from SQL (user defined function in ADO.NET Entity Framework) there are 3 alternative (it is silly!!!) but i have 3 joininig table!!!. How can i use if i join 3 table before?

Answer 1

Okay, you need a few steps here:

• add your stored procedure sp_GetDepartmanData to your Entity Framework model (as an aside - it's is strongly recommend NOT to call your stored procedures sp_(something) - use of the sp_ prefix is reserved for Microsoft-only system stored procedures)
• since your stored procedure is returning a set of data, you will need to create a conceptual entity for it first, before you can use your stored proc; in the Entity Designer, create a new entity and call it some useful name like DepartmentDataEntityType or something; add all the fields being returned from the stored procedure to that entity type
• now, you can create your function import in the entity data model - go to the model browser, in the "model.store" section go to your stored procedure, and right-click on "create function import"
• you can now give your function in the object context a name and define what it returns - in this case, pick your newly created entity type (e.g. DepartmentDataEntityType from above)
• you're done!

You should now have a function import something like:

public global::System.Data.Objects.ObjectResult<DepartmentDataEntityType> GetPersonelInformationWithDepartmanID(global::System.String departmentName)
{
    global::System.Data.Objects.ObjectParameter departmentNameParameter;

    departmentNameParameter = new global::System.Data.Objects.ObjectParameter("departmentNameParameter", departmentName);

    return base.ExecuteFunction<DepartmentDataEntityType>("sp_GetDepartmanData", departmentNameParameter);
}

This function on your object context can now be call to retrieve the data via the stored procedure from your database.

Marc

Answer 2

How do you create this "conceptual entity"? If I create an entity which is not mapped to the I get the following error: "Entity type 'foobar' is not mapped to the database.