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

WCF Oracle adapter: Field xxx has a value with time zone information set. Specify a value without time zone

Problem

I tried to call a Oracle stored procedure from orchestration with WCF Oracle Adapter.

My source message contains a date element like this <deliveryDate>2010-03-17T16:20:34.252125+02:00</deliveryDate> and when I put that value to destination message which is sent to Oracle: <DELIVERYDATE>2010-03-15T16:20:34.2365000Z</DELIVERYDATE>

I got an error "Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: Field DELIVERYDATE has a value with time zone information set. Specify a value without time zone.


Solution
Destination messsage date value should be modified to XML date format without timezone:

DateTime date = XmlConvert.ToDateTime(xmldate, XmlDateTimeSerializationMode.Unspecified);
DestinationElementValue = date.ToString("yyyy-MM-ddThh:mm:ss"));

Looping in stored procedure

Problem
How to loop through the select result in a stored procedure

Solution
DECLARE @MyId int
DECLARE Scroller SCROLL CURSOR
FOR SELECT MyId FROM MyTable

OPEN Scroller
FETCH NEXT FROM Scroller INTO @MyId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO MyTable2 (ReferenceId) VALUES (@MyId)
FETCH NEXT FROM Scroller INTO @MyId
END

CLOSE Scroller
DEALLOCATE Scroller