-2

I am looking to build the data access layer of my MVC5 application. In our project we are going for database first approach with stored procedures only as team is more conversant with SQL and would like to perform all CRUD operations via stored procedures.

I am looking for good examples that show the implementation of this approach. I want to see how the entities are mapped. As this would be stored procedures in the database getting mapped to classes in .net.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tom
  • 8,175
  • 41
  • 136
  • 267

1 Answers1

0

I think its time for your team to become "conversant" with EF if you are going to use it. Doing every single CRUD operation with stored procedures is not the path I would take. If the stored procedure is doing something simple as:

Get the company record with ID 1

Then I would not use stored procedure and use EF. For more complex operations, stored procedures can be used. Therefore, you and your team may want to have team work session to decide on when to use stored procedures and when not to. Once you have decided, the whole team should stick to that approach. If you need to change it, have another meeting and make sure everyone is in the know. It is important for everyone to follow the same pattern once the team has agreed to it.

How to use stored procedures with EF?

I would start with one test stored procedure to see how the whole thing works. Once you and your team know exactly how the process works with EF, then put together a design, conventions etc. and then the whole team should follow the same pattern.

  1. Write a test stored procedure which returns a resulset.
  2. Create the EDMX by connecting to your database from Visual Studio.
  3. Add the stored procedure to your EDMX.
  4. Use the model browser to add a Function Import. This will create a method in your context which you can call like any other method, but underneath it will call your stored procedure. Please see this answer for more on how to do this step.
  5. Step 4 will create a class based on your stored procedure's resultset.

Note

You may need to set this flag to off, TEMPORARILY, for EF to create the complex type based on your stored procedure result set.

SET FMTONLY OFF

See this answer for more about the flag.

Community
  • 1
  • 1
CodingYoshi
  • 25,467
  • 4
  • 62
  • 64
  • I agree with you that the approach of using storedprocs in not the way going forward but its just that the team needs more control on storedprocedures so that sql team can troubleshoot issues that occur in production. Also they want to be able to fix the issues in storeprocedures and deploy rather that touching the code base and deploying it. I have actually tried the one that you suggested. That is till the level of creating a function import. I am looking at an example of how a wrapper would be created over entity framework and how the storedrocedures would be automapped to business objects – Tom Mar 11 '17 at 12:42
  • Sorry but you did not mention none of that in your question that you are looking for a wrapper...perhaps you should read [this](http://stackoverflow.com/help/how-to-ask). Also, for mapping you may use [AutoMapper](http://automapper.org/) – CodingYoshi Mar 11 '17 at 12:58
  • Sorry for not being clear with my question . I am aware of auto mapper and have done mapping to tables . How is mapping of stored procedures done – Tom Mar 11 '17 at 13:26
  • What exactly do you mean? In my answer I have explained that SPs will return a complex class instance. You will map that to your business object like any other object. – CodingYoshi Mar 11 '17 at 13:31