Entity Framework 4 - How to Read Multiple Record Sets From A Stored Procedure?

Question

Hi Guys,

I have a very simple stored procedure which returns multiple record sets. All of the record sets have aliased columns so they all look the same.

E.g.

    SELECT TOP 10 FooId AS Id, Name As Name FROM Foos
    SELECT TOP 10 BarId AS Id, Name As Name FROM Bars
         ...

For my EF setup, i'm using POCOs and have my own DataContext (no code-generation).

Now, i have created a "Function Import" using the technique detailed here.

But the problem is, it's creating a complex type with Id and Name, not a type that can hold multiple collections of Id and Name. Can EF not detect that i am returning multiple record sets?

So the stored proc gets executed properly, but the only records that come back are from the 1st select statement, the other ones are discarded. So i only get back 10 records.

Here's how im executing the SPROC in my custom DataContext:

public ObjectResult<SomeSimpleProc_Result> GetSomeStuff()
    {
        return base.ExecuteFunction<SomeSimpleProc_Result>("SomeSimpleProc);
    }

And the Return Result POCO:

public class SomeSimpleProc_Result
    {
        #region Primitive Properties

        public int Id
        {
            get;
            set;
        }

        public string Name
        {
            get;
            set;
        }

        #endregion
    }

The end result is i want to an object which has 0-* objects in it (in the above case, 3 objects). Each object should have a set of simple objects in it (Id, Name).

I think the problem is definitely with the customization of the "Function Import". How should i be creating the complex type? Or should i be using "Returns a collection of Entities".

Any ideas?

Answer 1

I think multiple resut sets are not supported out of the box. Here is a blog post about using them in EF v1. EF v4 also doesn't support them directly - comments in this article contains following statement by Danny Simmons (he used to be dev manager for EF and Linq-To-Sql):

Unfortunately we weren’t able to get full support for multiple results into the product this time around. We did, however, add the method Translate to ObjectContext which allows you to materialize objects from a DataReader. ...