The sproc in question accepts a single input parameter and returns two output scalar values and to my frustration I soon discovered that EF currently supports sproc calls that return an Entity Type, but not sproc that return scalar values.
So I again Googled with Bing to find a solution or workaround and discovered a few suggestions. A few posts such as this one suggest altering the sproc to return a simple entity e.g. SELECT @Id = SCOPE_IDENTITY() AS Id; Others suggest using the EFExtensions or upgrading to EF4 which apparently supports this directly.
The sproc is in a legacy database which I could not alter and the EFExtensions or upgrading to EF4 seemed like overkill to make a single simple sproc call.
I eventually settled on making the call directly as follows:
EntityConnection entityConnection = (EntityConnection)context.Connection;
DbConnection storeConnection = entityConnection.StoreConnection;
DbCommand command = storeConnection.CreateCommand();
command.CommandText = "MY_STORED_PROCEDURE";
command.CommandType = CommandType.StoredProcedure;
SqlParameter idParam = new SqlParameter("p_id", SqlDbType.Float);
idParam.Value = myInputValue;
SqlParameter treeParam = new SqlParameter("p_tree", SqlDbType.VarChar, -1);
treeParam.Direction = ParameterDirection.Output;
SqlParameter privateParam = new SqlParameter("p_private", SqlDbType.VarChar, -1);
privateParam.Direction = ParameterDirection.Output;
command.Parameters.Add(idParam);
command.Parameters.Add(treeParam);
command.Parameters.Add(privateParam);
DbDataReader reader = command.ExecuteReader();
Console.WriteLine(treeParam.Value);
Console.WriteLine(privateParam.Value);
I think a move to EF4 is required sooner rather than later...
