Entity Framework with Output parameters

Question

I have a stored procedure with one output parameter. The procedure returns an Entity. I do the function import and eveything and the entity is returning fine. However the output parameter value is not being updated. I searched everywhere and I still am not sure if output parameters are supported by entity framework or not. I appreciate if someone could help me with these questions:

1) After function import, the function signature creates an ObjectParameter for the output parameter, but the rest of In parameters are generated as regular types (like string , int,...). Why is EF distinguishing between the two?
2) Does EF support output paramenters or not?
3) Does EF 4.0 Beta support output parameters?


The way it is right now it seems that I can never read a return value from a stored  procedures or get the value of an Output parameter updated.

Thanks

Answer 1

Use exactly the same variable name for your ObjectParameter in your imported function's signature.


ObjectParameter myParameter = new ObjectParameter("MyParameter ", typeof(int));

From edmx:

<Function Name="MyFunction" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo"><br/>
    <Parameter Name="MyParameter" Type="int" Mode="InOut" /><br/>
</Function>

From .designer.cs:

public global::System.Data.Objects.ObjectResult<MyEntityType> SearchMyEntity(global::System.Data.Objects.ObjectParameter myParameter)

Cankut

Answer 2

Thanks Cankut, but unfortunately it did not work for me.


Is it something that is working for you?

Answer 3

Sorry my mistake, nothing to do with variable names.

But the parameter name you declared in SP should be equal to name you use in the ObjectParameter.

My SP parameter was declared as @TotalRecordCount in the database.

On the code side it was

ObjectParameter prm = new ObjectParameter("totalRecordCount ", typeof(int));

The output parameter was not updated in this case.

When we change totalRecordCount -> TotalRecordCount it started to work.



Check your SP script and make sure that you use the same name in the ObjectParameter.

SP:
CREATE PROCEDURE  [dbo].[MyFunction] (
    @TotalRecordCount int = 0 output
)

ObjectParameter prm = new ObjectParameter("TotalRecordCount", typeof(int));



Hope this helps,

Cankut

Answer 4

Thanks. It is still not working for me.

Answer 5

i have stopped doing EF v1 for a while so i cant say much in this but i can tell u the story for what i tried in v4 that worked for me. I have a stored procedure that returns resultset as well output paramter. suppose the method call generated on the objectcontex is like this

var parameter = new ObjectParameter("TotalCustomer", ParamterType.Int) .. (something like that)
var custs = db.GetCusts(paramter);

foreach(var cust in custs)
{
--do someitng
}

int totalcusts = paramter.Value as int;

the point is u have to iterate through the resultset first before u can get the value back for output paramter.

Zeeshan hirani

Answer 6

Hi, Ijust did the following and it worked:

Added the following sporc to my SQL Server:

CREATE PROCEDURE GetProductName

      @ID int,

      @Name nvarchar(50) OUTPUT

      AS

      SELECT @Name = Name FROM Production.Product

      WHERE ProductID = @ID

I imported the function. The following code returns the value in the output param.

                    ObjectParameter id = new ObjectParameter("ID", 1);

                    ObjectParameter name = new ObjectParameter("Name", typeof(String));

 

                    context.GetProductName(productID, name);

                    Console.WriteLine(name.Value);

 

---

This posting is provided "AS IS" with no warranties, and confers no rights.