Calling stored procedure with parameters

Question

I have a stored procedure that returns a value, not a dataset, and I have problems getting it to work with EF4.

I've seen this: http://dotnet.dzone.com/news/how-retrieve-stored-procedure

Here is what I did: I added a procedure to the model, and imported a function.

The first issue that I had was the fact that parameters in my procedure are named like @_Parameter_in. That made EF to bring them in as p_Parameter_in because it wouldn't work with an underscore as the first character. Then when I called the stored procedure, I can see in the SQL Profiler call that it looks for @p_Parameter_in, and of course there was an issue with that.

Now I renamed the parameters and looked into SQL Trace - everything looks and works great. The problem is that I can't get the value out. Here is how my code looks:

System.Data.Objects.ObjectParameter newKey = new System.Data.Objects.ObjectParameter("NewKey_out", typeof(Int32));
newKey.Value = 0;
context.GetNextSurrogateKey_v2("tTest", newKey);

After calling newKey.Value, it's always 0 or whatever value I set it to. It doesn't bring the value back. I suspect my issue is with how I import the function. I use Scalars and data type Int32. "Create new Complex type" is disabled for me for some reason. Anybody had that issue?

Answer 1

I'm guessing here since I can't see the stored procedure that you're calling. I think you're wanting to retrieve a scalar value that has been returned by the stored procedure, rather than the value of an output parameter as defined in the stored procedure.

I believe you want to define a new Function within the EDM and point this at the stored procedure. A quick google has this potential solution: http://www.devtoolshed.com/using-stored-procedures-entity-framework-scalar-return-values

HTH

Answer 2

The problem is that EF process output parameters after datareader ends reading. That's usually after calling DataBind() function. I had same problem with longer processing procedures. I solve this by using .ToList() function on ObjectResult.

var rowsCount = new ObjectParameter("RowsCount", typeof(Int32));    
var result = db.GetProductList(pageSize, pageNumber, rowsCount).ToList();
this.ProductsCount = (int?)rowsCount.Value;

Single rows or values can you probably solve with FirstOrDefault() function.