Output parameter in stored procedure in EF

Question

I have an existing database with lots of complex stored procedure and I want to use those procedure through EF 4. I have done the following:

Created an EF data object, Customer.
Added a Stored Procedure into the EF
Right Click on the EF designer and add a function import.
Function Import Name - MyFunction, complex type.

Resulting code:

CustomerEntities entity = new CustomerEntities();
var result = entity.MyFunction("XYZ", ref o_MyString);

Now my stored procedure has an output parameter which I used to call by the ref (in WebForm). But I am getting the below error:

cannot convert from 'ref string' to 'System.Data.Objects.ObjectParameter'

Please help

Edit

When I am trying to save I am getting the below error

A mapping function binding specifies a function Model.Store.P_GetCustomer with an unsupported parameter: o_MyString. Output parameters may only be mapped through the RowsAffectedParameter property. Use result bindings to return values from a function invocation.

score 36 · Accepted Answer · answered May 31 '11 at 20:52

36

Output parameters are returned in ObjectParameter instance. So you must use code like:

var oMyString = new ObjectParameter("o_MyString", typeof(string));
var result = ctx.MyFunction("XYZ", oMyString).ToList();
var data = oMyString.Value.ToString();

The reason is that function import cannot use ref parameter because output parameter is not filled until you process result set from the database = if you don't call ToList or iterate the result of the stored procedure the output parameter is null.

answered May 31 '11 at 20:52

Ladislav Mrnka

360,892
59
660
670

thanks for the answer. I tried the same But the issue still persist. I am able to run the application but when I going to the desired URL I am getting the same error message in edmx file. – Chris May 31 '11 at 21:02
What type is your output parameter in the stored procedure? – Ladislav Mrnka May 31 '11 at 21:06
It's a string, simply returns the insert, update status with table name and other related info. NVARCHAR(1000) – Chris May 31 '11 at 21:16
That is strange - it should work. Is it stored procedure mapped to insert or update of the entity? – Ladislav Mrnka May 31 '11 at 21:55
This may help: [link](http://weblogs.asp.net/dwahlin/archive/2011/09/23/using-entity-framework-code-first-with-stored-procedures-that-have-output-parameters.aspx) or this [link](http://msdn.microsoft.com/en-us/library/bb896334.aspx) – stormwild Nov 15 '11 at 13:00
Ladislav, what if I don't need an output resultset? I would like retrieve output parameter only, because in my case stored procedure doesn't return a resulset. Can I read output parameter value? – Rudolf Dvoracek Oct 22 '12 at 14:53
Exactly Rudolf. One of the most absurd things I've seen in the realm of ORM. If all I want is the output parameters, I shouldn't have to process the results set (when there isn't one). – Phillip Holmes Jul 14 '15 at 14:53

score 2 · Answer 2 · edited Feb 03 '16 at 16:17

msdn suggests the following:

CREATE PROCEDURE dbo.GetDepartmentName
     @ID INT ,
     @Name NVARCHAR(50) OUTPUT
AS
     SELECT   @Name = Name
     FROM     Department
     WHERE    DepartmentID = @ID

Solution

using (SchoolEntities context = new SchoolEntities())
{
   // name is an output parameter.

   ObjectParameter name = new ObjectParameter("Name", typeof(String));
   context.GetDepartmentName(1, name);
   Console.WriteLine(name.Value);
}

Output parameter in stored procedure in EF

2 Answers2

Linked