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

See this 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"))  
            );  
    }  
}  

Enable Migration

Run this command:

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

Migration Configuration

Add-Migration -configuration CodeFirstStoredProcedure.EntitiesMigrations.Configuration InitialEntities

The add migration command generates a Dbmigration class. This DB Migration class has the 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");  
    }  
}  

Update Database

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

Update database command creates tables and Stored Procedure and definition of the Stored Procedure is as the following:

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 = "Roland Baltimore ";  
        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 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;  
}  

Then, run it and check the input