Perform aggregation in Foreign Key Column

17 Feb 20221 minute to read

Default aggregations are not supported in a foreign key column. You can achieve aggregation for the foreign key column by using the custom aggregates. The following example demonstrates the way to achieve aggregation in foreign key column.

In the following example, The Employee Name is a foreign key column and the aggregation for the foreign column was calculated in customAggregateFn.

CSHTML

@Html.EJS().Grid("Grid").DataSource((IEnumerable<object>)ViewBag.DataSource).Columns(col =>
{
    col.Field("OrderID").HeaderText("Order ID").Width("120").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("EmployeeID").HeaderText("Employee Name").Width("150").ForeignKeyValue("FirstName").DataSource(ViewBag.ForeignData).Add();
    col.Field("OrderDate").HeaderText("Order Date").Width("130").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Format("yMd").Add();
    col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add();
    col.Field("ShipCountry").HeaderText("Ship Country").Width("120").Add();

}).Aggregates(agg => { agg.Columns(new List<Syncfusion.EJ2.Grids.GridAggregateColumn>() { new Syncfusion.EJ2.Grids.GridAggregateColumn() { Field = "EmployeeID", Type = "Custom", CustomAggregate = "customAggregateFn", FooterTemplate = "Count of Margaret: ${Custom}" } }).Add();}).Render()

<script>
    function customAggregateFn(data, column) {
        var grid = document.getElementById("Grid").ej2_instances[0];
        return data.result.filter(function (dObj) {
            return ej.base.getValue('FirstName', ej.grids.getForeignData(grid.getColumnByField(column.columnName), dObj)[0]) === 'Margaret';
        }).length;
    };
</script>

Aggregate-foreign.cs

public IActionResult Index()
{
    ViewBag.DataSource = OrderDetails.GetAllRecords();
    ViewBag.ForeignData = EmployeeDetails.GetAllRecords();
    return View();
}