Export (0) Print
Expand All

How to: Use Stored Procedures Mapped for Multiple Result Shapes (LINQ to SQL)

When a stored procedure can return multiple result shapes, the return type cannot be strongly typed to a single projection shape. Although LINQ to SQL can generate all possible projection types, it cannot know the order in which they will be returned.

Contrast this scenario with stored procedures that produce multiple result shapes sequentially. For more information, see How to: Use Stored Procedures Mapped for Sequential Result Shapes (LINQ to SQL).

The ResultTypeAttribute attribute is applied to stored procedures that return multiple result types to specify the set of types the procedure can return.

In the following SQL code example, the result shape depends on the input (shape =1 or shape = 2). You do not know which projection will be returned first.

CREATE PROCEDURE VariableResultShapes(@shape int)
AS
if(@shape = 1)
    select CustomerID, ContactTitle, CompanyName from customers
else if(@shape = 2)
    select OrderID, ShipName from orders
	[Function(Name="dbo.VariableResultShapes")]
	[ResultType(typeof(VariableResultShapesResult1))]
	[ResultType(typeof(VariableResultShapesResult2))]
	public IMultipleResults VariableResultShapes([Parameter(DbType="Int")] System.Nullable<int> shape)
	{
		IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), shape);
		return ((IMultipleResults)(result.ReturnValue));
	}

You would use code similar to the following to execute this stored procedure.

NoteNote:

You must use the GetResult<TElement> pattern to obtain an enumerator of the correct type, based on your knowledge of the stored procedure.

Northwnd db = new Northwnd(@"c:\northwnd.mdf");

// Assign the results of the procedure with an argument 
// of (1) to local variable 'result'.
IMultipleResults result = db.VariableResultShapes(1);

// Iterate through the list and write results (the company names) 
// to the console. 
foreach(VariableResultShapesResult1 compName in
    result.GetResult<VariableResultShapesResult1>())
{
    Console.WriteLine(compName.CompanyName);
}

// Pause to view company names; press Enter to continue.
Console.ReadLine();

// Assign the results of the procedure with an argument 
// of (2) to local variable 'result'.
IMultipleResults result2 = db.VariableResultShapes(2);

// Iterate through the list and write results (the order IDs) 
// to the console. 
foreach (VariableResultShapesResult2 ord in
    result2.GetResult<VariableResultShapesResult2>())
{
    Console.WriteLine(ord.OrderID);
}

Community Additions

ADD
Show:
© 2015 Microsoft