Monday 5 March 2012

Grouping Data with LINQ

To group data in LINQ, we can use group ... by ... clause in query syntax or GroupBy() in method syntax. We will go through some examples and explanations along this post.


SIMPLE GROUPING
Let's start with simple grouping, below is an example:
// query syntax
var groupedData = from c in context.Customers
                  group c by c.Country;
// method syntax
var groupedData = context.Customers.GroupBy(c => c.Country);
Grouping in LINQ will result in an object of type IEnumerable<IGrouping<TKey,TSource>> which in this case is IEnumerable<IGrouping<String,Customer>>. IGrouping is a special class that has two properties; a key property and an IEnumerable<TSource> property that holds all the items corresponding to the key.

If we try to debug the 'groupedData' object, we will get something like this (you may need to click the image below to make it displayed bigger):
As we can see there's a 'Key' property and another property that contains some items corresponding to the 'Key' value.

To print out these items on screen:
foreach(var groupedItems in groupedData)
{
    Console.WriteLine(string.Format("Key: {0}", groupedItems.Key));
    foreach (var item in groupedItems)
    {
        Console.WriteLine(string.Format("{0} - {1}", item.CompanyName, item.Country));
    }
    Console.WriteLine("----------------------------------");
}


GROUPING WITH MORE THAN ONE KEY
If we want to have a grouping using two keys, we could use group x by new { x.Key1, x.Key2 } in query syntax or GroupBy( x => new { x.Key1, x.Key2 } ) in method syntax. Below is an example:
// query syntax
var groupedData2 = from c in context.Customers
                   group c by new { c.Country, c.City };
// method syntax
var groupedData2 = context.Customers.GroupBy(c => new {c.Country, c.City});

foreach (var groupedItems in groupedData2)
{
    //note that the Keys' names now become part of Key properties; ie. Key.Country and Key.City
    Console.WriteLine(string.Format("Key: {0} - {1}", groupedItems.Key.Country, groupedItems.Key.City));
    foreach (var item in groupedItems)
    {
        Console.WriteLine(string.Format("{0} - {1} - {2}", item.CompanyName, item.City, item.Country));
    }
    Console.WriteLine("----------------------------------");
}


PROJECTION
Here is an example of projecting the result into anonymous type objects:
// query syntax
var groupedData3 = from c in context.Customers
                   group c by c.Country into grp
                   select new
                   {
                       Key = grp.Key,
                       Items = grp.Select(g => new { g.CompanyName, g.Country })
                   };
// method syntax
var groupedData3 = context.Customers.GroupBy(c => c.Country).
                   Select(grp => new {
                                       Key = grp.Key, 
                                       Items = grp.Select(g => new {g.CompanyName, g.Country})
                                     }
                   );

foreach (var groupedItems in groupedData3)
{
    Console.WriteLine(string.Format("Key: {0}", groupedItems.Key));
    foreach (var item in groupedItems.Items)
    {
        Console.WriteLine(string.Format("{0} - {1}", item.CompanyName, item.Country));
    }
    Console.WriteLine("----------------------------------");
}

Below is another example that projects the result into strong typed objects.
The classes (made simple for demonstration purpose):
public class CompanyViewModel
{
    public string Name { get; set; }
    public string Country { get; set; }
}

public class GroupedCompanies
{
    public string CountryKey { get; set; }
    public IEnumerable<CompanyViewModel> Companies { get; set; }
}
Then the query:
var groupedData4 = from c in context.Customers
                   group c by c.Country into grp
                   select new GroupedCompanies
                   {
                       CountryKey = grp.Key,
                       Companies = grp.Select(g => new CompanyViewModel { Name = g.CompanyName, Country = g.Country })
                   };
foreach (GroupedCompanies groupedItems in groupedData4)
{
    Console.WriteLine(string.Format("Key: {0}", groupedItems.CountryKey));
    foreach (CompanyViewModel item in groupedItems.Companies)
    {
        Console.WriteLine(string.Format("{0} - {1}", item.Name, item.Country));
    }
    Console.WriteLine("----------------------------------");
}


GROUPING WITH MORE THAN ONE KEY + PROJECTION
Finally this example shows a combination of grouping with two keys and projection:
// query syntax
var groupedData5 = from c in context.Customers
                   group c by new { c.Country, c.City } into grp
                   select new
                   {
                       Key = grp.Key,
                       Items = grp.Select(g => new { g.CompanyName, g.City, g.Country })
                   };
// method syntax
var groupedData5 = context.Customers.GroupBy( c => new {c.Country, c.City} ).
                   Select( grp => new {
                                       Key = grp.Key, 
                                       Items = grp.Select(g => new {g.CompanyName, g.City, g.Country})
                                      }
                   );

No comments: