Executing a stored procedure that returns a result set and has output parameters with Entity Framework

c# entity-framework entity-framework-6 stored-procedures

Question

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?

1
1
3/2/2015 8:20:03 PM

Accepted Answer

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:

enter image description here

I don't see this mentioned in the actual documentation on msdn, and I'm not sure why.

https://msdn.microsoft.com/en-us/library/bb739018(v=vs.110).aspx

1
3/3/2015 7:20:28 PM

Popular Answer

I apologize, as I am a little bit confused by your problem.

Let's say you do this before executing your stored procedures.

   SqlCommandBuilder.DeriveParameters(_sqlCommand);

This would put all of the parameters in you sql command object. After executing,

   _sqlCommand.ExecuteNonQuery();

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow