Q&A: How to return multiple result sets from a stored procedure using LINQ

This one has come up for both LINQ to SQL and LINQ to Entities. The scenario is a reasonably common one. How to handle something like this:

CREATE PROCEDURE [dbo].[ReturnMultipleRS]
select * from customers
select * from products


The solution is to add a method to the DataContext using a partial class. Thankfully the Swiss MSDN team have already explained this one in great detail. Enjoy.

LINQ to Entities:

The easiest approach is to download the ADO.NET Entity Framework Extensions which enables multiple result set support. The download is tiny but still includes a sample project which demonstrates (among other things) working with multiple result sets to return categories and products.

   1: /// <summary>
   2: /// Gets a category with the requested ID and populates related products using
   3: /// a single stored procedure call. The procedure returns multiple result sets:
   4: /// the first set returns the category and the second set returns related products.
   5: /// </summary>
   6: /// <param name=”categoryID”>Category ID.</param>
   7: /// <returns>Category with given ID or null if none is found.</returns>
   8: public Category GetCategoryAndRelatedProducts(int categoryID) {
   9:     DbCommand command = this.CreateStoreCommand(“GetCategoryAndProducts”, CommandType.StoredProcedure, new SqlParameter(“cid”, categoryID));
  10:     Category category;
  12:     using (command.Connection.CreateConnectionScope())
  13:     using (DbDataReader reader = command.ExecuteReader()) {
  14:         // first result set includes the category
  15:         category = s_categoryMaterializer
  16:             .Materialize(reader)
  17:             .Bind(this.CategorySet)
  18:             .SingleOrDefault();
  20:         // second result set includes the related products
  21:         if (null != category && reader.NextResult()) {
  22:             category.Products.Attach(s_productMaterializer
  23:                 .Materialize(reader)
  24:                 .Bind(this.ProductSet));
  25:         }
  26:     }
  28:     return category;
  29: }