Code-First Stored Procedure Entity Framework 6.0

In this article you will learn about Code-First stored Procedures Entity of Framework 6.0.

Introduction

Code-First configures all entities to do the CRUD operations using direct table access. Using Entity Framework 6.0 and above, we can configure our code first model to use a Stored Procedure for a few or all entities of the model.

Stored Procedure Mapping

To use a Stored Procedure with the Code First model, we need to override the OnModelCreating method of DBContext and add the following code to map the Stored Procedure.

  1. protected override void OnModelCreating(DbModelBuilder modelBuilder)  
  2. {  
  3.     modelBuilder.Entity<yourEntity>().MapToStoredProcedures();  
  4. }  
The MapToStoreProcedures method has two overloaded methods, one method is without a parameter. This method uses Entity Framework code-first conventions to set up the Stored Procedure. The another method takes an action method as an input and allows us to customize the Stored Procedure name, parameter, schema name and so on.

By default an insert Stored Procedure has a parameter for every property except the properties marked as store generated (identity and computed). This Stored Procedure returns the value of the store generated column. An Update Stored Procedure has a parameter for every property except properties marked as a store generated (computed only). This Stored Procedure returns the result set with computed properties. Delete Stored Procedure has a parameter that is part of the entity key. This Stored Procedure returns nothing.

Example

I have the following classes:
  1. public class DepartmentMaster  
  2. {  
  3.     [Key]  
  4.     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]   
  5.     public int DepartmentId { getset; }  
  6.     public string Code { getset; }  
  7.     public string Name { getset; }  
  8.     public List<EmployeeMaster> Employees { getset; }  
  9. }  
  10.   
  11. public class EmployeeMaster  
  12. {  
  13.     [Key]  
  14.     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]   
  15.     public int EmployeeId { getset; }  
  16.     public string Code { getset; }  
  17.     public string Name { getset; }  
  18.     public int DepartmentId { getset; }  
  19.     public DepartmentMaster Department { getset; }  
  20. }  
My context class is as in the following. In this class, I overrode the OnModelCreating method to map the Stored Procedure with the EmployeeMaster entity.
  1. public class EntitiesContext : DbContext  
  2. {  
  3.     public EntitiesContext() base("name=Entities")  
  4.     {  
  5.   
  6.     }  

  7.     public DbSet<DepartmentMaster> Departments { getset; }  
  8.     public DbSet<EmployeeMaster> Employees { getset; }  
  9.   
  10.     protected override void OnModelCreating(DbModelBuilder modelBuilder)  
  11.     {  
  12.         modelBuilder.Entity<EmployeeMaster>()  
  13.             .MapToStoredProcedures(s => s.Insert(u => u.HasName("InsertEmployee""dbo"))  
  14.                                             .Update(u => u.HasName("UpdateEmployee""dbo"))  
  15.                                             .Delete(u => u.HasName("DeleteEmployee""dbo"))  
  16.             );  
  17.     }  
  18. }  
Now I run the migration steps.

Step 1

Enable Migration

enable-migrations -ContextTypeName CodeFirstStoredProcedure.EntitiesContext -MigrationsDirectory:EntitiesMigrations

package source

Step 2

Add Migration Configuration

Add-Migration -configuration CodeFirstStoredProcedure.EntitiesMigrations.Configuration InitialEntities

Package Manager Console

The add migration command generates a Dbmigration class. This DB Migration class has the definition for all the Stored Procedures.
  1. public partial class InitialEntities : DbMigration  
  2. {  
  3.     public override void Up()  
  4.     {  
  5.         CreateStoredProcedure(  
  6.             "dbo.InsertEmployee",  
  7.              p => new  
  8.             {  
  9.                 Code = p.String(),  
  10.                 Name = p.String(),  
  11.                 DepartmentId = p.Int(),  
  12.             },  
  13.             body:  
  14.                 @"INSERT [dbo].[EmployeeMasters]([Code], [Name], [DepartmentId])  
  15.             VALUES (@Code, @Name, @DepartmentId)  
  16.                         
  17.             DECLARE @EmployeeId int  
  18.             SELECT @EmployeeId = [EmployeeId]  
  19.             FROM [dbo].[EmployeeMasters]  
  20.             WHERE @@ROWCOUNT > 0 AND [EmployeeId] = scope_identity()  
  21.                         
  22.             SELECT t0.[EmployeeId]  
  23.             FROM [dbo].[EmployeeMasters] AS t0  
  24.             WHERE @@ROWCOUNT > 0 AND t0.[EmployeeId] = @EmployeeId"  
  25.         );  
  26.   
  27.         CreateStoredProcedure(  
  28.             "dbo.UpdateEmployee",  
  29.             p => new  
  30.             {  
  31.                 EmployeeId = p.Int(),  
  32.                 Code = p.String(),  
  33.                 Name = p.String(),  
  34.                 DepartmentId = p.Int(),  
  35.             },  
  36.             body:  
  37.                 @"UPDATE [dbo].[EmployeeMasters]  
  38.             SET [Code] = @Code, [Name] = @Name, [DepartmentId] = @DepartmentId  
  39.             WHERE ([EmployeeId] = @EmployeeId)"  
  40.         );  
  41.   
  42.         CreateStoredProcedure(  
  43.             "dbo.DeleteEmployee",  
  44.             p => new  
  45.             {  
  46.                 EmployeeId = p.Int(),  
  47.             },  
  48.             body:  
  49.                 @"DELETE [dbo].[EmployeeMasters]  
  50.             WHERE ([EmployeeId] = @EmployeeId)"  
  51.         );  
  52.   
  53.     }  
  54.   
  55.     public override void Down()  
  56.     {  
  57.         DropStoredProcedure("dbo.DeleteEmployee");  
  58.         DropStoredProcedure("dbo.UpdateEmployee");  
  59.         DropStoredProcedure("dbo.InsertEmployee");  
  60.     }  
  61. }  
Step 3

Update Database

Update-Database -configuration:CodeFirstStoredProcedure.EntitiesMigrations.Configuration -Verbose

update database configuration

Update database command creates tables and Stored Procedure and definition of the Stored Procedure is as the following:
  1. CREATE PROCEDURE [dbo].[InsertEmployee]  
  2.     @Code [nvarchar](max),  
  3.     @Name [nvarchar](max),  
  4.     @DepartmentId [int]  
  5. AS  
  6. BEGIN  
  7.     INSERT [dbo].[EmployeeMasters]([Code], [Name], [DepartmentId])  
  8.     VALUES (@Code, @Name, @DepartmentId)  
  9.       
  10.     DECLARE @EmployeeId int  
  11.     SELECT @EmployeeId = [EmployeeId]  
  12.     FROM [dbo].[EmployeeMasters]  
  13.     WHERE @@ROWCOUNT > 0 AND [EmployeeId] = scope_identity()  
  14.       
  15.     SELECT t0.[EmployeeId]  
  16.     FROM [dbo].[EmployeeMasters] AS t0  
  17.     WHERE @@ROWCOUNT > 0 AND t0.[EmployeeId] = @EmployeeId  
  18. END  
  19.   
  20. GO  
  21.   
  22. CREATE PROCEDURE [dbo].[UpdateEmployee]  
  23.     @EmployeeId [int],  
  24.     @Code [nvarchar](max),  
  25.     @Name [nvarchar](max),  
  26.     @DepartmentId [int]  
  27. AS  
  28. BEGIN  
  29.     UPDATE [dbo].[EmployeeMasters]  
  30.     SET [Code] = @Code, [Name] = @Name, [DepartmentId] = @DepartmentId  
  31.     WHERE ([EmployeeId] = @EmployeeId)  
  32. END  
  33.   
  34. GO  
  35.   
  36. CREATE PROCEDURE [dbo].[DeleteEmployee]  
  37.     @EmployeeId [int]  
  38. AS  
  39. BEGIN  
  40.     DELETE [dbo].[EmployeeMasters]  
  41.     WHERE ([EmployeeId] = @EmployeeId)  
  42. END  
store procedure in database

Test Code

In the test code, I am inserting a record into the EmployeeMaster table:
  1. static void Main(string[] args)  
  2. {  
  3.     using (EntitiesContext  context = new EntitiesContext())  
  4.     {  
  5.         EmployeeMaster employee = new EmployeeMaster();  
  6.         employee.Code = "A0001";  
  7.         employee.Name = "Jignesh Trivedi";  
  8.         employee.DepartmentId = 1;  
  9.         context.Employees.Add(employee);  
  10.         context.SaveChanges();  
  11.         Console.ReadLine();  
  12.     }  
  13. }  
The Interception/SQL logging feature is introduced in Entity Framework 6. Entity Framework, sends commands (or an equivalent SQL query) to the database to do a CRUD operation and this command can be intercepted by application code of Entity Framework. This feature of the Entity Framework is to capture an equivalent SQL query generated by Entity Framework internally and provide it as output. The following code can be used to send output to the console.
  1. public EntitiesContext() : base("name=Entities")  
  2. {  
  3.     Database.Log = Console.WriteLine;  
  4. }  
The following is the SQL logging output of the code above:

SQL logging output
logging output

Summary

Using Entity Framework 6.0 and above, we can configure our code first model to use a Stored Procedure for a few or all entities of the model.