>LINQ Operators – Part IV

>

Grouping operators

GroupBy : GroupBy is used to group a sequence into subsets. At first look it feels like it is same as T-SQL "Group By" but it is not. It slices a sequence into multiple "rows" where each row have a "key" and subset of sequence matching to those key. Here is a screen shot how it looks with this simple query:

Customers.GroupBy( c => c.Country )

 

groupBy

GroupBy has an easy syntax with some overloads. You can optionally specify an element selector (projection) and also optionally specify a custom equality comparer. Equality comparer is less used and not different from the sample we used in OrderBy().

Here is a sample with an element selector:

Customers
   .GroupBy (
      c => c.Country,
      c =>
         new 
         {
            CustomerID = c.CustomerID,
            CompanyName = c.CompanyName
         }
   ) 
 
 
groupBySelector

Latter GroupBy selects only two properties of Customers instead of all properties. You can also group by multiple keys:

Customers
   .GroupBy (
      c =>
         new 
         {
            Country = c.Country,
            Region = c.Region
         }, // keySelector
      c =>
         new 
         {
            CustomerID = c.CustomerID,
            CompanyName = c.CompanyName
         }  //elementSelector
   ) 
 

groupByMultipleKeys

In all these queries you are getting a sequence of "groupings". You can "browse" the result with nested foreach() which is sort of nested scan…endscan:

var byCountry = Customers.GroupBy ( c => c.Country );
  
foreach(var g in byCountry)
{
   Console.WriteLine("Country: {0}", g.Key);
   foreach(var customer in g)
   {
      Console.WriteLine("\t {0}\t{1,-50}\t{2}",
         customer.CustomerID,
         customer.CompanyName,
         customer.ContactName);
   }
Partial result:

groupByMultipleKeys3

 

In other words we are dealing with a sequence of sequences. With LinqToSQL inner sequences are queried as they are needed resulting in "groups count" + 1 selects send to backend. This may or may not be the behavior you want depending on where you need it (you can see the series of SQLs in LinqPad’s SQL tab). To prevent this behavior you can convert Customers to an IEnumerable  from IQueryable (or to list, array…) first and then do grouping. For example:

Customers.AsEnumerable().GroupBy( c => c.Country )

AsEnumerable() is a conversion operator that converts the IQueryable to an IEnumerable ( in other words from a remote source to local source – similar to cursors ). We will use AsEnumerable() in some of the following samples that show statistics, nested grouping, distinct sequences.

This sample shows some statistics about Customers grouped by country:

Orders.GroupBy (
  o => o.OrderDate.GetValueOrDefault().Year )
.Select (
   sales =>
   new 
   {
     Year = sales.Key,
     Customers = sales
       .Select( s => s.CustomerID )
       .Distinct()
       .Count(),
     Countries = sales
       .Select( s => s.ShipCountry )
       .Distinct()
       .Count(),
     FirstSale = sales.Min( s => s.OrderDate ),
     LastSale  = sales.Max( s => s.OrderDate )
   }
  ).OrderBy( s => s.Year ) 

This sample groups customerId and countries by years they made an "order":

Orders.AsEnumerable()
  .GroupBy( o => o.OrderDate.GetValueOrDefault().Year )
  .Select (
      sales =>
      new 
      {
        Year = sales.Key,
        Customers = sales
            .Select( s => s.CustomerID )
            .Distinct(),
        Countries = sales
            .Select( s => s.ShipCountry )
            .Distinct()
       }
   ) 

This sample is a multilevel grouping. It first groups customers by first letter of countries and then by countries:

Customers.AsEnumerable()
.GroupBy( c => c.Country [0] )
.Select( c =>
      new 
      {
        c.Key,
        countries =
           c.GroupBy( cn => cn.Country )
            .Select( cs =>
                 new 
                 {
                   cs.Key,
                   clist =
                      cs.Select( cf =>
                        new 
                        {
                           cf.CompanyName,
                           cf.Country,
                           cf.Region})
                        }).OrderBy( cn => cn.Key )
         }).OrderBy( c => c.Key )

There is another grouping operator called ToLookup() that creates one to many dictionary. I reserved it into another category.

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 )

Connecting to %s