LINQ to SQL Stored Procedures that Return Sequential Result Shapes using C#

The SQL stored procedures may also return multiple types of result sets sequentially. LINQ to SQL also provides the feature to handle the multiple result shapes returned by the stored procedures where it is known that the procedure will return different result shapes one by one sequentially. The IMultipleResults interface enables to fetch and represent the results of the mapped function or query with variable result sequences. To understand the concept of LINQ to SQL Stored Procedure that returns multiple results shapes using C# code we have created a stored procedure in the Northwind SQL database. It accepts no input parameter and returns the resulting rows from two database tables.

Stored Procedure

CREATE PROCEDURE sproc_SelectProductsAndCategories
AS
SELECT   CategoryID, 
    CategoryName, 
    Description, 
    Picture 
FROM   Categories

SELECT   ProductID,  
    ProductName,
    SupplierID,
    CategoryID,
    QuantityPerUnit,
    UnitPrice,
    UnitsInStock,
    UnitsOnOrder,
    ReorderLevel,
    Discontinued
FROM   Products

The above stored procedure will return two result sets, the first result set will return all the categories and the second result set will return all the products from the products table. LINQ to SQL O/R mapper tool does not generate the automated code for multiple result shapes but you can create its LINQ based code by extending the DataContext partial class manually. The following C# code shows the partial DataContext class and the function for implementing the IMultipleResults interface to manage the multiple types of result sets that the underlying stored procedure will return:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

// namespace for accessing MethodInfo class
using System.Reflection;

// namespace for accessing LINQ methods
// and LINQ mapping
using System.Data.Linq;
using System.Data.Linq.Mapping;

/// <summary>
/// Summary description for LinqDataContextClass
/// </summary>
public partial class DataClassesDataContext
{
    [Function(Name = "dbo.sproc_SelectProductsAndCategories")]
    [ResultType(typeof(Category))]
    [ResultType(typeof(Product))]
    public IMultipleResults SelectProductsAndCategories()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod());
        return (IMultipleResults)result.ReturnValue;
    }
}

The following C# code can be used to call the store procedure using LINQ that will retrieve and return the result sets sequentially:

public void GetProductsAndCategories()
{
    DataClassesDataContext dc = new DataClassesDataContext();
    IMultipleResults results = dc.SelectProductsAndCategories();

    foreach (Category category in results.GetResult<Category>())
    {
        Response.Output.Write("{0}<br />", category.CategoryName);
    }

    foreach (Product product in results.GetResult<Product>())
    {
        Response.Output.Write("{0}<br />", product.ProductName);
    }
}

The IMultipleResults itself does not provide Enumerable object instead it provides a GetResult<T> method that enables to retrieve the specified type of sequence from the result set.

Continue to the next tutorial: LINQ to SQL Stored Procedures to Insert, Update and Delete using C# to learn how to implement create, update and delete functionality using LINQ with stored procedures.