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

HostForLIFE.eu, a leading Windows web hosting provider with innovative technology solutions and a dedicated professional services team, today announced the support of SiteLock Malware Detector on all their newest hosting environments. HostForLIFE.eu Hosting with SiteLock Malware Detector plan starts from just as low as $36.00 / year only and this plan has supported daily malware scan, trust seal, application scan, TrueSpeed CDN, etc.

HostForLIFE.eu offers the greatest performance and flexibility hosting with SiteLock Malware Detector at an economical price. HostForLIFE.eu provides flexible hosting solutions that enable their company to give maximum uptime to customer. SiteLock monitors your website 24x7 for vulnerabilities and attacks, which means you can worry less about your website and more about your business.

SiteLock is a cloud-based, website security solution for small businesses. It works as an early detection alarm for common online threats like malware injections, bot attacks etc. It not only protects websites from potential online threats, but also fixes vulnerabilities. With the presence of SiteLock, your website will be protected and scanned against viruses, spyware, malware, identity theft and other online scams. Note that, as they rely more and more on internet technology, these online viruses and scams become bigger and smarter to handle.

Over 70% Customers look for a sign of security before providing personal details online. The SiteLock Trust Seal not only re-assures customers, but also boosts sales. The customer doesn’t need technical ability to install and set up SiteLock for their website. SiteLock is cloud-based and starts scanning website and email instantly. With this SiteLock feature, you can be assured that you will always be one step ahead of online hackers and swindlers’ illegal intentions. With more than 6 years in the web hosting business, HostForLIFE’s technical staff is more than ready on its feet to develop and tackle viruses, malware and the likes to sustain the safe and reliable use of your website.

Further information and the full range of features hosting with SiteLock Malware Detector can be viewed here http://www.hostforlife.eu/Hosting-with-Sitelock-Malware-Detector-in-Europe