6

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Deepak Tekchandani
  • 488
  • 2
  • 5
  • 15

2 Answers2

18

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:

  1. Double click your EDMX file
  2. Choose Update Model from Database

You will see the dialog similar to below:

enter image description here

  1. Make sure you have checked the boxes as shown.

That 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 auto-generated by EF then do so. I strongly suggest you do this and give your class a meaningful names that follow .NET naming conventions. The convention I follow is remove any verbs from the stored procedure name and append the word result to the end. So you will end up with name as shown below:

enter image description here

  1. Press OK

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.
Community
  • 1
  • 1
CodingYoshi
  • 25,467
  • 4
  • 62
  • 64
  • Thank you very much for the answer. I followed the same approach, but instead of creating a new complex return type, I selected entities (which in this case is _Employee_), then that created problem in mapping the select query with the _Employee_ class properties. So is it like if I want to use model generated by EF and using stored procedure to return something, then it should be equal, else it will throw error. And since my project has more than 100 tables, so I will have extra 100 classes to deal with for stored procedure. Can't they work with the existing models? – Deepak Tekchandani May 06 '17 at 17:28
  • Why do you care about the number of classes? They are auto generated by EF? – CodingYoshi May 07 '17 at 12:24
  • If your stored procedure returns something with the same properties as one of your entities, you can select that entity in the figure above step 5. – CodingYoshi May 07 '17 at 12:34
  • Yes, I can create a new entity(let's call it _EmployeeOutput_) based on my stored procedure. Currently am using _Employee_ class to do all the operations, now after stored procedure, will have another class which will be sort of subset of _Employee_ class and now I have two classes(with almost identical properties) to deal with. Afterwards for all the insert and update operations, will be using _Employee_ class and for select operations, _EmployeeOutput_. So, I want to ask, is it how we use Entity Framework in real application, or I am missing something? – Deepak Tekchandani May 07 '17 at 15:26
  • Like I said if you already have an entity, then map it to that entity. If you need an extra property you can derive a class and then select that as the result of the stored procedure. You can do all of that but as long as the properties match the resultset from the stored procedure. In the figure above step 5, you can select an existing class you already have and EF will map it to that. Make sense? – CodingYoshi May 07 '17 at 17:27
  • Is there a way to generate these but have them be asynchronous? – Hazza Aug 07 '18 at 11:12
  • Just an FYI this approach, while valid in all versions of Visual Studio 2017, this approach does not work in Visual Studio 2019 version 16.0.0 – Arthur Yegiazaryan Apr 08 '19 at 18:10
0
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");
Jurabek Azizkhujaev
  • 153
  • 2
  • 9
  • 1
    You'll probably want to add a `[NotMapped]` attribute on `FullName` _ otherwise EF is trying to fetch that property from the database table (where it doesn't exist.....) – marc_s May 06 '17 at 14:01
  • I just write it on notepadd++ it is not tested, it is way how to solve it. – Jurabek Azizkhujaev May 06 '17 at 14:03