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.v24.1.dll
NuGet Package: DevExpress.Data
#Declaration
#Related API Members
The following members return ICustomFunctionOperatorFormattable objects:
#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.
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);
}
}
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.
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
}
}