none
Use of Stored Procedures in Database Vs Entity in Entity Framework RRS feed

  • Question

  • Hi,

    I am starting new project and decided to use Entity Framework in Data Access Layer. I am unable to decide whether to write stored procedure or not in database? Below are my considerations.

    Note: I have experienced database developer and DBA in my project. 

    1. Entity Framework  provides an option to add, update and delete entities based on one-one, one-many and many-many relationships. Does it mean, i should not write any stored procedures in database and stored procedures allowed in Database if anything cannot be achievable in Entity Framework ?

    2. Can i use stored procedure in database for which has simple insert, update and delete functionality ?

    3. Entity Framework and database both supports transactions. Do i need to prefer to write code in Entity Framework for supporting  Transactions and avoid transaction in database stored procedures?

    4. Does bulk insert, update and delete supported in Entity Framework ? If yes, Does it have any performance impact as compared to ADO.Net ?

    Sunday, April 6, 2014 2:07 PM

Answers

  • Hello,

    >>1. Entity Framework provides an option to add, update and delete entities based on one-one, one-many and many-many relationships. Does it mean, i should not write any stored procedures in database and stored procedures allowed in Database if anything cannot be achievable in Entity Framework ?

    I do not think so, the database is the database, entity framework will mapped some or all objects of the database to the model which we need to use, you can write any object your can and let them stay in database, at that time, we may do not need to using them in model.

    >>2. Can i use stored procedure in database for which has simple insert, update and delete functionality ?

    Yes, we can, we can use stored procedure we have written in database to perform insert, update and delete operations for the single entity. You can check this blog:

    http://weblogs.asp.net/zeeshanhirani/archive/2010/10/14/inserting-updating-and-deleting-entity-using-stored-procedures.aspx

    >> 3. Entity Framework and database both supports transactions. Do i need to prefer to write code in Entity Framework for supporting Transactions and avoid transaction in database stored procedures?

    Entity Framework has an inside transaction when we can its SaveChange() method. I usually use this inside transaction and if you want to control the transaction yourself, you can use transactionscope

    >> 4. Does bulk insert, update and delete supported in Entity Framework ? If yes, Does it have any performance impact as compared to ADO.Net ?

    It seems now Entity Framework has supported this with the extend method:

    EntityFramework.BulkInsert:

    https://efbulkinsert.codeplex.com/

    Batch Update and Delete

    https://github.com/loresoft/EntityFramework.Extended/blob/master/readme.md

    There are detail explain for how to use them and how they work.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 7, 2014 3:09 AM
    Moderator
  • The best ways to take an Architecture decision on ORM VS SPs is to find out the “Database Model Complexity”, Refer when to use an ORM’ chart.

    I will recommend to go with a Hybrid model i.e. EF generated code whenever possible, and move performance related use-case to SPs and call that from EF. Because,

    A set of SQL stored procedures is effectively a second codebase. Proper cultivation of that code requires specifications, tests, and a way to ferret out regressions and other mistakes. An EF does all this for you, by coming to the table with a completely tested and ready-to-go set of capabilities that take all of that out of your hands.

    Refer this for some key advantage of EF over here



    Lingaraj Mishra

    Monday, April 7, 2014 6:50 AM

All replies

  • Hello,

    >>1. Entity Framework provides an option to add, update and delete entities based on one-one, one-many and many-many relationships. Does it mean, i should not write any stored procedures in database and stored procedures allowed in Database if anything cannot be achievable in Entity Framework ?

    I do not think so, the database is the database, entity framework will mapped some or all objects of the database to the model which we need to use, you can write any object your can and let them stay in database, at that time, we may do not need to using them in model.

    >>2. Can i use stored procedure in database for which has simple insert, update and delete functionality ?

    Yes, we can, we can use stored procedure we have written in database to perform insert, update and delete operations for the single entity. You can check this blog:

    http://weblogs.asp.net/zeeshanhirani/archive/2010/10/14/inserting-updating-and-deleting-entity-using-stored-procedures.aspx

    >> 3. Entity Framework and database both supports transactions. Do i need to prefer to write code in Entity Framework for supporting Transactions and avoid transaction in database stored procedures?

    Entity Framework has an inside transaction when we can its SaveChange() method. I usually use this inside transaction and if you want to control the transaction yourself, you can use transactionscope

    >> 4. Does bulk insert, update and delete supported in Entity Framework ? If yes, Does it have any performance impact as compared to ADO.Net ?

    It seems now Entity Framework has supported this with the extend method:

    EntityFramework.BulkInsert:

    https://efbulkinsert.codeplex.com/

    Batch Update and Delete

    https://github.com/loresoft/EntityFramework.Extended/blob/master/readme.md

    There are detail explain for how to use them and how they work.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 7, 2014 3:09 AM
    Moderator
  • The best ways to take an Architecture decision on ORM VS SPs is to find out the “Database Model Complexity”, Refer when to use an ORM’ chart.

    I will recommend to go with a Hybrid model i.e. EF generated code whenever possible, and move performance related use-case to SPs and call that from EF. Because,

    A set of SQL stored procedures is effectively a second codebase. Proper cultivation of that code requires specifications, tests, and a way to ferret out regressions and other mistakes. An EF does all this for you, by coming to the table with a completely tested and ready-to-go set of capabilities that take all of that out of your hands.

    Refer this for some key advantage of EF over here



    Lingaraj Mishra

    Monday, April 7, 2014 6:50 AM
  • There is a whole new approach that you may want to consider if what you're after is the power and performance of stored procedures, and the rapid development that tools like Entity Framework provide.

    I've taken SQL+ for a test drive on a small project, and it is really something special. You basically add what amounts to comments to your SQL routines, and those comments provide instructions to a code generator, which then builds a really nice object oriented class library based on the actual SQL routine. Kind of like entity framework in reverse.

    Input parameters become part of an input object, output parameters and result sets become part of an output object, and a service component provides the method calls.

    If you want to use stored procedures, but still want rapid development, you might want to have a look at this stuff.

    www.sqlplus.net


    Friday, February 8, 2019 2:16 PM