Stored procedure in Entity Framework database first approach

c# entity-framework stored-procedures

Accepted Answer

In EF, how do I map a saved procedure?

Let EF build the class of the stored procedure result for you because you are doing database-first strategy and you have an EDMX file. You could have a lot of stored procedures, thus you don't want to manually create the classes: After all that is the whole point of using an ORM tool. You could also have parameters for some of your saved methods. The process outlined below will take care of everything for you. Actually, it's rather easy.

Follow these steps to have EF handle this for you:

  1. Click the EDMX file twice.
  2. Select Model Update from Database.

The dialogue will look like this:

enter image description here

  1. Verify that you have ticked all the appropriate boxes.

You will notice the saved procedure added as shown below in your model browser after doing that:

enter image description here

  1. Change the class name that was automatically created by EF if you wish to. Giving your class a meaningful name that adheres to.NET naming rules is something I urge you to do. I usually exclude any verbs from the saved process name and add the word result at the end as per tradition. Your final name will thus be as follows:

enter image description here

  1. Input OK.

A Few Notes

In the event that the name of your stored procedure, the arguments it requires, or the outcome it returns change, this is much preferable than defining the classes manually. This strategy will also be effective for user-defined functions.

One Gotcha

Sometimes, due to this, the saved procedure won't be available for selection in the wizard dialogue. Just add the following at the start of your saved procedure:

SET FMTONLY OFF -- REMEMBER to remove it once the wizard is done.
14
5/23/2017 12:18:23 PM

Popular Answer

public class EmployeeProcedure
{
    [Column("EmpId")]
    public int EmployeeId { get; set; }

    [NotMapped]
    public string FullName { get; set; }

    public double Salary { get; set; }
}

call that after that:

this.Database.SqlQuery<EmployeeProcedure>("GetAllEmpDetails");


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow