Clever Geek Handbook

Stored Procedure in Entity Framework Database First Approach

I am making a transition for a project from Webforms to MVC application using the first Entity Framework database approach, and a pre-built database along with all the stored procedures.

I created the file successfully .edmx

and was able to use my stored procedures and it worked great when any insert or update operation was performed. But the real problem came when I used a select query in one of my stored procedures.

For example, there is a table Employee

that has the following columns:

EmpId, FirstName, LastName, Age, Salary

I have a stored procedure GetAllEmpDetails

that has the following select query.

Select 
    EmpId, (FirstName + ' ' + LastName) as FullName, Salary 
from 
    Employee

Now when I try to bind the result of this stored procedure to a class Employee

that has 5 properties according to the table structure, then I get an error the value of which is expected for the property Age

, but that is not available in the result set.

I know there is no property FullName

, so my question is how to solve this problem with the generated model class (as in this case Employee

) so that it can deal with this dynamism?

+3


source to share


2 answers


How do I map a stored procedure in EF?

Since you are doing a basic database approach and you have an EDMX file, let EF generate a stored procedure result class for you. You may have many stored procedures, and you want to avoid creating classes by hand: it all means using an ORM tool. Additionally, some of your stored procedures may have parameters. Doing this below will handle everything for you. It's actually pretty simple.

To get EF for this, follow these steps:

  • Double click the EDMX file
  • Select upgrade model from database

You will see a dialog box similar to the one below:

enter image description here

  1. Make sure you check the fields as shown.

This will add the stored procedure and you will see it in your model browser as shown below:

enter image description here



  1. If you want to change the class name automatically generated by EF, do so. I highly recommend that you do this and give your class meaningful names that follow the .NET naming conventions. The next convention is to remove any verbs from the stored procedure name and append the word result to the end. Thus, you will get a name like below:

enter image description here

  1. Click OK

Some notes

It is much better than writing the classes by hand in case the stored procedure name or parameters it needs, or the result that returns changes. This approach will work for custom functions as well.

A Gotcha

There are times when the stored procedure will not appear in the wizard dialog box, which is related to this . Just add this to the beginning of your stored procedure:

SET FMTONLY OFF -- REMEMBER to remove it once the wizard is done.
+7


source 


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

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

    public double Salary { get; set; }
}

after that call it:



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


source






More articles:


All Articles