EF based Repository pattern and store procedure calling RRS feed

  • Question

  • i was searching google for this subject and found one link http://stackoverflow.com/a/27975496/728750

    the person saying

    The purpose of the Repository Pattern is to abstract away the storage & retrieval of data to protect your client code e.g. 
    business layer (service layer in your case) from needing to know anything about how data is persisted. SQL statements,
    for example, would only exist inside your Repository classes, and not ripple throughout your code. If you expose SQL, Stored Procedure names and parameters to your client code your are not getting much benefit from the Repository Pattern,
    and if fact you can't really call it a Repository at all. You lose the benefit of being able to mock the repository and test your
    business layer independently of your data access layer. This means integration tests (requiring a full database instance)
    are required to verify business logic.

    may be he is right but we often work with sql server store procedure for achieving complex query with many loop/if..else etc.

    so tell me how could i achieve store procedure calling with EF based Repository pattern ?

    looking for great example which will be widely accepted when some one review the code.

    few more links for same issues



    Monday, February 13, 2017 9:08 AM


  • Hi Mou_kolkata,

    Based on my experience, I agree with the option, As we know the advantages of using ORM is:

    1. Database independent. This is the biggest advantage. No need to write code specific to database. I have worked in various products where they maintain separate module / code base for every database and there is lot of effort invested to support multiple database. ORM is a boon.
    2. There is no need to write SQL queries.
    3. Takes care of dependencies between tables and does join queries.
    4. Few ORM libraries has support of caching. Hibernate uses ehcache and provides caching support. This reduces the load from the database and increases the response time.
    5. Maintains transactions commit and rollback.
    6. Maintains database connection pool.
    7. Concurrency support.
    8. Easy maintenance and increases productivity.

    If your project primarily uses stored procedures, I would suggest that you could ado.net instead of entity framework.

    Best regards,

    Cole Wu

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Mou_kolkata Tuesday, February 14, 2017 7:47 AM
    Tuesday, February 14, 2017 6:21 AM