LINQ to SQL - Multiple result shapes

Working with LINQ to SQL, you might come across a situation where your stored procedure looks something like this:

CREATE PROCEDURE [dbo].[GetAllProductsAndCustomers]
   @CompanyID INT
AS 
SELECT [Code, Category] FROM Products
SELECT [Name, Email,Contact] FROM Customers

This SP returns multiple result sets which LINQ supports quite efficiently. However, If you work with auto-generated object relational mapper (*.dbml), you must have noticed that CLR cannot automatically determine which stored procedure returns multiple result shapes, hence creates a wrapper function with ISingleResult as a return type, which represents the result of a mapped function that has a single return sequence. For the above SP, it generated the below method signature:

<FunctionAttribute(Name:="GetAllProductsAndCustomers")> _
Public Function GetAllProductsAndCustomers(<Parameter(Name:="CompanyID", DbType:="Int")> ByVal CompanyID As System.Nullable(Of Integer)) As ISingleResult(Of GetAllProductsAndCustomersResult)
Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod,MethodInfo), companyID)
Return CType(result.ReturnValue,ISingleResult(Of GetAllProductsAndCustomersResult))
End Function

In order to turn this situation into our favor and handle multiple result shapes returned by the stored proc, all we need to do is replace the ISingleResult with IMultipleResults and supply the appropriate result types. If in case there is no specific result type, which is quite possible if stored proc is generating columns from multiple tables as a result of join, you can provide any names and LINQ will treat them as anonymous types. In the method signature below, I have created two classes GetAllProductsAndCustomersResult1 and GetAllProductsAndCustomersResult2 along with the properties Code and Category in the first and Name, Email, Contact in the second class.

One important thing, for the kind of SP we are using we need to read the result shapes in the same sequence as the SP returns the results. The order of IMultipleResults.GetResult() should be same as the order of SELECT statements in SP in order to avoid getting unexpected results or errors and exceptions if our IEnumerable result set is bound to a data source control.

The modified method signature will look like:

<FunctionAttribute(Name:="GetAllProductsAndCustomers"), _
ResultType(GetType(GetAllProductsAndCustomersResult1)), _
ResultType(GetType(GetAllProductsAndCustomersResult2))> _
Public Function GetAllProductsAndCustomers(<Parameter(Name:="DataBridgeQueueID", DbType:="Int")> ByVal companyID As System.Nullable(Of Integer)) As IMultipleResults
Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod,MethodInfo), companyID)
Return CType(result.ReturnValue,IMultipleResults)
End Function

HTH,

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
•