I have a stored procedure written a few years ago that has output parameters and returns a result set. I'm trying to use it in EntityFramework. Initially, when I imported it, the generated code was using
ExecuteFunction() to execute the procedure. Using this method, the
ObjectParameter objects had the output parameter values. I then changed the imported procedure's definition in the model to output a complex type I defined in the model that matches the result set the stored procedure returns. The generated code was changed to execute the procedure using
ExecuteFunction<MyComplexType>(), and the procedure returns the result set now in an
IEnumerable<MyComplexType>. But my
ObjectParameter objects have the default values I set them to when I created them, not the output of the procedure.
I've created a second mapping of the function in my model that isn't set up to return the complex type. I'm currently executing the stored procedure twice via these two functions. The first execution doesn't return the complex type. It sets the
ObjectParameter values. I then pass these same
ObjectParameters to the second function and at the end of these two executions, I have the result data and the output of the stored procedure.
Am I doing something wrong? Or is this some limitation of Entity Framework 6?
It turns out that the output parameters aren't available until all results are read. I fixed my problem by appending
.ToList() to the tend of the
ExecuteFunction() call. I figured it out by browsing the metadata in Visual Studio. You can see the relevant documentation here:
I don't see this mentioned in the actual documentation on msdn, and I'm not sure why.
I apologize, as I am a little bit confused by your problem.
Let's say you do this before executing your stored procedures.
This would put all of the parameters in you sql command object. After executing,
Any parameter in you stored procedure that had OUTPUT clause would have a value in them, provided they get a value in the stored procedure. So you can just enumerate through them. Apologies if I missing something, which I think I am.