Wednesday, December 5, 2012

EF 4: How to execute stored procedure with Entity Framework Code First?


Problem

How to execute stored procedure with Entity Framework Code First?

Solution

This would execute a stored procedure which returns a single integer value as result
using (var db = new MyEntities())
{
 var param1 = new System.Data.SqlClient.SqlParameter("@param1", 1000);
 var param2 = new System.Data.SqlClient.SqlParameter("@param2", 2000);
 int? result = db.Database.SqlQuery("MySproc @param1, @param2", param1, param2).FirstOrDefault();
}

This would execute a stored procedure which returns a table data as result
using (var db = new MyEntities())
{
 var cmd = db.Database.Connection.CreateCommand();
 cmd.CommandText = "MyStoredProc @param1, @param2, @param3";
 cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@param1", 1300));
 cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@param2", 60));
 cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@param3", false));

 db.Database.Connection.Open();
 var reader = cmd.ExecuteReader();
 while (reader.Read())
 {
  var resultvalue1 = reader.GetValue(0);
  var resultvalue2 = reader.GetValue(1);
 }
 db.Database.Connection.Close();
}