如何：使用为多个结果形状映射的存储过程 (LINQ to SQL)

更新：November 2007

当存储过程可以返回多个结果形状时，返回类型无法强类型化为单个投影形状。尽管 LINQ to SQL 可以生成所有可能的投影类型，但它无法获知将以何种顺序返回它们。

请将这种情况与按顺序产生多个结果形状的存储过程作一个对比。有关更多信息，请参见 如何：使用为顺序结果形状映射的存储过程 (LINQ to SQL)。

ResultTypeAttribute 属性适用于返回多个结果类型的存储过程，用以指定该过程可以返回的类型的集合。

示例

在下面的 SQL 代码示例中，结果形状取决于输入（shape =1 或 shape = 2）。您不知道将先返回哪个投影。

CREATE PROCEDURE VariableResultShapes(@shape int)
AS
if(@shape = 1)
    select CustomerID, ContactTitle, CompanyName from customers
else if(@shape = 2)
    select OrderID, ShipName from orders

<FunctionAttribute(Name:="dbo.VariableResultShapes"), _
ResultType(GetType(VariableResultShapesResult1)), _
ResultType(GetType(VariableResultShapesResult2))> _
Public Function VariableResultShapes(<Parameter(DbType:="Int")> ByVal shape As System.Nullable(Of Integer)) As IMultipleResults
    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), shape)
    Return CType(result.ReturnValue, IMultipleResults)
End Function

 [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));
    }

您将使用类似于以下内容的代码来执行此存储过程。

说明：
您必须根据您对存储过程的认识，使用 GetResult<TElement> 模式获取正确类型的枚举数。

Dim db As New Northwnd("c:\northwnd.mdf")

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

' Iterate through the list and write results (the company name)
' to the console.
For Each compName As VariableResultShapesResult1 _
    In result.GetResult(Of VariableResultShapesResult1)()
    Console.WriteLine(compName.CompanyName)
Next

' 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.'
Dim result2 As IMultipleResults = db.VariableResultShapes(2)

' Iterate through the list and write results (the order IDs)
' to the console.
For Each ord As VariableResultShapesResult2 _
    In result2.GetResult(Of VariableResultShapesResult2)()
    Console.WriteLine(ord.OrderID)
Next

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);
}

请参见

其他资源

存储过程 (LINQ to SQL)