Background

Very recent in one of our project we came to a need to manage custom stored procedures in entity framework in a convenient way so that we do not manage them manually for any modification. Moreover, we wanted to reduce the deployment hassle. We were using the code first approach and our intention was to deploy them automatically using the entity framework Migration. Here are my findings after google search and study.

Findings

So far Entity framework 6 has an option to use user defined stored procedures for CRUD operation (INSERT, UPDATE, DELETE) for an entity. We can do this overriding OnModelCreating function in the following way:
modelBuilder.Entity<EntityModel>().MapToStoredProcedures();
MapToStoredProcedures() has overload for custom stored procedure name and parameter.

 

Entity framework does not encourage to write custom stored procedures for select operation from the database. However, it has an option to create a complex entity using custom stored procedures which are applicable only for Database first approach(https://msdn.microsoft.com/en-us/data/gg699321.aspx).
 

Workaround

For code first approach we can manage them in a tricky way using the following steps:

 

    1. Add an empty Migration

    2. Write custom code in Up() and Down() function of Migration file to override them.
    
      public override void Up()
      {
      CreateStoredProcedure(
      name: "[dbo].[StoredProcedureName]",
      parametersAction:
      p => new {
      Id = p.String(50),
      },
      body: @" Stored procedure body goes here"
      );
      }
    
      public override void Down()
      {
      DropStoredProcedure("StoredProcedureName");
      }

 

    3. For any modification or update in the SP you need to follow the steps 1 & 2 and replace the    

CreateStoredProcedure() with AlterStoredProcedure().

 

Pros & Cons

This approach facilates the way to manage srored procedures through entity framework migration which does not require to deploy them manually but this approach of entity framework for custom stored procedures is not manageable and troublesome when modification is needed. Moreover, we can not generate the actual TSQL through this migration file for our local development environment testing phase.
 

Decision

Considering above points we will not manage stored procedures through this way.

Ref:
https://msdn.microsoft.com/en-us/data/dn468673.aspx
http://www.dotnetcurry.com/entityframework/938/ef-db-logging-stored-proc...
http://www.mikesdotnetting.com/article/299/entity-framework-code-first-a...

Add new comment