Category: Chain

Normally when we need aggregates (min, max, average, sum, etc.) we end up having to declare it in multiple places. First you need an object to hold the result of the query. Then you need to put the actual logic of the query into a view, inline SQL, or LINQ expression.

This alone isn’t too much of a problem. But each time the report needs to be changed, you have to find the class and the query so that you can modify them both. And if the query appears in multiple locations, then you have to duplicate the logic in each place.

But what if we moved the aggregates directly into the class?

[Table("Sales.EmployeeSalesView"]
public class SalesFigures
{
	[AggregateColumn(AggregateType.Min, "TotalPrice")]
	public decimal SmallestSale { get; set; }

	[AggregateColumn(AggregateType.Max, "TotalPrice")]
	public decimal LargestSale { get; set; }

	[AggregateColumn(AggregateType.Average, "TotalPrice")]
	public decimal AverageSale { get; set; }

	[CustomAggregateColumn("Max(TotalPrice) - Min(TotalPrice)")]
	public decimal Range { get; set; }

	[GroupByColumn]
	public int EmployeeKey { get; set; }

	[GroupByColumn]
	public string EmployeeName { get; set; }
}

Now if you want to get more information, say grouping by month, you can do it directly in the model.

What does this look like in action? Well that depends on the ORM. For Tortuga Chain, it looks like this:

var report = dataSource.From<SalesFigures>().ToCollection.ExecuteAsync();

A nice effect of this pattern is that the logic is easily reusable. For example, say you wanted two reports. One for the current month and one for the current year.

var monthlyReport = dataSource.From<SalesFigures>(new {SalesMonth = month, SalesYear = year}).ToCollection.ExecuteAsync();

var yearlyReport = dataSource.From<SalesFigures>(new {SalesYear = year}).ToCollection.ExecuteAsync();

This feature is part of Tortuga Chain 4.3, but I can see it being incorporated into any ORM that includes SQL generation.