Entity Framework - Stored Procedures with Scalar Output Values

Yesterday was the time I needed to execute a Stored Procedure with EF4. My immediate thoughts were "no problem", I'll just pull in the Stored Procedure with the EF designer, which will create the FunctionImport and then I'll Google with Bing for the syntax to make the call and away we go...

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...