Table function returns a result set, that can be used in the same way as the table - as the data source for a query or subquery. In order to retain this functionality, wrapper methods for such functions return IQueryable<> - the common interface of the delayed LINQ queries. This allows such method to be a source of a LINQ query and ensures it will be translated to SQL only when the whole query is enumerated.
Oracle pipelined functions can be an example of table functions. For example, the following function returns all employees (emps), belonging to the specified department (dept):
CREATE OR REPLACE TYPE short_emp_info AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10 BYTE), job VARCHAR2(9 BYTE) ); / CREATE OR REPLACE TYPE emp_table_type AS TABLE OF short_emp_info; / CREATE OR REPLACE FUNCTION get_emps_pipelined(dept_id INTEGER) RETURN emp_table_type PIPELINED IS emp_cursor SYS_REFCURSOR; emp_record emp%ROWTYPE; BEGIN OPEN emp_cursor FOR SELECT * FROM emp WHERE emp.deptno = dept_id; LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN (emp_cursor%NOTFOUND); PIPE ROW( short_emp_info( emp_record.empno, emp_record.ename, emp_record.job ) ); END LOOP; RETURN; END; /
Wrapper method could be the following (Attribute mapping is used; see Method Mapping for an example of the XML method mapping):
The CreateMethodCallQuery method is another DataContext method (along with ExecuteMethodCall) that executes stored procedure or function. However, unlike ExecuteMethodCall, CreateMethodCallQuery does not execute stored routine on its own; instead it returns the IQueryable<> interface, which executes the function when being enumerated. This method has the same parameters as ExecuteMethodCall - DataContext instance, the method metadata and the collection of its parameters.
ShortEmpInfo is the supplementary type that is used for describing the resultset of the sample function. This is a so-called complex type (or value type). For example, it can be defined in the following way:
Note: |
---|
If you create this complex type in Entity Developer, it will generated a bit more complex code. For example, this class will implement the INotifyPropertyChanging interface, which allows to use change tracking for it. Another difference is that the setter methods will not be just a trivial 'field = value', so the engine will bypass them and change the underlying fields directly via reflection. For this purpose, the attribute parameters of the complex type properties will specify the underlying fields instead of properties themselves. |
Since such wrapper method returns IQueryable, you can write queries to its result, and these queries (and the stored function itself) will be delayed. For example:
Generated SQL:
SELECT t1.ename AS "Ename", t1.job AS "Job" FROM TABLE(get_emps_pipelined(:p0)) t1
Like the scalar function wrappers, this wrapper method is executed only if it is called once in a query (if it is the main data source of the query). If it must be executed several times (for example, the stored function is called in subqueries), it is used when translating the corresponding LINQ query to include the stored function call in the SQL statement (because it would be rather ineffectively to call the method for each record). For example, let's select all the depts and the number of the corresponding emps:
Generated SQL:
SELECT t1.dname AS "Dname", ( SELECT COUNT(*) AS c1 FROM TABLE(get_emps_pipelined(t1.deptno)) t2 ) AS c1 FROM dept t1
Place a breakpoint in the GetEmpsPipelined method to ensure it is not executed - the application will never stop on this breakpoint.
See Also
Stored Procedures in LinqConnect | Scalar Functions | Non-Composable Functions, Returning Resultsets