How to: Implement and Use Custom Aggregate Functions

• Aug 24, 2020
• 5 minutes to read

This article describes how to implement and use a custom aggregate function that counts distinct items. This article assumes that you have a WinForms application with the Customer and Order business classes.

Tip

You can download a complete example here: How to Implement Custom Aggregates

Implement a Custom Aggregate Function

• Create the CountDistinctCustomAggregate class and add the required using statements.
• Implement the ICustomAggregate, ICustomAggregateFormattable, and ICustomAggregateQueryable interfaces. For information on how the interfaces differ, see the Custom Aggregate Functions article.

C#

using DevExpress.Data.Filtering;
using DevExpress.Data.Linq;
using DevExpress.Xpo;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace XpoCustomAggregate {
    class CountDistinctCustomAggregate : ICustomAggregate, ICustomAggregateQueryable, ICustomAggregateFormattable {

    }
}

VB.NET

Imports DevExpress.Data.Filtering
Imports DevExpress.Data.Linq
Imports DevExpress.Xpo
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Linq.Expressions
Imports System.Reflection

Namespace XpoCustomAggregate
    Friend Class CountDistinctCustomAggregate
        Implements ICustomAggregate, ICustomAggregateQueryable, ICustomAggregateFormattable

    End Class
End Namespace

• In the CountDistinctCustomAggregate class, implement the properties and methods as the code below demonstrates. Note that the Name property value cannot match aggregate function names in the application and does not allow standard aggregate names, for example, Min, Max, Sum, Avg, Single, Exists, and Count.

C#

static readonly CountDistinctCustomAggregate instance = new CountDistinctCustomAggregate();
public static void Register() {
    CriteriaOperator.RegisterCustomAggregate(instance);
}
public static void Unregister() {
    CriteriaOperator.UnregisterCustomAggregate(instance);
}
public string Name { get { return nameof(CountDistinct); } }
Type ICustomAggregate.ResultType(params Type[] operands) {
    return typeof(int);
}
object ICustomAggregate.CreateEvaluationContext() {
    return new HashSet<object>();
}
bool ICustomAggregate.Process(object context, object[] operands) {
    var ctx = (HashSet<object>)context;
    ctx.Add(operands[0]);
    return false;
}
object ICustomAggregate.GetResult(object context) {
    var ctx = (HashSet<object>)context;
    return ctx.Count;
}
string ICustomAggregateFormattable.Format(Type providerType, params string[] operands) {
    return string.Format("COUNT(distinct {0})", operands[0]);
}
MethodInfo ICustomAggregateQueryable.GetMethodInfo() {
    return typeof(CountDistinctCustomAggregate).GetMethod(Name);
}
public static object CountDistinct<T>(IEnumerable<T> collection, Expression<Func<T, object>> arg) {
    throw new InvalidOperationException("This method should not be called explicitly.");
}

VB.NET

Private Shared ReadOnly instance As New CountDistinctCustomAggregate()
Public Shared Sub Register()
    CriteriaOperator.RegisterCustomAggregate(instance)
End Sub
Public Shared Sub Unregister()
    CriteriaOperator.UnregisterCustomAggregate(instance)
End Sub
Public ReadOnly Property Name() As String Implements ICustomAggregate.Name
    Get
        Return NameOf(CountDistinct)
    End Get
End Property
Private Function ICustomAggregate_ResultType(ParamArray ByVal operands() As Type) As _
    Type Implements ICustomAggregate.ResultType
    Return GetType(Integer)
End Function
Private Function ICustomAggregate_CreateEvaluationContext() As Object Implements ICustomAggregate.CreateEvaluationContext
    Return New HashSet(Of Object)()
End Function
Private Function ICustomAggregate_Process(ByVal context As Object, ByVal operands() As Object) As _
    Boolean Implements ICustomAggregate.Process
    Dim ctx = DirectCast(context, HashSet(Of Object))
    ctx.Add(operands(0))
    Return False
End Function
Private Function ICustomAggregate_GetResult(ByVal context As Object) As Object Implements ICustomAggregate.GetResult
    Dim ctx = DirectCast(context, HashSet(Of Object))
    Return ctx.Count
End Function
Private Function ICustomAggregateFormattable_Format(ByVal providerType As Type, _
    ParamArray ByVal operands() As String) As String Implements ICustomAggregateFormattable.Format
    Return String.Format("COUNT(distinct {0})", operands(0))
End Function
Private Function ICustomAggregateQueryable_GetMethodInfo() As MethodInfo Implements ICustomAggregateQueryable.GetMethodInfo
    Return GetType(CountDistinctCustomAggregate).GetMethod(Name)
End Function
Public Shared Function CountDistinct(Of T)(ByVal collection As IEnumerable(Of T), ByVal arg _
    As Expression(Of Func(Of T, Object))) As Object
    Throw New InvalidOperationException("This method should not be called explicitly.")
End Function

Show the API description

API	Description
RegisterCustomAggregate(ICustomAggregate)	Registers a custom aggregate function to use in any CriteriaOperator-based criteria in your application.
UnregisterCustomAggregate(ICustomAggregate)	Unregisters a specified custom aggregate function from use in any CriteriaOperator-based criteria in your application.
Name	When implemented by a custom aggregate function, specifies its name.
ResultType(Type[])	When implememnted by a custom aggregate function, determines its return value type based on the type of aggregate function parameters.
Process(Object, Object[])	Is called to process every element of a collection supplied to a custom aggregate function.
GetResult(Object)	Gets a custom aggregate function’s result.
Format(Type, String[])	Builds a SQL command that calculates a custom aggregate function result on the server side.
GetMethodInfo()	When implemented by a class, returns the metadata of a method associated with a custom aggregate function used in LINQ to XPO expressions.

Add a Grid Control and Register the Custom Aggregate Function

• Add a new GridControl to the form.
• Open the form’s code file and register the custom aggregate.

C#

CountDistinctCustomAggregate.Register();

VB.NET

CountDistinctCustomAggregate.Register()

Choose the Data Source

There are two ways you can load data from the data store. If you work with the CriteriaOperator, you can use the XPView, XPCollection, or other XPO data sources. Use XPQuery if you work with LINQ.

Use the XPView as a Data Source

• In the form’s file, initialize a new XPView.
• Add the required properties to the XPView and assign the XPView to the GridControl‘s DataSource.

C#

XPView xpView = new XPView(session1, typeof(Customer));
xpView.AddProperty("ContactName", CriteriaOperator.Parse("[FirstName] + ' ' + [LastName]"));
xpView.AddProperty("DistinctProducts", CriteriaOperator.Parse("[Orders][].CountDistinct([ProductName])"));
xpView.Sorting.Add(new SortProperty("ContactName", DevExpress.Xpo.DB.SortingDirection.Ascending));
gridControl1.DataSource = xpView;

VB.NET

Dim xpView As New XPView(session1, GetType(Customer))
xpView.AddProperty("ContactName", CriteriaOperator.Parse("[FirstName] + ' ' + [LastName]"))
xpView.AddProperty("DistinctProducts", CriteriaOperator.Parse("[Orders][].CountDistinct([ProductName])"))
xpView.Sorting.Add(New SortProperty("ContactName", DevExpress.Xpo.DB.SortingDirection.Ascending))
gridControl1.DataSource = xpView

Use XPQuery as a Data Source

• In the form’s file, initialize a new XPQuery<T> and assign it to the GridControl‘s DataSource.
• Call the custom aggregate function.

C#

gridControl2.DataSource = new XPQuery<Customer>(session1)
    .Select(t => new {
        ContactName = t.ContactName,
        DistinctProducts = (int)CountDistinctCustomAggregate.CountDistinct(t.Orders, o => o.ProductName),
    })
    .OrderBy(t => t.ContactName)
    .ToList();

VB.NET

gridControl2.DataSource = (New XPQuery(Of Customer)(session1)).Select(Function(t) New With {
    Key .ContactName = t.ContactName,
    Key .DistinctProducts = CInt(Math.Truncate(CountDistinctCustomAggregate.CountDistinct(t.Orders, Function(o) o.ProductName)))
}).OrderBy(Function(t) t.ContactName).ToList()