Call stored procedures with Entity Framework 6 code first approach in Asp.Net MVC 5

Question

Call stored procedures with Entity Framework 6 code first approach in Asp.Net MVC 5

asp.net-mvc c# ef-code-first entity-framework-6 stored-procedures

Question

We are using Ado.Net for working with the database from our C# code in ASP.NET MVC. I want to work with Entity Framework, so I need to use stored procedures with it, call stored procedures from EF.

There are few examples how to use stored procedures with Entity Framework, but none specific for ASP.NET MVC and code-first (at least I couldn't find any).

They are a good start but I need something more, better information and better examples!

I have this stored procedure:

Create Procedure spAddEmployee  
    @Name nvarchar(50),  
    @Gender nvarchar(20),  
    @Salary int,  
    @EmployeeId int Out  
as  
Begin  
    Insert into tblEmployees 
    values(@Name, @Gender, @Salary)  

    Select @EmployeeId = SCOPE_IDENTITY()  
 End

So @Name, @Salary, @Gender are input parameters, and @EmployeeId is an output parameter, it returns the ID of the newly added employee.

Can someone tell me how to use Entity Framework (code-first) to call this stored procedure with the parameters?

arianit ax

View more on Stack Overflow

Valkyrie · Accepted Answer · 12/27/2016 9:04:16 AM

You can call a stored procedure in your DbContext class as follows.

this.Database.SqlQuery<YourEntityType>("storedProcedureName",params);

Update:

This is an Example how to call your SP in ActionResult:

public ActionResult ExecuteProcedure()
{
   using(var  db = new CueEntities())
   {
     var parameter = 1;
     var query =  db.Database.SqlQuery<TestProcedure>("TestProcedure @parameter1", 
                    new  SqlParameter("@parameter1", parameter)).ToList();          
        return Json(query,JsonRequestBehavior.AllowGet);     
    }
}

Second Update:

For Multiple Params you can easily go like this:

var param1 = new SqlParameter(); 
param1.ParameterName = "@Value1"; 
param1.SqlDbType = SqlDbType.Int; 
param1.SqlValue = val1;

var param2 = new SqlParameter(); 
param2.ParameterName = "@Value2"; 
param2.SqlDbType = SqlDbType.NVarChar; 
param2.SqlValue = val2;

var result = db.tablename.SqlQuery("SP_Name @Value1,@Value2", param1, param2 ).ToList();

Call stored procedures with Entity Framework 6 code first approach in Asp.Net MVC 5

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related