Accessing both stored procedure output parameters AND the result set in Entity Framework?
- by MS.
Is there any way of accessing both a result set and output parameters from a stored procedure added in as a function import in an Entity Framework model?
I am finding that if I set the return type to "None" such that the designer generated code ends up calling base.ExecuteFunction(...) that I can access the output parameters fine after calling the function (but of course not the result set).
Conversely if I set the return type in the designer to a collection of complex types then the designer generated code calls base.ExecuteFunction<T>(...) and the result set is returned as ObjectResult<T> but then the value property for the ObjectParameter instances is NULL rather than containing the proper value that I can see being passed back in Profiler.
I speculate the second method is perhaps calling a DataReader and not closing it. Is this a known issue? Any work arounds or alternative approaches?
Edit
My code currently looks like
public IEnumerable<FooBar> GetFooBars(
int? param1,
string param2,
DateTime from,
DateTime to,
out DateTime? createdDate,
out DateTime? deletedDate)
{
var createdDateParam = new ObjectParameter("CreatedDate", typeof(DateTime));
var deletedDateParam = new ObjectParameter("DeletedDate", typeof(DateTime));
var fooBars = MyContext.GetFooBars(param1, param2, from, to, createdDateParam, deletedDateParam);
createdDate = (DateTime?)(createdDateParam.Value == DBNull.Value ?
null :
createdDateParam.Value);
deletedDate = (DateTime?)(deletedDateParam.Value == DBNull.Value ?
null :
deletedDateParam.Value);
return fooBars;
}
