This topic covers procedures that include explicit select statements. For such stored routines, mapping is most straight-forward. For those common with LINQ to SQL we can say that this type of procedures should be mapped exactly as it is done in LINQ to SQL. The following procedure illustrates this case (the procedure is written for SQL Server; if you use MySQL, you can keep the wrapper code, but the stored procedure syntax must be modified):

  
CREATE PROCEDURE GetEmpsByDeptId
  @id int
AS
BEGIN
  SELECT * FROM emp WHERE emp.deptno = @id;
END

Here is the corresponding wrapper method of DataContext.

[Function(Name=@"GetEmpsByDeptId")]
[ResultType(typeof(Emp), 0, ResultTypeOrigin.ExplicitSelect)]
public ISingleResult<Emp> GetEmpsByDeptId(
    [Parameter(Name="id", DbType="int")] int? id) {
 
    IExecuteResult _GetEmpsByDeptIdResult = this.ExecuteMethodCall(
        this,
        (MethodInfo)MethodInfo.GetCurrentMethod(),
        id
    );
    return (ISingleResult<Emp>)_GetEmpsByDeptIdResult.ReturnValue;
}

<[Function](Name:="GetEmpsByDeptId")> _
<ResultType(GetType(Emp), 0, ResultTypeOrigin.ExplicitSelect)> _
Public Function GetEmpsByDeptId( _
    <Parameter(Name:="id", DbType:="int")> id As System.Nullable(Of Integer)
) As ISingleResult(Of Emp)
 
    Dim _GetEmpsByDeptIdResult As IExecuteResult = _
        Me.ExecuteMethodCall(Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), id)
    Return DirectCast(_GetEmpsByDeptIdResult.ReturnValue, ISingleResult(Of Emp))
End Function

We show the ResultType attribute here for greater clarity, but in fact it isn't necessary. Runtime understands that we are working with SQL Server (after reading the ProviderAttribute or the corresponding element of XML mapping), and for SQL Server methods returning ISingleResult are considered as stored procedures with a SELECT statement inside by default. And the type of the collection is specified by the generic argument of ISingleResult.

However, Entity Developer generates the ResultType attribute to make code more clear. This attribute has three parameters: type of the collection (Emp in our case), the number (in our case, the number of SELECT in the stored procedure), and the resultset source type (ResultTypeOrigin.ExplicitSelect).

You can simply iterate the result ISingleResult<Emp> (since it is inherited from IEnumerable<Emp>).

ScottDataContext context = new ScottDataContext() { Log = Console.Out };
 
var list = context.GetEmpsByDeptId(30);
foreach (var item in list)
    Console.WriteLine(item.Ename);

Dim context As New ScottDataContext() With { _
    .Log = Console.Out _
}
Dim list = context.GetEmpsByDeptId(30)
For Each item In list
    Console.WriteLine(item.Ename)
Next

And now let's look at a stored procedure, returning two resultsets.

CREATE PROCEDURE [dbo].[GetDeptAndEmpsById]
  @id int
AS
BEGIN
  SELECT * FROM dept WHERE deptno = @id;
  SELECT * FROM emp WHERE deptno = @id;
END

The corresponding wrapper method can be the following:

[Function(Name = @"GetDeptAndEmpsById")]
[ResultType(typeof(Dept), 0, ResultTypeOrigin.ExplicitSelect)]
[ResultType(typeof(Emp), 1, ResultTypeOrigin.ExplicitSelect)]
public IMultipleResults GetDeptAndEmpsById(
  [Parameter(Name = "id", DbType = "int")] int? id
)
{
 
    IExecuteResult _GetDeptAndEmpsByIdResult = this.ExecuteMethodCall(
      this,
      (MethodInfo)MethodInfo.GetCurrentMethod(),
      id
    );
    return (IMultipleResults)_GetDeptAndEmpsByIdResult.ReturnValue;
}

<[Function](Name:="GetDeptAndEmpsById")> _
        <ResultType(GetType(Dept), 0, ResultTypeOrigin.ExplicitSelect)> _
        <ResultType(GetType(Emp), 1, ResultTypeOrigin.ExplicitSelect)> _
Public Function GetDeptAndEmpsById( _
    <Parameter(Name:="id", DbType:="int")> id As System.Nullable(Of Integer) _
) As IMultipleResults
    Dim _GetDeptAndEmpsByIdResult As IExecuteResult = _
        Me.ExecuteMethodCall(Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), id)
    Return DirectCast(_GetDeptAndEmpsByIdResult.ReturnValue, IMultipleResults)
End Function

In this case the description of resultsets is required, because otherwise runtime cannot determine the types of the collections, returned by the procedure. ResultTypeOrigin still can be omitted, because runtime knows we are working with SQL Server and will expect SELECT statements inside the procedure.

Here is the example of how to use the returned IMultipleResults.

ScottDataContext context = new ScottDataContext() { Log = Console.Out };
var res = context.GetDeptAndEmpsById(30);
Console.WriteLine(
    res.GetResult<Dept>().Single().Dname
);
foreach (var emp in res.GetResult<Emp>())
    Console.WriteLine("{0}, {1}", emp.Ename, emp.Job);

Dim context As New ScottDataContext() With { _
    .Log = Console.Out _
}
Dim res = context.GetDeptAndEmpsById(30)
Console.WriteLine(res.GetResult(Of Dept)().[Single]().Dname)
For Each emp In res.GetResult(Of Emp)()
    Console.WriteLine("{0}, {1}", emp.Ename, emp.Job)
Next

Here we call the GetResult<> method and pass the Dept and Emp types to it depending on which resultset we are reading. The resultsets are read in the order, specified by the Order parameter of the ResultType attributes, and this order in turn is determined by the order of the SELECT statements in the procedure. In fact, the generic argument is used only to know the type to materialize the resultset rows to. If we tried to read Emps first, and then Depts, we would get an invalid cast error.

See Also

Non-Composable Functions, Returning Resultsets  | Out Cursor Parameters  | Return Value of Cursor Type