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

Question

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

If you're using entity framework you really should have a clean domain model. What type of things are you trying to load into your model where it requires this strange double output procedure? Was this made to handle some sort of performance constraint? — JDPeckham, Jul 01 '12 at 20:48
@JDPeckham - Not particularly strange IMO. The output parameters contains some header data about the result and the result set contains details rows. The stored procedure result set contains some quite complicated logic that I don't want to run twice and Entity Framework couldn't do efficiently. So is this possible or not? (In the meantime I'm just using ADO.NET and a DataReader but as it can do one or the other I would like to know if it's possible to do both) — MS., Jul 01 '12 at 22:59

score 4 · Accepted Answer · edited May 23 '17 at 10:34

4

According to this SO post, the sproc doesn't actually execute until you iterate the resultset. I simulated your scenario, ran some tests and confirmed this is the case. You didn't add a code sample, so I can't see what you're doing exactly, but as per your response below, try caching the resulset in a list (eg, Context.MyEntities.ToList()) and then check the value of the ObjectParameter

edited May 23 '17 at 10:34

Community

1
1

answered Jul 01 '12 at 18:15

ssis_ssiSucks

1,476
1
12
11

I don't enumerate the result set myself. That all happens within the [`base.ExecuteFunction(...)`](http://msdn.microsoft.com/en-us/library/bb739018.aspx) method that iterates through it and returns a collection of strongly typed objects as per the mappings in the designer. – MS. Jul 01 '12 at 18:18
Yes that was the problem. I was doing something like `var s = context.MySp(param1); var p = param1.Value; return s;`. This has fixed the issue. Thanks! – MS. Jul 03 '12 at 09:02

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

1 Answers1