I am doing transition for a project from Webforms to MVC application using Entity Framework database first approach and have database ready along with all stored procedures in place.
I successfully created an .edmx
file and was able to use my stored procedures and it worked great when there was any insert or update operation to perform. But the real problem occurred when I was using select query in one of my stored procedures.
For example, there is an Employee
table which has following columns:
EmpId, FirstName, LastName, Age, Salary
I have a stored procedure GetAllEmpDetails
which has following select query.
Select
EmpId, (FirstName + ' ' + LastName) as FullName, Salary
from
Employee
Now when I am trying to bind the result of this stored procedure with the Employee
class which has 5 properties as per the table structure, then I am getting an error that value for Age
property is expected but it is not available in the resultset.
I know there is no FullName
property as well, so my question is how to solve this problem with the model class generated (as in this case Employee
) so that it can tackle these dynamism?
How to map a stored procedure in EF?
Since you are doing Database First Approach and you have an EDMX file, let EF generate the class of the stored procedure result for you. You may have many stored procedures and you want to avoid creating the classes manually: After all that is the whole point of using an ORM tool. Also some of your stored procedures may have parameters. Doing it the way below will handle all that for you. It is actually pretty simple.
To get EF to do this for you, follow the steps to below:
You will see the dialog similar to below:
That will add the stored procedure and you will see it in your model browser as shown below:
Some Notes
This is much better than writing the classes manually in case your stored procedure name, or the parameters it needs, or the result it returns changes. This approach will work for user defined functions as well.
A Gotcha
There will be times when the stored procedure will not appear in the selection in the wizard dialog, that is because of this. Simply add this to the beginning of your stored procedure:
SET FMTONLY OFF -- REMEMBER to remove it once the wizard is done.
public class EmployeeProcedure
{
[Column("EmpId")]
public int EmployeeId { get; set; }
[NotMapped]
public string FullName { get; set; }
public double Salary { get; set; }
}
after that call this:
this.Database.SqlQuery<EmployeeProcedure>("GetAllEmpDetails");