xpo-401341-examples-how-to-implement-and-use-custom-aggregate-functions.md
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
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.
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 {
}
}
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.
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.");
}
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.
| |
|
When implemented by a custom aggregate function, specifies its name.
| |
|
When implememnted by a custom aggregate function, determines its return value type based on the type of aggregate function parameters.
| |
|
Is called to process every element of a collection supplied to a custom aggregate function.
| |
|
Gets a custom aggregate function’s result.
| |
|
Builds a SQL command that calculates a custom aggregate function result on the server side.
| |
|
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 new GridControl to the form.
Open the form’s code file and register the custom aggregate.
CountDistinctCustomAggregate.Register();
CountDistinctCustomAggregate.Register()
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.
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.
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;
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
In the form’s file, initialize a new XPQuery<T> and assign it to the GridControl‘s DataSource.
Call the custom aggregate function.
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();
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()