>LINQ Operators – Part VIII

>

Aggregation operators

Aggregation operators are Average, Count, LongCount, Sum, Max, Min and Aggregate. Aggregation operators are available only as methods in C# (VB also have a comprehension syntax for aggregation operators).

Average: Gets the average of a numeric collection. T-SQL counterpart is Avg()

Sum: Sums the values in a numeric collection. T-SQL counterpart is Sum()

Min,Max: Gets minimum and largest values in a sequence. T-SQL counterparts are Min(), Max()

Count, LongCount: Counts the elements in a collection. T-SQL counterparts are Count(), Count_Big().

Aggregate: Performs custom aggregation on a set of values. T-SQL has no counterpart.

int[] scores = {60,75,89};
scores.Average().Dump("Average");
scores.Sum().Dump("Sum");
scores.Max().Dump("Max");
scores.Min().Dump("Min");
scores.Count().Dump("Count");
scores.LongCount().Dump("LongCount");

Count and LongCount have a filtering parameter that filters what to count based on a boolean expression as in this sample:

int[] numbers = {17,122,23,12,22};
numbers.Count( n => n%2 == 0).Dump("Even Numbers count");

Count and LongCount are same except that LongCount returns a long (64 bits integer).

All these methods (except Count and LongCount) have a "selector" parameter to specify "what" to sum,average,min or max. Here are some samples:

Customers.Select( 
   c => new 
   {
      customer = c.CompanyName,
      firstPurchase = c.Orders.Min( o => o.OrderDate ),
      lastPurchase = c.Orders.Max( o => o.OrderDate ), 
      orders = c.Orders.Count,
      totalSale = c.Orders.Count == 0 ? 0 :
      c.Orders.Sum( o => o.OrderDetails
              .Sum( od => od.Quantity * od.UnitPrice ))
   } )
   .OrderBy( c => c.customer )

This sample uses Min, Max, Count, Sum to generate a summary statistics on customer purchases. Here is a sample for getting averages:

double quantity = OrderDetails.Average( od => od.Quantity );
decimal price =  OrderDetails.Average( od =>  od.UnitPrice );
decimal sale = OrderDetails.Average( od => od.Quantity * od.UnitPrice );

Console.WriteLine("{0} {1} {2}", quantity, price, sale );

 

Aggregate is the interesting one here that is new to VFP developers (and SQL developers as well). Aggregate allows custom aggregations to be done and is supported only in local data (not supported by Linq To SQL). Here are some samples:

IEnumerable<int> numbers = Enumerable.Range(1,10);
int sum = numbers.Aggregate( 
  (current, next) => current + next );
int sumProduct = numbers.Aggregate( 
  (current, next) => current * next );

sum.Dump();
sumProduct.Dump();

First one is simply a custom implementation of Sum(). Second one gets a product of the numbers in list ( 1 * 2 * 3 * 4 … 10 ). Next example shows that to use aggregate it doesn’t have to be a numeric.

Customers.Select(c => c.CustomerID).ToArray().
    Aggregate(   (current, next) =>  current  + ", " +next )

This sample creates a comma separated list of all customer ID values. Note that we get the customer IDs to an array first to make the collection local.

Aggregate have two more signatures. Next one uses an initial "seed" value (in the above samples the seed value was the first element in the list). I think VS Linq samples have one of the best samples for this one:

        [Category("Aggregate Operators")]
        [Title("Aggregate – Seed")]
        [Description("This sample uses Aggregate to create a running account balance that " +
                     "subtracts each withdrawal from the initial balance of 100, as long as " +
                     "the balance never drops below 0.")]
        public void Linq93() {
            double startBalance = 100.0;
            
            int[] attemptedWithdrawals = { 20, 10, 40, 50, 10, 70, 30 };
            
            double endBalance = 
                attemptedWithdrawals.Aggregate(startBalance,
                    (balance, nextWithdrawal) =>
                        ( (nextWithdrawal <= balance) ? (balance – nextWithdrawal) : balance ) );
            
            Console.WriteLine("Ending balance: {0}", endBalance);
        }

 

Next version of Aggregate allows you to have a custom selector projection on the result. 

Products.Select( p => new { p.ProductName, p.Category.CategoryName }).ToArray()
.Aggregate( 
       new {Beverages=0,Condiments=0,Other=0}, // initial seed value
       (acc,next) => next.CategoryName == "Beverages" 
                     ? new {Beverages=acc.Beverages+1, Condiments=acc.Condiments, Other=acc.Other}
                     : ( next.CategoryName == "Condiments"
                     ? new {Beverages=acc.Beverages, Condiments=acc.Condiments+1, Other=acc.Other}
                     : new {Beverages=acc.Beverages, Condiments=acc.Condiments, Other=acc.Other+1} ),
       result => String.Format("Beverages: {0}, Condiments: {1}, Others:{2}", result.Beverages, result.Condiments, result.Other)  // projection
)

It looks confusing and yes it is. Probably you wouldn’t want to aggregate like this but use a for loop. I wrote this one just to show how you could initialize a complex (here an anonymous type with Beverages,Condiments and Other properties) seed value, do accumulation based on category names and finally project the result to a custom string.

Advertisements

About cetinbasoz

A developer working primarily with C#,F#,VFP,SQL server, Silverlight,Linq ...
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s