Walkthrough: Multiple Result Sets from Stored Procedure (June CTP)

The information in this post has been removed as it is out of date.

For Multiple Result Sets see https://msdn.com/data/jj691402.

Visit msdn.com/data/ef for the latest information on current and past releases of EF.

Comments

• Anonymous
  July 28, 2011
  How about doing this with code first? How can I use stored procedure with a complex type?

• Anonymous
  August 10, 2011
  Thanks for this helpful article, I have downloaded the EF v 4.1 and created new edmx file, and added stored procedure that map to 2 entity sets and it works fine, but it seem that in order to use this version we have to change the framework version from 4.0 to Microsoft Entity Framework June 2011 CTP. Now I want to deploy the application to a shared hosting server like GoDaddy, is it possible to do that?

• Anonymous
  September 07, 2011
  I agree that something like this would be nice for code-first.  I actually do currently fetch multiple result sets in a single roundtrip by transforming multiple DbContext LINQ queries to a string, running this through a SqlConnection / SqlCommand, then using a SqlDataReader with ObjectContext.Translate to populate the DbContext, but that's a little messy to code. Ideally, there would be an interface that would allow something like the following: var context = new DeferredDbContext<MyDbContext>(); context.Unicorns.Load();  // Deferred context.Rainbows.Load();  // Deferred context.Load(); // All deferred queries executed

• Anonymous
  September 07, 2011
  That would be good for us to be able retrieve rows from a second result set without calling GetNextResult. For example: var customers = db.FindCustomersWithRecentOrders("ALFKI"); var recentOrders = customers.SelectMany(x => x.Orders);

• Anonymous
  September 07, 2011
  ..or alternatively an ability to map some "not mapped" property to that extra result set. For example: Customer.RecentOrders will return orders from the second result set of type IEnumerable<Order> if there is any otherwise it will return null

• Anonymous
  October 04, 2011
  Did this feature not make it into the 4.1 release?

• Anonymous
  October 19, 2011
  Same question as Dave. Can someone provide an answer, please?

• Anonymous
  November 15, 2011
  @Dave Definitely NO. This feature won't make into EF 4.x release. It will make into next major release of EF 4, within the .NET 4.5.

• Anonymous
  December 07, 2011
  @Eriawan: Even this feature is not included in EF 4.2 which was released this month 1st November. We can expect this functionality in the necxt release of EF.

• Anonymous
  December 09, 2011
  Eriawan is right. Support for multiple results in stored procedures will be part of the release of EF that will come out when .NET 4.5 is released. Future pre-release versions of .NET 4.5 will include the feature, but any update we make to EF that is based on .NET 4.0 bits (e.g. 4.1, 4.2 or 4.3) will not support multiple results as the feature required changes to the core libraries of EF which are part of .NET. Please take a look at this blog post: blogs.msdn.com/.../how-we-talk-about-ef-and-its-future-versions.aspx for a more detailed explanation of EF versions and their relationship to the .NET framework.

• Anonymous
  December 09, 2011
  @Drammer & Chris Thoman: We agree it would be great to have support for multiple-results in Code First, both for stored procedures and for general queries and we have this in our feature backlog. We haven't completed a design yet though, and when this will happen is going to depend its relative priority. Please take advantage of http://ef.mswish.net/ for feature suggestions.

• Anonymous
  December 09, 2011
  @Konstantin: Thanks for your suggestions. If you want to provide more details, feel free to create a feature suggestion in http://ef.mswish.net or in Microsoft Connect.

• Anonymous
  December 12, 2011
  It would be great to have Database.SqlQuery supporting multiple record sets: public Tuple‹IEnumerable‹T1›, IEnumerable‹T2›› SqlQuery‹T1, T2›(string sql, params object[] parameters) public Tuple‹IEnumerable‹T1›, IEnumerable‹T2›, IEnumerable‹T3›› SqlQuery‹T1, T2, T3›(string sql, params object[] parameters) etc.

• Anonymous
  December 13, 2011
  Let's say, you want to have a stored procedure that returns multiple customers and their respective recent orders? Are there navigation-properties to get from the customer to his recent orders?

• Anonymous
  December 21, 2011
  Re: relations, this is just my personal opinion but I would use a single LINQ query with an anonymous type rather than multiple result sets.  Assuming Customer has an Orders property, I wouldn't want to reuse that for fear of overloading that property - rather I'd have something along the lines of Customers.Select(c => new { c.Id, c.Name, RecentOrders = c.Orders.OrderByDescending(o => o.Id).Take(10) }). The answer to your question, however, is that there are no navigation properties automatically created between one result set and another result set.

• Anonymous
  January 29, 2012
  The comment has been removed

• Anonymous
  March 05, 2012
  I know this post is a bit old, but I haven't seen anything written on this topic for EF 5 - Beta 1.  When returning multiple result sets is it possible to use entity types (as opposed to complex types) ?  I tried the example for complex types and it works well.  I have yet to correctly adapt it to Entity types.  Will this be supported ?

• Anonymous
  March 05, 2012
  @Eric Barr: yes, both complex types and entity types work with multiple results. The reason we haven't been focusing on this feature a lot in the blog is that although we have built-in support for it in our core libraries we ended up not having support for it in either the Entity Designer or Code First for EF 5.0. This means the only way you can us it is by editing your model in XML form. This makes it very hard for most of our customers to actually take advantage of the features.

• Anonymous
  March 05, 2012
  @dpblogs - thanks for the response.  I understand the focus/lack-there-of --there is a lot new in EF 5 and not every feature makes the cut.  As someone who manually implemented MARS in EF 4.0 I'm still on the fence over whether I would rather (a) use direct ADO and Translate<MyEFObject>()  as I have in the past or (b) face this brave new world and edit the EDMX  manaully. Re, converting the above example to MARS filling Entities can you comment on my understanding? Step 7 - CDSL  - no need to create/modify complex types

• "FunctionImport" should read something like:    <FunctionImport name="MyMARS_EntityFunction">        <ReturnType Type="Collection(MyEFModel.EntityTypeForResultSetOne)"/>        <ReturnType Type="Collection(MyEFModel.EntityTypeForResultSetTwo)"/>       <Parameter Name="CustomerID" Mode="In" Type="string"/>   </FunctionImport> **Step 8 - MSL ** Note that I guessed and modified "ComplexTypeMapping" to "EntityTypeMapping".  This step is where it seems to break down:      <FunctionImportMapping FunctionImportName=" MyMARS_EntityFunction"                                                 FunctionName="MyEFModel .Store. MyMARS_EntityFunction ">        <ResultMapping>          <EntityTypeMapping TypeName=" MyEFModel.EntityTypeForResultSetOne">            <ScalarProperty Name="CustomerID" ColumnName="CustomerID" />            <ScalarProperty Name="CompanyName" ColumnName="CompanyName" />            <ScalarProperty Name="ContactName" ColumnName="ContactName" />            <ScalarProperty Name="Phone" ColumnName="Phone" />          </ EntityTypeMapping  >        </ResultMapping>        <ResultMapping>          < EntityTypeMapping  TypeName=" MyEFModel.EntityTypeForResultSetTwo ">            <ScalarProperty Name="OrderID" ColumnName="OrderID" />            <ScalarProperty Name="OrderDate" ColumnName="OrderDate" />            <ScalarProperty Name="RequiredDate" ColumnName="RequiredDate" />            <ScalarProperty Name="ShippedDate" ColumnName="ShippedDate" />          </ EntityTypeMapping  >        </ResultMapping>      </FunctionImportMapping>

• Anonymous
  August 14, 2012
  I am using Code First in a project. The project is half done. I need to call a stored procedure to return multiple results (3 selects). My research tells me that it is not supported in Code First. What options do I have? Changing the project to use Model first?

• Anonymous
  August 15, 2012
  @Frank - Great question, I hadn't tried this before but you actually can do this with Code First. So I blogged about it - romiller.com/.../code-first-stored-procedures-with-multiple-results