Scalar Functions

In This Topic

Scalar Functions

LinqConnect Documentation
Scalar Functions
[email protected]

Scalar function can be used in the SELECT clause or in some condition (WHERE, JOIN, ORDER BY, etc.). The same should be correct for the corresponding method. LinqConnect allows you to use a DataContext method, mapped to the scalar function, in any part of a LINQ query.

Here is an example with a function, returning a number of employees (Emp table rows) for the specified department (Dept table row). This example uses Oracle stored function, you need to modify the function to use it with other DBMS.

  
CREATE OR REPLACE FUNCTION emp_number (dept_id NUMBER)
  RETURN NUMBER
AS
  res NUMBER;
BEGIN
  SELECT count(empno) INTO res FROM emp WHERE emp.deptno = dept_id;
  RETURN res;
END;
/

DataContext method for this function may look like the following (Attribute mapping is used; see Method Mapping for an example of the XML method mapping).

C#csharp	Copy Code
[Function(Name = @"EMP_NUMBER", IsComposable = true)] public int EmpNumber( [Parameter(Name = "DEPT_ID", DbType = "NUMBER")] int DeptId ) { IExecuteResult _EmpNumberResult = this.ExecuteMethodCall( this, (MethodInfo)MethodInfo.GetCurrentMethod(), DeptId ); return (int)_EmpNumberResult.ReturnValue; }

C#csharp

Copy Code

[Function(Name = @"EMP_NUMBER", IsComposable = true)]
public int EmpNumber(
  [Parameter(Name = "DEPT_ID", DbType = "NUMBER")] int DeptId
)
{
 
    IExecuteResult _EmpNumberResult = this.ExecuteMethodCall(
      this,
      (MethodInfo)MethodInfo.GetCurrentMethod(),
      DeptId
    );
    return (int)_EmpNumberResult.ReturnValue;
}

Visual Basic	Copy Code
<[Function](Name:="EMP_NUMBER", IsComposable:=True)> _ Public Function EmpNumber( _ <Parameter(Name:="DEPT_ID", DbType:="NUMBER")> DeptId As Int32 _ ) As Int32 Dim _EmpNumberResult As IExecuteResult = _ Me.ExecuteMethodCall(Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), DeptId) Return CType(_EmpNumberResult.ReturnValue, Int32) End Function

Visual Basic

Copy Code

<[Function](Name:="EMP_NUMBER", IsComposable:=True)> _
Public Function EmpNumber( _
    <Parameter(Name:="DEPT_ID", DbType:="NUMBER")> DeptId As Int32 _
) As Int32
    Dim _EmpNumberResult As IExecuteResult = _
        Me.ExecuteMethodCall(Me, DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), DeptId)
    Return CType(_EmpNumberResult.ReturnValue, Int32)
End Function

Notice the following details of the wrapper method implementation:

ExecuteMethodCall - this is a service method of the DataContext class, which prepares and executes a query based on the method metadata. It accepts the following arguments: a DataContext instance, .NET method metadata, and the collection of the method parameters.
ExecuteMethodCall result - the IExecuteResult interface, that consists of two members: the ReturnValue property returning the method return value, and the GetParameterValue(int parameterIndex) method returning the value of the specified parameter.
Since both ReturnValue and GetParameterValue return System.Object, you need to explicitly cast them to the correct types in the wrapper methods.

This wrapper method can be used in the SELECT clause of a query:

C#csharp	Copy Code
ScottDataContext context = new ScottDataContext() { Log = Console.Out }; var query = from dept in context.Depts select new { dept.Deptno, EmpsNumber = context.EmpNumber(dept.Deptno) }; var list = query.ToList();

C#csharp

Copy Code

ScottDataContext context = new ScottDataContext() { Log = Console.Out };
var query = from dept in context.Depts
            select new
            {
                dept.Deptno,
                EmpsNumber = context.EmpNumber(dept.Deptno)
            };
var list = query.ToList();

Visual Basic	Copy Code
Dim context As New ScottDataContext() With { _ .Log = Console.Out _ } Dim query = From dept In context.Depts Select New With { _ dept.Deptno, _ Key .EmpsNumber = context.EmpNumber(dept.Deptno) _ } Dim list = query.ToList()

Visual Basic

Copy Code

Dim context As New ScottDataContext() With { _
    .Log = Console.Out _
}
Dim query = From dept In context.Depts
            Select New With { _
                dept.Deptno, _
                Key .EmpsNumber = context.EmpNumber(dept.Deptno) _
            }
Dim list = query.ToList()

Generated SQL:

SELECT t1.DEPTNO AS "Deptno", EMP_NUMBER(t1.DEPTNO) AS C1
FROM DEPT t1

and in conditions (for example, in WHERE clause):

C#csharp	Copy Code
ScottDataContext context = new ScottDataContext() { Log = Console.Out }; var query = from dept in context.Depts where context.EmpNumber(dept.Deptno) > 1 select dept; var list = query.ToList();

Visual Basic	Copy Code
Dim context As New ScottDataContext() With { _ .Log = Console.Out _ } Dim query = From dept In context.Depts _ Where context.EmpNumber(dept.Deptno) > 1 _ Select dept Dim list = query.ToList()

Generated SQL:

SELECT t1.DEPTNO, t1.DNAME, t1.LOC
FROM DEPT t1
WHERE EMP_NUMBER(t1.DEPTNO) > :p0

As you can see, the wrapper methods wrap scalar functions so effectively that you don't need to think whether these methods wrap stored routines, and can just use them in queries as usual .NET methods.

Notice that if such wrapper method is used as a part of a query, it is not in fact executed. It is just used as a mark to specify that the certain stored function should be called in this place, so that the LinqConnect engine can translate the LINQ expression into the proper SQL. It's easy to check it - just set a breakpoint inside the wrapper method code and execute any of above queries. The example will execute but it will not stop on the breakpoint. If you think about it, it is quite logical: if the wrapper method were executed, every record would have to be fetched sequentially from the database, which would be fatal for performance.

The wrapper is executed only when it is called directly (instead of being a part of a LINQ query):

C#csharp	Copy Code
ScottDataContext context = new ScottDataContext() { Log = Console.Out }; int salesEmployeesCount = context.EmpNumber(30);

Visual Basic	Copy Code
Dim context As New ScottDataContext() With { _ .Log = Console.Out _ } Dim salesEmployeesCount As Integer = context.EmpNumber(30)

There is no query in this example, so there is nothing to translate. The method is executed and its code calls the stored function (if the method is written correctly). To always have correct wrapper methods generated, use Entity Developer visual LinqConnect model designer and code generator.

See Also