There is a number of ways how stored routines can return resultsets, other that are used for the table-valued functions:

• Explicit queries inside a function body. Such way is supported in SQL Server and MySQL - just enter "select * from dept" inside a functon, and it will return the corresponding resultset. Or you can enter multiple queries, and the function will return several resultsets.
• OUT cursor parameters. They are supported, for example, in Oracle and PostgreSQL. A cursor for some SELECT statement can be opened inside a stored procedure and passed to an application, which can read the cursor later. This way allows passing more than one result set (via multiple cursors) as well.
• Return value that is not a dynamic table, and thus cannot be the data source for a SELECT statement. For example, these are cursors returned by Oracle functions: cursors must be fetched sequentially, so the function cannot be used as a source in SELECT statements.

LinqConnect supports multiple DBMSs, and each of them allows a certain subset of specified approaches. LinqConnect supports all of the approaches and chooses the most suitable one as the default depending on the DBMS used. You can specify the approach for each stored routine explicitly (Entity Developer does it automatically). LinqConnect uses the ResultTypeAttribute enum for specifying the way a stored routine returns a resultset. It can have the following values: ReturnValue, OutputCursorParameter, and ExplicitSelect. You can assign one of these values to the Origin property in the result type mapping, and our runtime will search for the resultset in the specified place. See the examples in the corresponding topics of this section.

As for the .NET Framework side, the resultsets can also be returned in different ways:

• As the IEnumerable<>/ISingleResult<> interfaces, returned by the wrapper method
• As the output parameters of the IEnumerable<> type
• As the IMultipleResults return value of the wrapper method (this interface is designed for returning several resultsets, though can be used for a single one as well).

Note:

The ISingleResult and IMultipleResults are very simple interfaces. They both inherit IFunctionResult that has the only property - ReturnValue (if the function returns a value of a scalar type, you can assign it to this property), plus they provide a way to read one or more collection of objects. ISingleResult<TEntity> inherits IEnumerable<TEntity> for this purpose, and IMultipleResult has the GetResult<TEntity>() method, returning IEnumerable<TEntity>. Important note: you should remember that GetResult always returns the next resultset, and generic argument is only used to get the strong-typed collection. Thus, ensure that you use the generic type argument that corresponds to the order of resultsets (if the stored routine reads depts and then emps, GetResult<Emp> must be called after calling GetResult<Dept>; otherwise, you get an error).

Let's discuss the correspondence between how stored routines can return resultsets and how the resultsets are returned on the .NET Framework side (you can also see examples in the topics of this section). Let's start with the case when a single resultset is returned. It's obvious that when such resultset is returned as the cursor result value of the stored function or as the result of the query inside the stored routine, it should be returned as the return value of the wrapper method.

However, when the resultset is returned as the output cursor parameter, there are two options. To maintain the similarity of the method and stored routine signatures, it seems better to pass the result to an output parameter of the wrapper method. However this may be inconvenient - the developer needs to create a variable each time such method is called. Besides, consider the case you want to use the same DataContext class for databases on several DBMSs (say, Oracle and SQL Server). If the result set is passed through the return value of the wrapper value, you may share this wrapper method between the corresponding routines of different DBMSs. That's why Entity Developer generates methods with ISingleResult<> return value for the stored procedures with output cursor parameters.

Also, in each case the single result set may be passed via the ImultipleResults interface; however, it is rather hard to imagine the scenario for which this way is necessary.

If a stored routine returns several resultsets, we do recommend using IMultipleResults to return them, and Entity Developer generates method code using IMultipleResults in such cases. For resultsets passed as output cursor parameters, you can also use output parameters of the method wrapper (instead of returning ImultipleResults).

And now let's look at the examples of each of the ways to return resultsets. All the examples are based on two kinds of the functions - the first one returns the information about all the employees (emp) of the specified department (dept) by the department ID, and the other one does the same and additionally returns the information about this department (hence, returning two resultsets - the first with the single dept, and the second with the corresponding emps). The examples are described in the following topics:

• Explicit Queries inside Procedure
• Out Cursor Parameters
• Return Value of Cursor Type

See Also

Stored Procedures in LinqConnect  | Scalar Functions  | Table Functions