ICustomFunctionOperatorFormattable Interface

Allows you to use the Query Builder or filter editor to insert a string into SQL queries generated for SELECT operations.

Namespace: DevExpress.Data.Filtering

Assembly: DevExpress.Data.v23.2.dll

NuGet Package: DevExpress.Data

Declaration

C#

public interface ICustomFunctionOperatorFormattable :
    ICustomFunctionOperator

VB.NET

Public Interface ICustomFunctionOperatorFormattable
    Inherits ICustomFunctionOperator

Related API Members

The following members return ICustomFunctionOperatorFormattable objects:

• ConnectionProviderSql.GetCustomFunctionOperator(String)

Remarks

The ICustomFunctionOperatorFormattable interface adds a function to the list of available functions in the Query Builder and filter editors. The user can insert a function into the query string. Before sending the SQL query to the data source, the data access engine calls the Format method to substitute the function call with the string that the method returns.

The ICustomFunctionOperatorFormattable interface inherits from the ICustomFunctionOperator interface, so you should also implement the Evaluate(Object[]) method. The Evaluate(Object[]) method is called when a binding expression that contains the custom function is calculated. The following sample function is only for SQL editors, so the Evaluate(Object[]) method is not applicable, and throws an exception.

C#

public class SampleCustomOperatorFormattable : ICustomFunctionOperatorFormattable {
    public string Name => "CalcCost";
    public object Evaluate(params object[] operands) {
        return ((decimal)operands[0] * (int)operands[1]);
    }
    public string Format(Type providerType, params string[] operands) {
        if(typeof(PostgreSqlConnectionProvider).IsAssignableFrom(providerType)) {
            return $" {operands[0]} * {operands[1]} ";
        } else throw new NotSupportedException();
    }
    public Type ResultType(params Type[] operands) {
        return typeof(decimal);
    }
    public static SampleCustomOperatorFormattable Instance = new SampleCustomOperatorFormattable();
    public static void Register() {
        CriteriaOperator.RegisterCustomFunction(Instance);
    }
    public static void Unregister() {
        CriteriaOperator.UnregisterCustomFunction(Instance);
    }
}

VB.NET

Public Class SampleCustomOperatorFormattable
    Implements ICustomFunctionOperatorFormattable

    Public ReadOnly Property Name() As String
        Get
            Return "CalcCost"
        End Get
    End Property
    Public Function Evaluate(ParamArray ByVal operands() As Object) As Object
        Return (DirectCast(operands(0), Decimal) * DirectCast(operands(1), Integer))
    End Function
    Public Function Format(ByVal providerType As Type, ParamArray ByVal operands() As String) As String
        If GetType(PostgreSqlConnectionProvider).IsAssignableFrom(providerType) Then
            Return $" {operands(0)} * {operands(1)} "
        Else
            Throw New NotSupportedException()
        End If
    End Function
    Public Function ResultType(ParamArray ByVal operands() As Type) As Type
        Return GetType(Decimal)
    End Function
    Public Shared Instance As New SampleCustomOperatorFormattable()
    Public Shared Sub Register()
        CriteriaOperator.RegisterCustomFunction(Instance)
    End Sub
    Public Shared Sub Unregister()
        CriteriaOperator.UnregisterCustomFunction(Instance)
    End Sub
End Class

If you want to make your custom function available to end users in Expression Editors, implement the ICustomFunctionOperatorBrowsable interface as well.

Example

The following code implements the StdDev custom function intended only for SQL queries in Query Builder. The Format method modifies the query string.

When this function is used in an expression, the Evaluate method throws an exception.

C#

using System;
using System.Collections.Generic;
using DevExpress.Data.Filtering;


namespace SelectQueryWindowsFormsApplication {
    public sealed class StDevFunction : ICustomFunctionOperatorBrowsable, ICustomFunctionOperatorFormattable {
        const string name = "StDev";
        public static void Register() {
            CriteriaOperator.RegisterCustomFunction(new StDevFunction());
        }
        public static void Unregister() {
            CriteriaOperator.UnregisterCustomFunction(name);
        }
        public static readonly HashSet<Type> ValidOperandTypes = new HashSet<Type> {
            typeof(sbyte),
            typeof(byte),
            typeof(short),
            typeof(ushort),
            typeof(int),
            typeof(uint),
            typeof(long),
            typeof(ulong),
            typeof(decimal),
            typeof(double),
            typeof(float)
        };

        #region ICustomFunctionOperator
        public Type ResultType(params Type[] operands) {
            return typeof(double);
        }
        public object Evaluate(params object[] operands) {
            throw new NotSupportedException();
        }
        public string Name { get { return name; } }
        #endregion

        #region ICustomFunctionOperatorBrowsable
        public FunctionCategory Category { get { return FunctionCategory.Math; } }
        public string Description { get { return "Standard deviation function"; } }
        public bool IsValidOperandCount(int count) {
            return count == 1;
        }
        public bool IsValidOperandType(int operandIndex, int operandCount, Type type) {
            return ValidOperandTypes.Contains(type);
        }
        public int MaxOperandCount { get { return 1; } }
        public int MinOperandCount { get { return 1; } }
        #endregion

        #region ICustomFunctionOperatorFormattable
        public string Format(Type providerType, params string[] operands) {
            return string.Format("stdev({0})", operands[0]);
        }
        #endregion
    }
}

VB.NET

Imports System
Imports System.Collections.Generic
Imports DevExpress.Data.Filtering


Namespace SelectQueryWindowsFormsApplication
    Public NotInheritable Class StDevOperator
        Implements ICustomFunctionOperatorBrowsable, ICustomFunctionOperatorFormattable
        Private Const name_Renamed As String = "StDev"
        Public Shared Sub Register()
            CriteriaOperator.RegisterCustomFunction(New StDevOperator())
        End Sub
        Public Shared Sub Unregister()
            CriteriaOperator.UnregisterCustomFunction(name_Renamed)
        End Sub
        Public Shared ReadOnly ValidOperandTypes As New HashSet(Of Type)() From {GetType(SByte), GetType(Byte), GetType(Short), GetType(UShort), GetType(Integer), GetType(UInteger), GetType(Long), GetType(ULong), GetType(Decimal), GetType(Double), GetType(Single)}

#Region "ICustomFunctionOperator"
        Public Function ResultType(ParamArray ByVal operands() As Type) As Type Implements ICustomFunctionOperator.ResultType
            Return GetType(Double)
        End Function
        Public Function Evaluate(ParamArray ByVal operands() As Object) As Object Implements ICustomFunctionOperator.Evaluate
            Throw New NotSupportedException()
        End Function
        Public ReadOnly Property Name() As String Implements ICustomFunctionOperator.Name
            Get
                Return name_Renamed
            End Get
        End Property

#End Region
#Region "ICustomFunctionOperatorBrowsable"
        Public ReadOnly Property Category() As FunctionCategory Implements ICustomFunctionOperatorBrowsable.Category
            Get
                Return FunctionCategory.Math
            End Get
        End Property
        Public ReadOnly Property Description() As String Implements ICustomFunctionOperatorBrowsable.Description
            Get
                Return "Standard deviation function"
            End Get
        End Property
        Public Function IsValidOperandCount(ByVal count As Integer) As Boolean Implements ICustomFunctionOperatorBrowsable.IsValidOperandCount
            Return count = 1
        End Function
        Public Function IsValidOperandType(ByVal operandIndex As Integer, ByVal operandCount As Integer, ByVal type As Type) As Boolean Implements ICustomFunctionOperatorBrowsable.IsValidOperandType
            Return ValidOperandTypes.Contains(type)
        End Function
        Public ReadOnly Property MaxOperandCount() As Integer Implements ICustomFunctionOperatorBrowsable.MaxOperandCount
            Get
                Return 1
            End Get
        End Property
        Public ReadOnly Property MinOperandCount() As Integer Implements ICustomFunctionOperatorBrowsable.MinOperandCount
            Get
                Return 1
            End Get
        End Property
#End Region

#Region "ICustomFunctionOperatorFormattable"
        Public Function Format(ByVal providerType As Type, ParamArray ByVal operands() As String) As String Implements ICustomFunctionOperatorFormattable.Format
            Return String.Format("stdev({0})", operands(0))
        End Function
#End Region
    End Class
End Namespace

View Example: Reporting for WinForms - Implement a Custom Function for Use in a Query Expression