C# LINQ and Sum, Min, Max, Average behaviour

I’ve recently been improving support for aggregates in Shaolinq in order to make Shaolinq queries match LINQ to objects as closely as possible. The LINQ Enumerable<T>.Sum, Enumerable<T>.Min, Enumerable<T>.Max and Enumerable<T>.Average extension methods map to the SQL aggregate functions SUM, MIN, MAX and AVG respectively. In SQL, all these functions will return a null if there are no rows returned. In .NET, the behaviour when the enumerable is empty depends on whether the type T is nullable or not.

The .NET aggregate methods Min, Max and Average will throw an exception if the enumerable is empty and type T is not nullable (int vs int? etc). If type T is nullable then Min, Max, Average will return null. The exception to all this is Sum which will always return 0 regardless of the nullability of type T.

When performing a SQL aggregate query such as SELECT SUM(age) FROM PERSON, SQL will return a single row even if there are no PERSON rows and the SUM column value set to null.  This null value is used to detect empty result sets which can be mapped to the appropriate exception or null result depending on the nullability of the .NET required type. To match the behaviour of always returning 0 for Enumerable<T>.Sum regardless of whether the resultset is empty or not, Shaolinq wraps SUM aggregate calls in a SQL COALESCE. This is handled by the SumAggregatesDefaultValueCoalescer class along with SqlQueryProvider and ObjectProjector.

 

Leave a Reply

Your email address will not be published.