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:
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:
Latter GroupBy selects only two properties of Customers instead of all properties. You can also group by multiple keys:
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:
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:
This sample groups customerId and countries by years they made an "order":
This sample is a multilevel grouping. It first groups customers by first letter of countries and then by countries:
There is another grouping operator called ToLookup() that creates one to many dictionary. I reserved it into another category.