EF Code First:Executing Stored Procedure

Code First in Entity Framework does not support Stored Procedure by default. We can not even map our Stored procedure to Entity. There are a many scenario we have seen where we are bound to use stored procedure for any database modifications (insert/update/delete). Here is how we can use stored procedure. 

Here i have created two Entities 

 public class Employee  
   {  
     public int Id { get; set; }  
     public string Name { get; set; }  
   }  
   public class Address:Employee  
   {  
     public string Address1 { get; set; }  
     public string Address2 { get; set; }  
     public string City { get; set; }  
     public int Pin { get; set; }  
   }  

-------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

Now we create EmployeeDBContext 

 public class EmployeeContext : DbContext  
   {  
     public DbSet<Employee> EmployeeSet { get; set; }  
     public DbSet<Address> Address { get; set; }  
     public void AddEmp(Address Emp)  
     {  
       this.Database.ExecuteSqlCommand("exec AddEmpData @Name,@Address1,@Address2,@City,@pin,@Discriminator",  
               new SqlParameter("@Name", Emp.Name),  
               new SqlParameter("@Address1",Emp.Address1),  
               new SqlParameter("@Address2", Emp.Address2),  
               new SqlParameter("@City", Emp.City),  
               new SqlParameter("@Pin", Emp.Pin),  
               new SqlParameter("@Discriminator", "Address"));  
     }  
     protected override void OnModelCreating(DbModelBuilder modelBuilder)  
     {  
       modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();  
     }  
   }  

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Text;  
 using System.Data.Entity;  
 namespace Calling_SP  
 {  
   class Program  
   {  
     static void Main(string[] args)  
     {  
       var Address = new Address  
       {  
         Name="Nirbhay",  
         Address1 = "Wakad-1",  
         Address2 = "Silver Society",  
         City = "pune",  
         Pin = 411010  
       };  
       //Save Data to database        
       using (var context = new EmployeeContext())  
       {  
         context.AddEmp(Address);  
       }  
       Console.Write("Person saved !");  
       Console.ReadLine();  
        //Retrieving data from Database  
       using (var context = new EmployeeContext())  
       {  
         var result = context.Address.SqlQuery("GetEmpData").ToList<Address>();  
         foreach (var item in result)  
         {  
           Console.Write(item.Id);  
           Console.Write(item.Name);  
           Console.Write(item.Address1);  
           Console.Write(item.Address2);  
           Console.Write(item.City);  
           Console.Write(item.Pin);  
           Console.WriteLine();  
         }  
       }  
       Console.ReadLine();  
     }  
   }  
 }  

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

In App.Config set the desired connection string

 <?xml version="1.0" encoding="utf-8" ?>  
 <configuration>  
  <connectionStrings>  
   <add name="EmployeeContext" connectionString="data source=SY113;initial catalog=Employee;integrated security=True;" providerName="System.Data.SqlClient"/>  
  </connectionStrings>  
 </configuration>  

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Stored Procedure

 USE [Employee]  
 GO  
 /****** Object: StoredProcedure [dbo].[AddEmpData]  Script Date: 05/23/2012 12:27:49 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 CREATE procedure [dbo].[AddEmpData]  
 @Name nvarchar(max),  
 @Address1 nvarchar(max),  
 @Address2 nvarchar(max),  
 @City nvarchar(max),  
 @Pin int,  
 @Discriminator nvarchar(128)  
 AS  
 Begin  
 insert into Employee (Name,Address1,Address2,City,Pin,Discriminator)  
 values  
 (@Name,@Address1,@Address2,@City,@Pin,@Discriminator)  
 End  
 GO  
 *********************************************************************************************  
 USE [Employee]  
 GO  
 /****** Object: StoredProcedure [dbo].[GetEmpData]  Script Date: 05/23/2012 12:28:34 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 Create procedure [dbo].[GetEmpData]  
 AS  
 Begin  
 select * from Employee  
 End  
 GO  

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Following are the 2 tables Created By EF.

You can see in the above DB Diagram there is a field Called Discriminator, it is Created by EF because we have inherited Employee Class in Address and the value for this field is Address.