Adding aggregate functions to an expression column certainly gives you more data options, but as a calculation method it doesn’t provide any benefit beyond the DataTable.Compute method. The real power of aggregate expression columns appears when working with related tables. By adding an aggregate function to a parent table that references the child table, you can generate summaries that are grouped by each parent row. This functionality is similar in purpose to the GROUP BY clause found in the SQL language.
C#
// ----- Build the parent table and add some data.
DataTable customers = new DataTable("Customer");
customers.Columns.Add("ID", typeof(int));
customers.Columns.Add("Name", typeof(string));
customers.Rows.Add(new Object[] {1, "Coho Winery"});
customers.Rows.Add(new Object[] {2, "Fourth Coffee"});
// ----- Build the child table and add some data. The "Total"
// expression column adds sales tax to the subtotal.
DataTable orders = new DataTable("Order"); orders.Columns.Add("ID", typeof(int));
orders.Columns.Add("Customer", typeof(int)); orders.Columns.Add("Subtotal", typeof(decimal));
orders.Columns.Add("TaxRate", typeof(decimal)); orders.Columns.Add("Total", typeof(decimal), "Subtotal * (1 + TaxRate)");
// ----- Two sample orders for customer 1, 1 for customer 2.
orders.Rows.Add(new Object[] {1, 1, 35.24, 0.0875});
// Total = $38.32 orders.Rows.Add(new Object[] {2, 1, 56.21, 0.0875});
// Total = $61.13 orders.Rows.Add(new Object[] {3, 2, 14.94, 0.0925});
// Total = $16.32
// ----- Link the tables within a DataSet.
DataSet business = new DataSet(); business.Tables.Add(customers); business.Tables.Add(orders);
business.Relations.Add(customers.Columns["ID"], orders.Columns["Customer"]);
// ----- Here is the aggregate expression column. customers.Columns.Add("OrderTotals", typeof(decimal), "Sum(Child.Total)");
// ----- Display each customer's order total. foreach (DataRow scanCustomer in customers.Rows) { Console.WriteLine((string)scanCustomer["Name"] + ": " + string.Format("{0:c}", (decimal)scanCustomer["OrderTotals"])); }