Using LINQ to SQL to return Multiple Results

In the LINQ in Action forum, a user asked about returning multiple result sets from a single stored procedure. Below is one way of dealing with this issue.

In the procedure, we used multiple results rather than a result with a return value (through RETURN or an OUTPUT parameter). Here we need to use the IMultipleResult rather than the default ISingleResult implementation. It appears that the designer does not map IMultipleResult in the final build, so we are going to need to do it ourselves. We mention this interface in chapter 8 but didn't have a chance to include a sample. Here's a sample implementation on returning the Subjects and Books from the Book sample database. First the stored proc:

CREATE PROCEDURE dbo.GetSubjectAndBooks
AS

 Select * from subject
 
 IF @@RowCount>0 BEGIN
  Select * from Book
 END

Now for the function mapping. We want to create a function that can return both the Subjects and the Books. To do this, we will create a function that returns the MultipleResult. Simlar to the standard stored procedure mapping, you create a function in a custom partial for the DataContext. The function will return a value of type IMultipleResults. Decorate the function with the FunctionAttribute including the name of the function. Here's the implementation in VB:

    <FunctionAttribute(Name:="dbo.GetSubjectAndBooks")> _
    <ResultType(GetType(Book))> _
    <ResultType(GetType(Subject))> _
    Public Function GetSubjectAndBooks() As IMultipleResults
        Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo))
        Dim results As IMultipleResults = DirectCast(result.ReturnValue, IMultipleResults)
        Return results
    End Function

Notice, the main difference here is the addition of two attributes identifying the possible ResultTypes (Book and Subject). The rest of the function should be self explanitory. To consume the function, we call the GetResult method of IMultipleResults passing in the generic type we want to return as follows:

    Dim context As New LinqBooksDataContext

    ObjectDumper.Write(context.GetSubjectAndBooks.GetResult(Of Subject))
    ObjectDumper.Write(context.GetSubjectAndBooks.GetResult(Of Book))