Perform aggregation in Foreign Key Column

17 Feb 20222 minutes 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

<ejs-grid id="Grid" dataSource="@ViewBag.DataSource">
    <e-grid-columns>
        <e-grid-column field="OrderID" headerText="Order ID" textAlign="Right" width="120"></e-grid-column>
        <e-grid-column field="EmployeeID" headerText="Customer ID" type="string" width="120" foreignKeyValue='FirstName' dataSource="ViewBag.ForeignData"></e-grid-column>
        <e-grid-column field="ShipCity" headerText="Ship City" width="150"></e-grid-column>
        <e-grid-column field="ShipName" headerText="Ship Name" width="150"></e-grid-column>
    </e-grid-columns>
    <e-grid-aggregates>
        <e-grid-aggregate>
            <e-aggregate-columns>
                <e-aggregate-column field="EmployeeID" type="Custom" customAggregate="@("customAggregateFn")" footerTemplate="Count of Margaret:${Custom}"></e-aggregate-column>
            </e-aggregate-columns>
        </e-grid-aggregate>
    </e-grid-aggregates>
</ejs-grid>

<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();
}