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

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

Step 3

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