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);
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?
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
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.