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

Edit:

If you are getting a mapping error ("Error 3027: No mapping specified for the following EntitySet/AssociationSet") after doing this, it's because the entity you created is not mapped to anything and is only ever used when the function import populates a collection of these entities. You either need to map this entity to a data store somehow or you need to change it to a complex type.

To create a complex type simply open up the EF designer and right-click on an empty area. Go to Add > Complex Type. You should see a new complex type appear in the model browser. Right click it and add scalar properties similar to how you added properties to your entity. Then delete your entity and rename your complex type the same as the entity.

That's all you have to do :)