Entity Framework code-first & stored procedure

Question

I am using a code-first approach in my application. I have generated the entities (tables) using the approach. Now I would like to create a stored procedure as well through code. Can someone guide me as I tried migration option and it's failing.

I am using Entity Framework Code First approach.Using this,I have created Customer and some other entities.

Now I want to create a stored procedure 'GetCustomers' using context class and pass parameters and get result set in a collection

it has to return 2 collections as below

create procedure getcustomer @name nvarchar(max),@zipcode int

as

select id,name,zipcode from Customer where name like (@name );

select id,name,zipcode from Customer where zipcode =@zipcode

I want to create a stored procedure 'GetCustomers' using context class and not manually execute in DB.I need to achieve below results:

1.Pass name parameter alone and return first collection

2.Pass zipcode parameter alone and return 2nd collection.

3.Combine result collection of 1 and 2 into a single collection using merge

Vignesh Vignesh · Accepted Answer · 2017-08-21T07:22:38

You can create/generate stored Procedure using CreateStoredProcedure() method using Add- Migration option in Entity Framework.

Step 1: Generate Migration script using add-migration SP_DO_NOT_DELETE in Package Manager Console. If no Model Changes is there, then the system will generate Empty migration script like below.

   public partial class SP_DO_NOT_DELETE : DbMigration
   {
      public override void Up()
      {      

      }

      public override void Down()
      {

      }
  }

Step 2: After generating the Script, Please add your stored procedure inside Up() and down() methods like below. Note: below example, "dbo.GetNextDisplayId" is the Stored Procedure Name which will be used to get the NextAvailableDisplayId using Stored procedure.

   public partial class SP_DO_NOT_DELETE : DbMigration
   {
      public override void Up()
      {      
        CreateStoredProcedure(
          "dbo.GetNextDisplayId",
          body:
              @"DECLARE @requestid INT  
                        SELECT @requestid = NextAvailableDisplayId  
                        FROM [TrackingNumberHistories] WITH (TABLOCKX)  

                        UPDATE [TrackingNumberHistories]
                        SET NextAvailableDisplayId = @requestid + 1

                        SELECT @requestid AS 'NextAvailableDisplayId'"
        ); 
       }

       public override void Down()
       {
          DropStoredProcedure("dbo.GetNextDisplayId");
       }
  }

Note: CreateStoredProcedure() in Up() Method will create Stored procedure automatically whenever running migration script. DropStoredProcedure() in Down() will be used to drop stored procedure when ever we roll back/delete the stored procedure automatically in migration script.

Hope this might help you to move forward!!

Entity Framework code-first & stored procedure

1 Answers