Code-First Stored Procedure Entity 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.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<yourEntity>().MapToStoredProcedures();
}

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. Another method takes an action method as 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.

public class DepartmentMaster  
{  
    [Key]  
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]   
    public int DepartmentId { get; set; }  
    public string Code { get; set; }  
    public string Name { get; set; }  
    public List<EmployeeMaster> Employees { get; set; }  
}  
  
public class EmployeeMaster  
{  
    [Key]  
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]   
    public int EmployeeId { get; set; }  
    public string Code { get; set; }  
    public string Name { get; set; }  
    public int DepartmentId { get; set; }  
    public DepartmentMaster Department { get; set; }  
}  

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.

public class EntitiesContext : DbContext  
{  
    public EntitiesContext() : base("name=Entities")  
    {  
    }  

    public DbSet<DepartmentMaster> Departments { get; set; }  
    public DbSet<EmployeeMaster> Employees { get; set; }  
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)  
    {  
        modelBuilder.Entity<EmployeeMaster>()  
            .MapToStoredProcedures(s => s.Insert(u => u.HasName("InsertEmployee", "dbo"))  
                                         .Update(u => u.HasName("UpdateEmployee", "dbo"))  
                                         .Delete(u => u.HasName("DeleteEmployee", "dbo")));  
    }  
}  

Now I run the migration steps.

Step 1. Enable Migration

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

Step 2. Add-Migration Configuration

Add-Migration -configuration CodeFirstStoredProcedure.EntitiesMigrations.Configuration InitialEntities

The add migration command generates a Dbmigration class. This DB Migration class has a definition for all the Stored Procedures.

public partial class InitialEntities : DbMigration  
{  
    public override void Up()  
    {  
        CreateStoredProcedure(  
            "dbo.InsertEmployee",  
            p => new  
            {  
                Code = p.String(),  
                Name = p.String(),  
                DepartmentId = p.Int(),  
            },  
            body:  
                @"INSERT [dbo].[EmployeeMasters]([Code], [Name], [DepartmentId])  
                VALUES (@Code, @Name, @DepartmentId)  
                
                DECLARE @EmployeeId int  
                SELECT @EmployeeId = [EmployeeId]  
                FROM [dbo].[EmployeeMasters]  
                WHERE @@ROWCOUNT > 0 AND [EmployeeId] = scope_identity()  
                
                SELECT t0.[EmployeeId]  
                FROM [dbo].[EmployeeMasters] AS t0  
                WHERE @@ROWCOUNT > 0 AND t0.[EmployeeId] = @EmployeeId"  
        );  

        CreateStoredProcedure(  
            "dbo.UpdateEmployee",  
            p => new  
            {  
                EmployeeId = p.Int(),  
                Code = p.String(),  
                Name = p.String(),  
                DepartmentId = p.Int(),  
            },  
            body:  
                @"UPDATE [dbo].[EmployeeMasters]  
                SET [Code] = @Code, [Name] = @Name, [DepartmentId] = @DepartmentId  
                WHERE ([EmployeeId] = @EmployeeId)"  
        );  

        CreateStoredProcedure(  
            "dbo.DeleteEmployee",  
            p => new  
            {  
                EmployeeId = p.Int(),  
            },  
            body:  
                @"DELETE [dbo].[EmployeeMasters]  
                WHERE ([EmployeeId] = @EmployeeId)"  
        );  
    }  

    public override void Down()  
    {  
        DropStoredProcedure("dbo.DeleteEmployee");  
        DropStoredProcedure("dbo.UpdateEmployee");  
        DropStoredProcedure("dbo.InsertEmployee");  
    }  
}  

Step 3. Update-Database

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

Update database command creates tables and Stored Procedure and the definition of the Stored Procedure are as follows.

CREATE PROCEDURE [dbo].[InsertEmployee]  
    @Code [nvarchar](max),  
    @Name [nvarchar](max),  
    @DepartmentId [int]  
AS  
BEGIN  
    INSERT [dbo].[EmployeeMasters]([Code], [Name], [DepartmentId])  
    VALUES (@Code, @Name, @DepartmentId)  
    
    DECLARE @EmployeeId int  
    SELECT @EmployeeId = [EmployeeId]  
    FROM [dbo].[EmployeeMasters]  
    WHERE @@ROWCOUNT > 0 AND [EmployeeId] = scope_identity()  
    
    SELECT t0.[EmployeeId]  
    FROM [dbo].[EmployeeMasters] AS t0  
    WHERE @@ROWCOUNT > 0 AND t0.[EmployeeId] = @EmployeeId  
END  

GO  

CREATE PROCEDURE [dbo].[UpdateEmployee]  
    @EmployeeId [int],  
    @Code [nvarchar](max),  
    @Name [nvarchar](max),  
    @DepartmentId [int]  
AS  
BEGIN  
    UPDATE [dbo].[EmployeeMasters]  
    SET [Code] = @Code, [Name] = @Name, [DepartmentId] = @DepartmentId  
    WHERE ([EmployeeId] = @EmployeeId)  
END  

GO  

CREATE PROCEDURE [dbo].[DeleteEmployee]  
    @EmployeeId [int]  
AS  
BEGIN  
    DELETE [dbo].[EmployeeMasters]  
    WHERE ([EmployeeId] = @EmployeeId)  
END  

Test Code

In the test code, I am inserting a record into the EmployeeMaster table.

static void Main(string[] args)
{
    using (EntitiesContext context = new EntitiesContext())
    {
        EmployeeMaster employee = new EmployeeMaster();
        employee.Code = "A0001";
        employee.Name = "Jignesh Trivedi";
        employee.DepartmentId = 1;
        context.Employees.Add(employee);
        context.SaveChanges();
        Console.ReadLine();
    }
}

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 the 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.

public EntitiesContext() : base("name=Entities")
{
    Database.Log = Console.WriteLine;
}

The following is the SQL logging output of the code above.

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.