Accessing both stored procedure output parameters AND the result set in Entity Framework?

Posted by MS. on Stack Overflow See other posts from Stack Overflow or by MS.
Published on 2012-07-01T16:36:01Z Indexed on 2012/07/03 9:16 UTC
Read the original article Hit count: 168

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;
    }

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about .net-4.0