What we learned previously

In my previous articles we learned Projection Operators/Ordering Operators and Grouping Operators/Join Operators. In this article we are going to see few more operators like

• Set
• Aggregate

Set Operators

In set operator we will going to see Union, Intersect and Except; which are common set operators, and Distinct is use to select unique occurrence of item. First we will see Distinct.

Generally we are using distinct if we want some unique data, let see example of unique products on which we have placed order.

var query = context.Products
    .Join(context.Order_Details,
    p => p.ProductID,
    od => od.ProductID,
    (p, od) => new { p.ProductID });

Console.WriteLine("Total Products in Order:{0}", query.Count());
Console.WriteLine("Total Distinct Products in Order:{0}", query.Distinct().Count());

Let’s see the output

We can write same query in LINQ query expression as follow

var query = from product in context.Products
            join orderDet in context.Order_Details
            on product.ProductID equals orderDet.ProductID
            select product.ProductID;

Console.WriteLine("Total Products in Order:{0}", query.Count());
Console.WriteLine("Total Distinct Products in Order:{0}", query.Distinct().Count());

Distinct uses GetHashCode and Equals methods to compare object. Distinct have one more overload using which you can override default behavior of object comparison. Here Count is aggregate operators which will see soon.

Set operators; three useful set operator that we going to discuss are Union, Intersect and Except. Usability of all these three are same and functionality are also as normal set behaviors.

var orderSet1 = context.Orders.Take(5);
var orderSet2 = context.Orders.Skip(3).Take(5);

Console.WriteLine("OrderSet1");
foreach (var item in orderSet1)
{
    Console.WriteLine("--OID:{0}", item.OrderID);
}

Console.WriteLine("OrderSet2");
foreach (var item in orderSet2)
{
    Console.WriteLine("--OID:{0}", item.OrderID);
}

var unionOfOrd = orderSet1.Union(orderSet2);

Console.WriteLine("UnionOfOrd");
foreach (var item in unionOfOrd)
{
    Console.WriteLine("--OID:{0}", item.OrderID);
}

Here we are creating two sets to demonstrate our set operators, we have used Take and Skip which are partitioning operators; I will explain that operators in my next article. For now we just assume that its taking some number of rows and skipping some number of rows respectively. Let see the output

Its normal behavior of Union operator, it took all records from set1 merged it with set2 and selected unique items. Not only Distinct but Union, Intersect and Except are compared objects using GetHashCode and Equals method or custom comparer. Intersect and Except behaving same as normal set operation and same syntax as Union, so I as skipping that two operators.

Aggregate Operators

LINQ also provides few aggregate operators to implement common aggregation. There are Count, LongCount, Sum, Min, Max, Average and Aggregate in which few are very simple. We will quickly see these with examples.

Count and LongCount, We already seen Count in our previous example, it simply counts item in source sequence. Count have one more overloads which accept predicate to filter the items.

var germanyCust = context.Customers.Count(c => c.Country.Equals("Germany"));
Console.WriteLine("Number of Customer from Germany: {0}", germanyCust);

var usaCust = (from cust in context.Customers
                where cust.Country.Equals("USA")
                select cust).Count();
Console.WriteLine("Number of Customer from USA: {0}", usaCust);

First germanyCust is is using predicate as argument to Count operator, which returns true if condition satisfied and which get counted. Second one usaCust is counting the items in source sequence.

Here is the output.

LongCount only vary in return type, Count returns integer where LongCount returns long.

Sum, Sum have same methods as Count and LongCount, first one is operate on source sequence items, it operates on same Numeric type, Numeric type includes int, int?, long, long?, float, float?, double, double?, decimal and decimal?. Here ? defines type as Nullable type. For example long? means Nullable<System.Int64>. If source is empty zero is returned, in case nullable result might be null. Default implementation can be used to do direct sum on list. We can use predicate for operator on sequence of source. Let see both with example.

int[] numbers = { 3, 54, 23, 23, 34, 34 };
Console.WriteLine("Sum of numbers are:{0}", numbers.Sum());

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    int? stock = context.Products.Sum(p => p.UnitsInStock);
    Console.WriteLine("Available stock: {0}", stock.HasValue ? stock.Value : 0);
}

Second one is interesting, UnitsInStock is nullable short, that is the reason our stock variable is of type nullable int, HasValue returns true if the current System.Nullable<T> object has a value; false if the current System.Nullable<T> object has no value.

Let see the output.

Min and Max, these are straight forward operators calculates minimum and maximum respectively. It have same overloads as Sum operator. Comparison uses IComparable<T> interface.

var minQuery = (from ord in context.Order_Details
                select ord.Quantity).Min();
Console.WriteLine("Min Quantity using Default: {0}", minQuery);

var maxQuery = (from ord in context.Order_Details
                select ord.Quantity).Max();
Console.WriteLine("Max Quantity using Default: {0}", maxQuery);

var minExpr = context.Order_Details.Min(o => o.Quantity);
Console.WriteLine("Min Quantity using predicate: {0}", minExpr);

var maxExpr = context.Order_Details.Max(o => o.Quantity);
Console.WriteLine("Max Quantity using predicate: {0}", maxExpr);

Above code demonstrate both overload of Min and Max. Let see the output.

Average, Average is next operator fall under this category. It calculate arithmetic average of given set of values from source sequence. Usability of this also same as Min, Max and other seen operators. It has default implementation which operates on sequence directly and other using predicate. Let see with example.

var avgQuery = (from ord in context.Order_Details
                select ord.UnitPrice).Average();
Console.WriteLine("Average UnitPrice using default:{0}", avgQuery);

var avgExpr = context.Order_Details.Average(ord => ord.UnitPrice);
Console.WriteLine("Average UnitPrice using predicate:{0}", avgExpr);

var avgExpr1 = from cust in context.Customers
            from ord in cust.Orders
            where ord.Order_Details.Count > 0
            select new { cust.ContactName, AverageOrder = ord.Order_Details.Average(det => det.UnitPrice) };

foreach (var custOrd in avgExpr1.ToList())
{
    Console.WriteLine("Customer : {0}, AvgOrder : {1}", custOrd.ContactName, custOrd.AverageOrder);
}

First method is invoked without argument as its found Numeric sequence, if it don’t found then you have to provide selector predicate which we used in second method. It do first sum and then make average, when sum of the values is too large for result then OverflowException will thrown. Last listing needs little bit attention, We are trying to get customer and its average order, there may be case when customer cont have order in that case Average will throw error as there is no order for average!. To avoid this condition we have to remove those entry for which there is no orders.

Let’s see the output.

Last operator in this category is Aggregate.

This operator invokes function recursively from first element to last element, storing result in accumulator and passes in each step. In simple term, it operates on first and second operator and then result of both and third element and so on so forth. Let see with example.

string[] strArray = { "First", "Second", "Third", "Fourth" };
var aggQuery = strArray.Aggregate((a, b) => a + " | " + b);

Console.WriteLine("Simple Aggregate of Array : {0}", aggQuery);

This will concat  each item of array with | in between, let see the output.

So, what this will did is, it take first element append second element with | and create first result, then it take first result added third element with | and created second result so on so forth, at the end of iteration it will give us final result which is in case all string array is concatenate with | as separator. The Aggregation may replace all other aggregation operator, but this will used in case we don’t have specific aggregation operator available. Let see how it can work as Min, Max and Sum.

//Finding Min using Min operator
var minUnitPrice = context.Order_Details.Min(od => od.UnitPrice);
//Finding Min using Aggregate operator
var minUnitPriceAgg = context.Order_Details.ToList()
    .Aggregate((od1, od2) => od1.UnitPrice < od2.UnitPrice ? od1 : od2).UnitPrice;

//Finding Max using Max operator
var maxUnitPrice = context.Products.Max(p => p.UnitPrice);
//Finding Max using Aggregate operator
var maxUnitPriceAgg = context.Products.ToList()
    .Aggregate((a, b) => (a.UnitPrice.Value > b.UnitPrice.Value) ? a : b).UnitPrice;

//Finding Sum using Sum operator
var sumUnitPrice = context.Order_Details.Sum(od => od.UnitPrice);
//Finding Sum using default Aggregate operator
var sumUnitPriceAgg = context.Order_Details.ToList()
    .Aggregate((a, b) => new Order_Detail() { UnitPrice = a.UnitPrice + b.UnitPrice }).UnitPrice;
//Finding Sum using overloaded Aggregate operator
var sumUnitPriceAgg1 = context.Order_Details.ToList()
    .Aggregate(0m, (a, b) => a += b.UnitPrice);

Console.WriteLine("minUnitPrice : {0}", minUnitPrice);
Console.WriteLine("minUnitPriceAgg : {0}", minUnitPriceAgg);
Console.WriteLine("");

Console.WriteLine("maxUnitPrice : {0}", maxUnitPrice);
Console.WriteLine("maxUnitPriceAgg : {0}", maxUnitPriceAgg);
Console.WriteLine("");

Console.WriteLine("sumUnitPrice : {0}", sumUnitPrice);
Console.WriteLine("sumUnitPriceAgg : {0}", sumUnitPriceAgg);
Console.WriteLine("sumUnitPriceAgg : {0}", sumUnitPriceAgg1);

Here is the output.

Let’s look closer to overload method of Aggregate which we used to calculate sum.

Default aggregate operator used to return type on which its operating, so in our case we are operating on Order_Detail type hence it returns Order_Detail; that means our accumulator is of type Order_Detail, but as you see that we don’t need whole object of Order_Detail; we need only UnitPrice, at this point you can use overload method of Aggregate operator in which you can specify type of accumulator; which at the end yours return value. In our case we have specify that we need our accumulator to be decimal and initialized it to 0. Accumulator can be of any type it can be even anonymous class, let’s see example of Aggregate operator having anonymous class as accumulator.

var anonAgg = context.Order_Details.ToList()
    .Aggregate(new
    {
        Amount = 0m,
        Month = 0
    }
    , (a, b) => a.Amount > b.UnitPrice ? a
        : new
        {
            Amount = b.UnitPrice,
            Month = b.Order.OrderDate.Value.Month
        }
    );

Console.WriteLine("Amount : {0}, Month : {1}", anonAgg.Amount, anonAgg.Month);

This is bit complex Aggregate, what used here it can not be replaced by any other aggregate operators, let’s quickly understand the query.

Here is the output.

Summary

In this part we learn about set and aggregate operators. Set contains all set operators and behaves same as set operators like Union, Intersect and Except, We also see how you can select distinct records using distinct operator. Further more we learn basic aggregate operators like Sum, Min, Max and Average, we saw how Aggregate operator replace other aggregate operators. At last we saw how we can use Aggregate operator using bit complex example. The next article will explain you about Generation Operator, Quantifier Operators, Partitioning Operators, Element Operators and Other operators.