If an application executes a number of queries with complex conditions, data formatting, etc., such operations are often separated to the stored procedures/functions. Moreover, the whole queries can be put to stored procedues, and this can be more convenient than creating multiple views (especially if filter conditions are subject to change or the query result should depend on some parameters).
As stored procedures are used so often, any full-featured ORM must support them. This section contains information on how LinqConnect supports stored routines, and how to use them for reading data in LinqConnect applications. You can read how to use stored procedures for data modification in the Customizing Insert, Update, and Delete Operations topic.
Generally speaking, LinqConnect allows you to map database procedure or function to a DataContext method. This provides the most convenient way to work with stored routines because the wrapper methods are strongly-typed, available in Intellisence, and have signatures, close to the original routines' ones.
Stored routines may be divided into two groups - composable and non-composable ones. The first ones are the routines, that can be called inside SELECT statements, and the second ones just return a query result and cannot be called inside a SELECT statement. (Actually, non-composable stored routines may return no result, for example they may just modify some data, however, such cases are out of scoupe for this topic).
 Note: |
|---|
| LINQ to SQL documentation states that stored functions are always composable, and stored procedures are always not. For SQL Server this statement is true. However this cannot be applied to all the database servers, supported by LinqConnect. For example, Oracle stored function returning a cursor is not composable (its result can be fetched in a PL/SQL block, however it cannot be used as a data source in select statements that are usually genrated by ORMs). |
Composable stored routines are in turn divided into scalar and table routines. Scalar functions return one value of a simple type, and table ones return a sequence of values. Wrapper methods of scalar functions return primitive .NET types, and table functions are mapped to methods, that return the IQueryable<> interface.
Non-composable functions return one or more resultsets. Depending on a DBMS used, the resultsets can be returned as a return value, output cursor parameter, or from a query in a function body. Wrapper methods map these resultsets to ISingleResult, IMultipleResults, or just to IEnumerable.
For more details about these kinds of stored routines and their usage see the following topics: