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();
}