Code-First Stored Procedure Entity Framework 6.0

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.