LinqConnect Documentation
In This Topic
    Table Functions
    In This Topic
    Table Functions
    LinqConnect Documentation
    Table Functions
    [email protected]

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


    C#csharpCopy Code
    [Function(Name = @"GET_EMPS_PIPELINED", IsComposable = true)]
public IQueryable<ShortEmpInfo> GetEmpsPipelined(
  [Parameter(Name = "DEPT_ID", DbType = "NUMBER")] decimal? deptId
)
{
 
    return this.CreateMethodCallQuery<ShortEmpInfo>(
      this,
      (MethodInfo)MethodInfo.GetCurrentMethod(),
      deptId
    );
}
    Visual BasicCopy Code
    <[Function](Name:="GET_EMPS_PIPELINED", IsComposable:=True)> _
Public Function GetEmpsPipelined( _
    <Parameter(Name:="DEPT_ID", DbType:="NUMBER")> deptId As System.Nullable(Of Decimal)
) As IQueryable(Of ShortEmpInfo)
 
    Return Me.CreateMethodCallQuery(Of ShortEmpInfo)( _
        Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), deptId)
End Function

    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:


    C#csharpCopy Code
    public partial class ShortEmpInfo
{
 
    public ShortEmpInfo() { }
 
    [Column(Name = @"EMPNO", Member = "Empno", DbType = "NUMBER NULL")]
    public decimal? Empno { get; set; }
 
    [Column(Name = @"ENAME", Member = "Ename", DbType = "VARCHAR2(4000 CHAR) NULL")]
    public string Ename { get; set; }
 
    [Column(Name = @"JOB", Member = "Job", DbType = "VARCHAR2(4000 CHAR) NULL")]
    public string Job { get; set; }
}
    Visual BasicCopy Code
    Partial Public Class ShortEmpInfo
 
    Public Sub New()
    End Sub
 
    <Column(Name:="EMPNO", Member:="Empno", DbType:="NUMBER NULL")> _
    Public Property Empno() As System.Nullable(Of Decimal)
        Get
            Return m_Empno
        End Get
        Set(value As System.Nullable(Of Decimal))
            m_Empno = Value
        End Set
    End Property
    Private m_Empno As System.Nullable(Of Decimal)
 
    <Column(Name:="ENAME", Member:="Ename", DbType:="VARCHAR2(4000 CHAR) NULL")> _
    Public Property Ename() As String
        Get
            Return m_Ename
        End Get
        Set(value As String)
            m_Ename = Value
        End Set
    End Property
    Private m_Ename As String
 
    <Column(Name:="JOB", Member:="Job", DbType:="VARCHAR2(4000 CHAR) NULL")> _
    Public Property Job() As String
        Get
            Return m_Job
        End Get
        Set(value As String)
            m_Job = Value
        End Set
    End Property
    Private m_Job As String
End Class

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


    C#csharpCopy Code
    ScottDataContext context = new ScottDataContext() { Log = Console.Out };
 
// The function is not executed here, this line just 
// describes the query that will eventually be executed.
 
var query = from emp in context.GetEmpsPipelined(30)
            select new { emp.Ename, emp.Job };
 
// Here the query (and the function) is executed.
var list = query.ToList();
    Visual BasicCopy Code
    Dim context As New ScottDataContext() With { _
    .Log = Console.Out _
}
 
' The function is not executed here, it is just used in the 
' other query part as a data source.
 
Dim query = From emp In context.GetEmpsPipelined(30)
            Select New With { _
                emp.Ename, _
                emp.Job _
            }
 
' Here the query (and the function) is executed.
Dim list = query.ToList()

    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:


    C#csharpCopy Code
    ScottDataContext context = new ScottDataContext() { Log = Console.Out };
var query = from dept in context.Depts
            select new
            {
                dept.Dname,
                EmpsNumber = context.GetEmpsPipelined(dept.Deptno).Count()
            };
    Visual BasicCopy Code
    Dim context As New ScottDataContext() With { _
    .Log = Console.Out _
}
 
Dim query = From dept In context.Depts Select New With { _
    dept.Dname, _
    Key .EmpsNumber = context.GetEmpsPipelined(dept.Deptno).Count() _
}
 
Dim list = query.ToList()

    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

     

     

    © 2010 - 2024 Devart. All Rights Reserved.

    [email protected]