LinqConnect Documentation
Non-Composable Functions, Returning Resultsets

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

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:

CautionNote:
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:

See Also

Stored Procedures in LinqConnect  | Scalar Functions  | Table Functions