Code First Approach With Stored Procedure

Introduction

In this article we will see how to access a SQL Server database with the Entity Framework Code First approach and later we will see how to create a procedure using the Fluent API.

Step 1: Create console application

Migrations

Employee.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeFirstApproach_SPApp
{
   public class Employee
   {
       public Employee()
       {
       }

       [Key]
       [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
       public int Id { get; set; }
       public string FirstName { get; set; }
       public string LastName { get; set; }

    }
}

Employeecontext.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeFirstApproach_SPApp
{
    public class EmployeeContext : DbContext
    {
        public EmployeeContext()
            : base("EmployeeConn")
        {
            Database.SetInitializer<EmployeeContext>(new CreateDatabaseIfNotExists<EmployeeContext>());
        }

        public DbSet<Employee> Employees { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {

            modelBuilder.Entity<Employee>()
                .MapToStoredProcedures();
        }
    }
}

Web.config

<connectionStrings>
    <add name="EmployeeConn"
    connectionString="Data Source=WIN-B4KJ8JI75VF;Initial Catalog=EmployeeDB;Integrated Security=true"
    providerName="System.Data.SqlClient"/>
  </connectionStrings>

The output of the application looks as in this:

Summary

In this article we saw how to access a SQL Server database with the Entity Framework Code First approach and how to create a procedure using the Fluent API. Happy coding.