Return Multiple Result Sets from Stored Procedures RRS feed

  • Question

  • Some of my stored procedures return multiple datasets.  For example, one stored procedure returns a Company dataset and Address, Phone, and EMail datasets associated with the company.

    I need to return these multiple result sets to a Silverlight application from a WCF Data Service/OData.  The method in the service has the [WebGet] attribute and can only return one entity.

    Vitek Karas on the WCF Data Service forum said I could create a new ComplexType that incorporates all 4 of the datasets mentioned above.  I tried modifying the .edmx file as follows:

    <Property Type="Int32" Name="CompanyId" Nullable="false" />
    <Property Type="String" Name="Name" Nullable="false" MaxLength="100" />
    <Property Type="CompanyModel.CompanyAddress" Name="Addresses" Nullable="false" />
    <Property Type="CompanyModel.CompanyPhone" Name="Phones" Nullable="false" />
    <Property Type="CompanyModel.CompanyEMail" Name="EMailAddresses" Nullable="false" />

    where the elipsis represents all the other fields in the Company ComplexType.

    When I build and execute my application, I get the following error:  "Nested ComplexType property 'Addresses' in the ReturnType 'ap_Company_Load_Result' of the function 'CompanyEntities.ap_Company_Load' is not supported, please consider flattening the nested ComplexType property."

    OData supports nested ComplexTypes as shown in this code:

    public class Company
        public string Name { get; set; }
        public List<Address> Addresses { get; set; }
    public class Address
        public string City { get; set; }

    What do I need to do for the Entity Framework to allow the nesting of ComplexTypes?



    Tuesday, December 18, 2012 5:17 PM

All replies

  • Hi Tom,

    A page on using multiple result sets with stored procs is here: http://msdn.microsoft.com/en-us/data/jj691402

    It's been too long since I've done this so I can't remember it all. Have a look through the walkthrough and if you can't get it to work then post again and I'll set it up on my machine so I can find out what the problem is.

    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Wednesday, December 19, 2012 10:01 PM
  • Glenn,

    I am able to access the result sets in my WCF Data Service on the server just like in the example but I still can't return the data to my Silverlight application.

    Silverlight only accepts a limited number of return types, e.g.  IEnumerable, IQueryable, etc.  Most examples show packaging the data in List<> because it implements the IEnumerable interface.  The problem with multiple result sets is that they have to be put in a single return type.  In my case, I have 4 Lists each of which contains one of my result sets but I need them in one container that can be cast to IEnumerable.

    How can I package my result sets so I can return the data to my Silverlight app?



    Thursday, December 20, 2012 3:56 PM
  • Hi,

    you don't need to put multiple records into a single record by using out parameter in your service method.

    refer this :

    In Contract :

    ServiceMessage Method1(out List<Person> person, out List<Address> address);

    In Service implementtion class :

    public ServiceMessage Method1(out List<Person> person, out List<Address> address)
    person = new List<Person>();
    address = new List<Address>();
    ...your logic goes here ..

    You can pass any many number of collections as you need using ths method.

    ServiceMessage type is just arbitrarily taken. You can create your own type.

    Let me know if you have any issue.

    One good question is equivalent to ten best answers.

    • Edited by Shyam Kr Thursday, December 20, 2012 4:14 PM
    Thursday, December 20, 2012 4:12 PM
  • bootstrap2,

    I hate to appear stupid but I don't have any contracts in my WCF Data Service.  I have never need one to return Entities to my Silverlight application.

    I created the service contract but my project is complaining about the return type.  Can you give me an example of how to define ServiceMessage?



    • Edited by TomBean Thursday, December 20, 2012 6:46 PM
    Thursday, December 20, 2012 5:55 PM
  • bootstrap2,

    I am calling my WCF Data Service from a Silverlight application with a URI.

    How do I add the 'out' parameters to a URI?



    Thursday, December 20, 2012 6:57 PM
  • Glenn,

    I finally realized I haven't explained my situation properly.  My stored procedure returns 4 result sets each of which is a ComplexType not an Entity.

    The article you referenced only deals with stored procedures returning multiple result sets of Entities.

    I tried adding my ComplexType result sets 2, 3, and 4 as members of the first result set but got an error saying "Nested ComplexType property 'Addresses' in the ReturnType 'ap_Company_Load_Result' of the function 'CompanyEntities.ap_Company_Load' is not supported, please consider flattening the nested ComplexType property."

    I can't flatten result sets 2, 3, and 4 because they contain multiple records.

    Is there any way to add result sets 2, 3, and 4 to the first result set so I can return all the data from the stored procedure?



    Friday, December 21, 2012 7:42 PM
  • Hi,

    I test to use Complex Type to receive the result set but failed. It seems like it requires a EntitySet, but I am not sure how to do this in code first. I guess it is not possible to receive complex type in code first. If you find something, please share with me.

    Wednesday, December 26, 2012 8:47 AM