Chapter 9 - LINQ Operators

Simple Local Filter

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };

IEnumerable<string> query =
  names.Where (name => name.EndsWith ("y"));

query.Dump ("In fluent syntax");

query =
  from n in names
  where n.EndsWith ("y")
  select n;
  
query.Dump ("In query syntax");

Multiple Where Clauses

var names = new[] { "Tom", "Dick", "Harry", "Mary", "Jay" }.AsQueryable();

var query =
  from n in names
  where n.Length > 3
  let u = n.ToUpper()
  where u.EndsWith ("Y")
  select u;
  
query.Dump();

Indexed Filtering

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };

names.Where ((n, i) => i % 2 == 0).Dump ("Skipping every second element");

Contains and LIKE when querying a database

Customers.Where (c => c.Name.Contains ("a"))  
  .Dump ("Notice the SQL translation uses LIKE");

Customers.Where (c => c.Name.StartsWith ("J"))  
  .Dump ("StartsWith and EndsWith also translate to LIKE");

Customers.Where (c => EF.Functions.Like (c.Name, "_ar%y"))
  .Dump ("A more complex use of LIKE");

IN and NOT IN when querying a database

string[] chosenOnes = { "Tom", "Jay" };

Customers.Where (c => chosenOnes.Contains (c.Name))
  .Dump ("This translates to SQL WHERE ... IN");
  
Customers.Where (c => !chosenOnes.Contains (c.Name))
  .Dump ("This translates to SQL WHERE NOT ... IN");

Extra - Where-based Subqueries

// We saw how to construct a basic subquery in Chapter 8:

var names = new[] { "Tom", "Dick", "Harry", "Mary", "Jay" }.AsQueryable();
(
  from n in names
  where n.Length == names.Min (n2 => n2.Length)
  select n
)
.Dump ("Basic subquery");

// The same principle works well when querying a database:
(
  from c in Customers
  where c.Name.Length == Customers.Min (c2 => c2.Name.Length)
  select c
)
.Dump ("Basic subquery, EF Core");

// We can construct similar subqueries across association properties:
(
  from c in Customers
  where c.Purchases.Any (p => p.Price > 1000)
  select c
)
.Dump ("Customers who have purchased at least one item > $1000");

Extra - Where-based Subqueries with let

// The let keyword in query expressions comes in useful with subqueries: it lets
// you re-use the subquery in the projection:

from c in Customers
let highValuePurchases = c.Purchases.Where (p => p.Price > 1000)
where highValuePurchases.Any()
select new
{
  c.Name,
  highValuePurchases
}

// We'll see more examples of this in the following section, "Projecting".

Take and Skip

// We need a long list of names for this example, so will indulge in a little
// reflection (using LINQ, of course!) The following query extracts all type 
// names in the System.CoreLib assembly:

string[] typeNames =
  (from t in typeof (int).Assembly.GetTypes() select t.Name).ToArray();

typeNames
  .Where (t => t.Contains ("Exception"))
  .OrderBy (t => t)
  .Take (20)
  .Dump ("The first 20 matches");

typeNames
  .Where   (t => t.Contains ("Exception"))
  .OrderBy (t => t)
  .Skip (20)
  .Take (20)
  .Dump ("Matches 21 through 40");

Take and Skip when querying a database

// The following skips the first 5 purchases (ordered by price) and takes the next 3:

Purchases.OrderBy (p => p.Price).Skip (5).Take(3)

// Take a look at the SQL.  So much easier with LINQ!

TakeWhile and SkipWhile

int[] numbers = { 3, 5, 2, 234, 4, 1 };

numbers.TakeWhile (n => n < 100).Dump ("TakeWhile");
numbers.SkipWhile (n => n < 100).Dump ("SkipWhile");

Distinct

"HelloWorld".Distinct()

Distinct querying a database

Purchases.Select (p => p.Description).Distinct()

Simple Local Select

var query =
  from f in FontFamily.Families
  select f.Name;

query.Dump ("In query syntax");


FontFamily.Families.Select (f => f.Name)   .Dump ("In lambda syntax");

Projecting into Anonymous Type

from f in FontFamily.Families.AsQueryable() 
select new 
{
  f.Name,
  LineSpacing = f.GetLineSpacing (FontStyle.Bold)
}

Select - No Transformation

from f in FontFamily.Families.AsQueryable() 
where f.IsStyleAvailable (FontStyle.Strikeout)
select f

Select - Indexed

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };

names.Select ((s,i) => i + "=" + s)  .Dump();

Select-Subqueries and Object Hierarchies

string sampleDirectory = Environment.GetFolderPath  (Environment.SpecialFolder.MyDocuments);

DirectoryInfo[] dirs = new DirectoryInfo (sampleDirectory).GetDirectories();

var query =
  from d in dirs  
  where (d.Attributes & FileAttributes.System) == 0
  select new
  {
    DirectoryName = d.FullName,
    Created = d.CreationTime,
    Files = from f in d.GetFiles()
      where (f.Attributes & FileAttributes.Hidden) == 0
      select new { FileName = f.Name, f.Length, }
  };

query.Dump();

// Here's how to enumerate the results manually:

foreach (var dirFiles in query)
{
  Console.WriteLine ("Directory: " + dirFiles.DirectoryName);
  foreach (var file in dirFiles.Files)
    Console.WriteLine ("    " + file.FileName + "Len: " + file.Length);
}

Select-Subqueries querying a database

// Requires .ToList() because EF Core 3 cannot create Queryables in the select result.
// Punted for version 3 but may be resolved in the future. 
// Issue tracked at http://github.com/aspnet/EntityFrameworkCore/issues/16314

from c in Customers
select new
{
  c.Name,
  Purchases = (
    from p in Purchases
    where p.CustomerID == c.ID && p.Price > 1000
    select new { p.Description, p.Price }).ToList()
}

Select-Subqueries using Assocations

from c in Customers
select new
{
  c.Name,
  Purchases =
    from p in c.Purchases
    where p.Price > 1000
    select new { p.Description, p.Price }
}

Select-Subqueries Filtered

from c in Customers
where c.Purchases.Any (p => p.Price > 1000)
select new
{
  c.Name,
  Purchases =
    from p in c.Purchases
    where p.Price > 1000
    select new { p.Description, p.Price }
}

Select-Subqueries Filtered with let

from c in Customers
let highValueP =
  from p in c.Purchases
  where p.Price > 1000
  select new { p.Description, p.Price }
where highValueP.Any()
select new 
{
  c.Name,
  Purchases = highValueP
}

Simple SelectMany

var fullNames = new[] { "Anne Williams", "John Fred Smith", "Sue Green" }.AsQueryable();

IEnumerable<string[]> query1 = fullNames.Select     (name => name.Split());
IEnumerable<string>   query2 = fullNames.SelectMany (name => name.Split());

query1.Dump ("A simple Select gives us a hierarchical result set");
query2.Dump ("SelectMany gives us a flat result set");

IEnumerable<string> query3 = 
  from fullName in fullNames
  from name in fullName.Split()     // Translates to SelectMany
  select name;

query3.Dump ("Same SelectMany query, but in query syntax");

Outer Range Variables

var fullNames = new[] { "Anne Williams", "John Fred Smith", "Sue Green" }.AsQueryable();

IEnumerable<string> query =
  from fullName in fullNames                       // fullName = outer variable
  from name in fullName.Split()                    // name = iteration variable
  select name + " came from " + fullName;
  
query.Dump ("Both variables are in scope");

Try Translating this to Fluent Syntax!

// (Without cheating, by looking at the lambda tab!)

var fullNames = new[] { "Anne Williams", "John Fred Smith", "Sue Green" }.AsQueryable();

IEnumerable<string> query =
  from fullName in fullNames
  from name in fullName.Split()
  orderby fullName, name
  select name + " came from " + fullName;
  
query.Dump();

The Solution

string[] fullNames = { "Anne Williams", "John Fred Smith", "Sue Green" };

IEnumerable<string> query1 =
  from fullName in fullNames
  from x in fullName.Split().Select (name => new { name, fullName } ) 
  orderby x.fullName, x.name
  select x.name + " came from " + x.fullName;
  
query1.Dump ("The intermediate solution");

IEnumerable<string> query2 = fullNames
  .SelectMany (fName => fName.Split().Select (name => new { name, fName } ))
  .OrderBy (x => x.fName)
  .ThenBy  (x => x.name)
  .Select  (x => x.name + " came from " + x.fName);

query2.Dump ("Final translation to fluent syntax");

NOTE - Another way to call SelectMany

// SelectMany is overloaded to help you (slightly!) with queries that perform a Select
// within SelectMany's lambda expression. To illustrate, consider the following query:

string[] fullNames = { "Anne Williams", "John Fred Smith", "Sue Green" };

var query1 =
  fullNames
  .SelectMany (fName => fName.Split().Select (name => new { name, fName } ));

// We can re-write this as follows, and get the same result:

var query2 =
  fullNames
  .SelectMany (fName => fName.Split(), (fName, name) => new { name, fName } );
  
query1.Dump ("Using SelectMany+Select");
query2.Dump ("Using SelectMany with a collection selector + result selector");

// Instead of performing a Select inside the SelectMany, we supply SelectMany with two
// lambda expressions:
//   (a) the collection selector (which would otherwise come just before the .Select)
//   (b) the result selector (which would otherwise be fed into the .Select).
//
// C#, in the current release, favours this version of SelectMany when translating
// query syntax queries. Functionally, it has the same effect as performing
// a Select within a SelectMany.

Expanding and Flattening

from c in Customers
from p in c.Purchases
select c.Name + " bought a " + p.Description

Cross Product

var numbers = new[] { 1, 2, 3 }.AsQueryable();
var letters = new[] { "a", "b" }.AsQueryable();

IEnumerable<string> query =
  from n in numbers
  from l in letters
  select n.ToString() + l;
  
query.Dump();

Cross Product Join

var players = new[] { "Tom", "Jay", "Mary" }.AsQueryable();

IEnumerable<string> query =
  from name1 in players
  from name2 in players
  select name1 + " vs " + name2;

query.Dump();

Cross Product Join - Filtered

var players = new[] { "Tom", "Jay", "Mary" }.AsQueryable();

IEnumerable<string> query =
  from name1 in players
  from name2 in players
  where name1.CompareTo (name2) < 0
  orderby name1, name2
  select name1 + " vs " + name2;

query.Dump();

Database - Cross Join

from c in Customers
from p in Purchases
select c.Name + " might have bought a " + p.Description

Database - Standard (equi) Inner Join

var query1 =
  from c in Customers
  from p in Purchases
  where c.ID == p.CustomerID
  select c.Name + " bought a " + p.Description;

var query2 =
  from c in Customers
  from p in c.Purchases
  select c.Name + " bought a " + p.Description;

query1.Dump ("Using SelectMany to manually join");
query2.Dump ("Using an Association to achieve the same result");

Database - Filtered Association

from c in Customers
where c.Name.StartsWith ("T")
from p in c.Purchases
select new { c.Name, p.Description }

Database - Joining to the Parent

// Don't make this harder than it needs to be!

from p in Purchases
select new
{
   p.Description,
   CustomerName = p.Customer.Name
}

Database - Outer Joins with SelectMany

(
  from c in Customers
  from p in c.Purchases
  where p.Price > 1000
  select new { c.Name, p.Description, p.Price }
)
.Dump ("An inner join");

(
  from c in Customers
  from p in c.Purchases.DefaultIfEmpty()
  select new { c.Name, p.Description, Price = (decimal?) p.Price }
)
.Dump ("An outer join (without the predicate)");

Local query - Outer Joins with SelectMany

Customer[] localCustomerCollection = Customers.ToArray();

var query =
  from c in localCustomerCollection
  from p in c.Purchases.DefaultIfEmpty()
  select new
  {
    Descript = p == null ? null : p.Description,
    Price = p == null ? (decimal?) null : p.Price
  };

query.Dump();

Database - Filtered Outer Joins

from c in Customers
from p in c.Purchases.Where (p => p.Price > 1000).DefaultIfEmpty()
select new
{
  c.Name,
  p.Description,
  Price = (decimal?)p.Price
}

// We could do this instead:

//var dbQuery =
//from c in Customers
//select new
//{
//  c.Name,
//  Purchases =
//    from p in c.Purchases
//    where p.Price > 1000
//    select new { p.Description, p.Price }
//};
//
//dbQuery.Dump();

// It achieves the same result (outer join), except that you end up with shaped data 
// instead of flat data. Shaped data is usually easier to work with, anyway, and
// if you really need flat data, you could always flatten it via a local query at
// the end:

//var flat =
//  from result in dbQuery.AsEnumerable()
//  from purchase in result.Purchases.DefaultIfEmpty()
//  select new
//  {
//    result.Name,
//    purchase?.Description,
//    purchase?.Price
//  };
//  
//flat.Dump ("If you really need flat data");

// Yet another option is to check out whether the joining operators can be
// used to achieve this result in EFCore.

Extra - Database - Manual Outer Joins

// You can also use a Where subquery to join manually, rather than 
// through Association properties:

from c in Customers
from p in Purchases.Where (p => p.CustomerID == c.ID).DefaultIfEmpty()
select new
{
  c.Name,
  p.Description,
  Price = (decimal?) p.Price
}

// You can use a similar strategy to perform nonequi-outer joins.

Simple Join

// Note: before delving into this section, make sure you've read the preceding two
// sections: Select and SelectMany. The Join operators are actually unnecessary
// when querying a database, and the equivalent of SQL inner and outer joins is 
// most easily achieved using Select/SelectMany and subqueries!

from c in Customers
join p in Purchases on c.ID equals p.CustomerID
select c.Name + " bought a " + p.Description

Join vs SelectMany

Customer[] customers = Customers.ToArray();
Purchase[] purchases = Purchases.ToArray();

var slowQuery =
  from c in customers
  from p in purchases where c.ID == p.CustomerID
  select c.Name + " bought a " + p.Description;

var fastQuery =
  from c in customers
  join p in purchases on c.ID equals p.CustomerID
  select c.Name + " bought a " + p.Description;
                
slowQuery.Dump ("Slow local query with SelectMany");                
fastQuery.Dump ("Fast local query with Join");

Join - Reversing Sequences

var query1 =
  from c in Customers
  join p in Purchases on c.ID equals p.CustomerID
  select c.Name + " bought a " + p.Description;
  
var query2 =
  from p in Purchases
  join c in Customers on p.CustomerID equals c.ID
  select c.Name + " bought a " + p.Description;

query1.Dump();
query2.Dump();

Joining Multiple Sequences

from c in Customers
join p in Purchases on c.ID equals p.CustomerID           // first join
join pi in PurchaseItems on p.ID equals pi.PurchaseID     // second join
select new
{
  c.Name, p.Description, pi.Detail
}

Joining on Multiple Keys

PropertyInfo[] stringProps = typeof (string).GetProperties().Dump ("String Props");
PropertyInfo[] builderProps = typeof (StringBuilder).GetProperties().Dump ("StringBuilder Props");

var query =
  from s in stringProps
  join b in builderProps
    on new { s.Name, s.PropertyType } equals new { b.Name, b.PropertyType }
  select new
  {
    s.Name,
    s.PropertyType,
    StringToken = s.MetadataToken,
    StringBuilderToken = b.MetadataToken
  };
  
query.Dump ("Join query");

Joining in Fluent Syntax

var querySyntax =
  from c in Customers
  join p in Purchases on c.ID equals p.CustomerID
  select new
  {
    c.Name, p.Description, p.Price
  };

var fluentSyntax =
  Customers.Join (         // outer collection
    Purchases,             // inner collection
    c => c.ID,             // outer key selector
    p => p.CustomerID,     // inner key selector
    (c, p) => new          // result selector 
    {
      c.Name, p.Description, p.Price
    }    
  );
  
querySyntax.Dump ("Query syntax");
fluentSyntax.Dump ("Same query in fluent syntax");

Joining in Fluent Syntax with OrderBy

var querySyntax =
  from c in Customers
  join p in Purchases on c.ID equals p.CustomerID
  orderby p.Price
  select c.Name + " bought a " + p.Description + " for $" + p.Price;
  
var fluentSyntax =
  Customers.Join (           // outer collection
    Purchases,               // inner collection
    c => c.ID,               // outer key selector
    p => p.CustomerID,       // inner key selector
    (c, p) => new { c, p }   // result selector 
  )
  .OrderBy (x => x.p.Price)
  .Select  (x => x.c.Name + " bought a " + x.p.Description + " for $" + x.p.Price);
  
querySyntax.Dump ("Query syntax");
fluentSyntax.Dump ("Same query in fluent syntax");

Simple GroupJoin

// NOTE: EF Core currently requires GroupBy without an aggregate expression to be explicitly performed client-side. This is because
//       the query translates into a GroupJoin, which SQL databases don't natively support unless an aggregate expression appears in
//       the SELECT list. The EF Core team takes the position that implicit, rather than explicit, client-side evaluation can mask
//       performance pitfalls. Subsequently, the developer must explicitly force client-side evaluation. This results in two queries
//       to the database, where Linq to SQL generated a single, more efficient query. The EF Core team is tracking the issue and
//       may revisit it at a future date.
//       http://github.com/aspnet/EntityFrameworkCore/issues/17068
from c in Customers.AsEnumerable()
join p in Purchases on c.ID equals p.CustomerID
into custPurchases
select custPurchases

GroupJoin with Outer Range Variable

Customer[] customers = Customers.ToArray();
Purchase[] purchases = Purchases.ToArray();

var groupJoinQuery =
  from c in customers
  join p in purchases on c.ID equals p.CustomerID
  into custPurchases
  select new
  {
    CustName = c.Name,
    custPurchases
  };

var selectEquivalent =
  from c in customers
  select new
  {
    CustName = c.Name,
    custPurchases = purchases.Where (p => c.ID == p.CustomerID)
  };

@"The GroupJoin query is more efficient in this case, because we're querying
arrays (i.e. local collections).".Dump();
  
groupJoinQuery.Dump ("Group Join Query");
selectEquivalent.Dump ("Equivalent with Select");

Same Query to a database

// Requires .ToList() because EF Core 3 cannot create Queryables in the select result.
// Punted for version 3 but may be resolved in the future. 
// Issue tracked at http://github.com/aspnet/EntityFrameworkCore/issues/16314

var groupJoinQuery =
  from c in Customers.AsEnumerable()
  join p in Purchases.AsEnumerable() on c.ID equals p.CustomerID
  into custPurchases
  select new
  {
    CustName = c.Name,
    custPurchases
  };

var selectEquivalent =
  from c in Customers 
  select new
  {
    CustName = c.Name,
    custPurchases = Purchases.Where (p => c.ID == p.CustomerID).ToList()
  };

@"Notice in the SQL results pane, that there's no difference between these two queries.
The second query, however, is more flexibile.".Dump();  

groupJoinQuery.Dump ("Group Join Query");
selectEquivalent.Dump ("Equivalent with Select");

GroupJoin with Inner Join Filter

// NOTE: EF Core currently requires GroupBy without an aggregate expression to be explicitly performed client-side. This is because
//       the query translates into a GroupJoin, which SQL databases don't natively support unless an aggregate expression appears in
//       the SELECT list. The EF Core team takes the position that implicit, rather than explicit, client-side evaluation can mask
//       performance pitfalls. Subsequently, the developer must explicitly force client-side evaluation. This results in two queries
//       to the database, where Linq to SQL generated a single, more efficient query. The EF Core team is tracking the issue and
//       may revisit it at a future date.
//       http://github.com/aspnet/EntityFrameworkCore/issues/17068

from c in Customers.AsEnumerable()
join p in Purchases on c.ID equals p.CustomerID
into custPurchases
where custPurchases.Any()
select new
{
  CustName = c.Name,
  custPurchases
}

GroupJoin with Pre-Filter

// NOTE: EF Core currently requires GroupBy without an aggregate expression to be explicitly performed client-side. This is because
//       the query translates into a GroupJoin, which SQL databases don't natively support unless an aggregate expression appears in
//       the SELECT list. The EF Core team takes the position that implicit, rather than explicit, client-side evaluation can mask
//       performance pitfalls. Subsequently, the developer must explicitly force client-side evaluation. This results in two queries
//       to the database, where Linq to SQL generated a single, more efficient query. The EF Core team is tracking the issue and
//       may revisit it at a future date.
//       http://github.com/aspnet/EntityFrameworkCore/issues/17068

from c in Customers.AsEnumerable()
join p in Purchases.Where (p2 => p2.Price > 1000)
  on c.ID equals p.CustomerID
into custPurchases
where custPurchases.Any()
select new
{
  CustName = c.Name,
  custPurchases
}

Outer Joins

from c in Customers
join p in Purchases on c.ID equals p.CustomerID into custPurchases
from cp in custPurchases.DefaultIfEmpty()
select new
{
  CustName = c.Name,
  Price = cp == null ? (decimal?) null : cp.Price
}

Joining with Lookups

Customer[] customers = Customers.ToArray();
Purchase[] purchases = Purchases.ToArray();

ILookup<int?, Purchase> purchLookup = purchases.ToLookup (p => p.CustomerID, p => p);

var inner =
  from c in customers
  from p in purchLookup [c.ID]
  select new { c.Name, p.Description, p.Price };

inner.Dump ("Inner join equivalent");

var outer =
  from c in customers
  from p in purchLookup [c.ID].DefaultIfEmpty()
  select new
  {
    c.Name,
    Descript = p == null ? null : p.Description,
    Price = p == null ? (decimal?) null : p.Price
  };
  
outer.Dump ("Outer join equivalent");

var groupJoin =
  from c in customers
  select new 
  {
     CustName = c.Name,
     CustPurchases = purchLookup [c.ID]
  };

groupJoin.Dump ("GroupJoin equivalent");

Extra - Weights Puzzle

// Luke Hoban, from Microsoft, has found a way to solve a puzzle with a LINQ query!
// His solution is dramatically faster with Join than SelectMany:
//
// http://blogs.msdn.com/lukeh/archive/2007/03/19/using-linq-to-solve-puzzles.aspx
//
// Here's the query in LINQPad: 

from a in Enumerable.Range(1, 13)
join b in Enumerable.Range(1, 13) on 4 * a equals b
from c in Enumerable.Range(1, 13)
join d in Enumerable.Range(1, 13) on 5 * c equals d
from e in Enumerable.Range(1, 13)
join f in Enumerable.Range(1, 13) on 3 * e equals 2 * f  
join g in Enumerable.Range(1, 13) on 2 * (c + d) equals 3 * g
from h in Enumerable.Range(1, 13)
join i in Enumerable.Range(1, 13) on 3 * h - 2 * (e + f) equals 3 * i
from j in Enumerable.Range(1, 13)
join k in Enumerable.Range(1, 13) on 3 * (a + b) + 2 * j - 2 * (g + c + d) equals k
from l in Enumerable.Range(1, 13)
join m in Enumerable.Range(1, 13) on (h + i + e + f) - l equals 4 * m
where (4 * (l + m + h + i + e + f) == 3 * (j + k + g + a + b + c + d))
select new
{
  a, b, c, d, e, f, g, h, i, j, k, l, m,
    Total = a + b + c + d + e + f + g + h + i + j + k + l + m
}

Simple Ordering

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };

names.OrderBy (s => s).Dump ("Ordered alphabetically");
names.OrderBy (s => s.Length).Dump ("Ordered by length");

Ordering on Multiple Expressions

var names = new[] { "Tom", "Dick", "Harry", "Mary", "Jay" }.AsQueryable();

names.OrderBy (s => s.Length).ThenBy (s => s)
  .Dump ("By length, then alphabetically");
  
names.OrderBy (s => s.Length).ThenBy (s => s[1]).ThenBy (s => s[0])
  .Dump ("By length, then second character, then first character");

(
  from s in names
  orderby s.Length, s[1], s[0]
  select s
)
.Dump ("Same query in query syntax");

Descending

Purchases
  .OrderByDescending (p => p.Price)
  .ThenBy (p => p.Description)
  .Dump ("In fluent syntax");

(
  from p in Purchases
  orderby p.Price descending, p.Description
  select p
)
.Dump ("In query syntax");

Comparers and Collations

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };

names.OrderBy (n => n, StringComparer.CurrentCultureIgnoreCase)
  .Dump ("Case insensitive ordering");

(
  from c in Customers
  orderby c.Name.ToUpper()
  select c.Name
)
.Dump ("Closest equivalent when querying a SQL Server database with default collation");

IOrderedEnumerable and IOrderedQueryable

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };

IOrderedEnumerable<string> query1 = names.OrderBy (s => s.Length);
IOrderedEnumerable<string> query2 = query1.ThenBy (s => s);

query2.Dump();

var query = names.OrderBy (s => s.Length).AsEnumerable();
query = query.Where (n => n.Length > 3);

query.Dump();

Simple GroupBy

string[] files = Directory.GetFiles (Path.GetTempPath()).Take (100).ToArray();

files.GroupBy (file => Path.GetExtension (file))
  .Dump ("Your temporary files, grouped by extension.");

if (files.Length == 100) "(Maybe you need a cleanup!)".Dump();

Enumerating groupings

string[] files = Directory.GetFiles (Path.GetTempPath()).Take (100).ToArray();

IEnumerable<IGrouping<string,string>> query = 
  files.GroupBy (file => Path.GetExtension (file));

foreach (IGrouping<string,string> grouping in query)
{
  Console.WriteLine ("Extension: " + grouping.Key);
  
  foreach (string filename in grouping)
    Console.WriteLine ("   - " + filename);
}

Transforming the Input Elements

var files = Directory.GetFiles (Path.GetTempPath()).Take (100).ToArray().AsQueryable();

files.GroupBy (file => Path.GetExtension (file), file => file.ToUpper())
  .Dump ("In Fluent Syntax");
  
(
  from file in files
  group file.ToUpper() by Path.GetExtension (file)
)
.Dump ("In query syntax");

Group with Query Continuation

var files = Directory.GetFiles (Path.GetTempPath()).Take (100).ToArray().AsQueryable();

(
  from file in files
  group file.ToUpper() by Path.GetExtension (file) into grouping
  orderby grouping.Key
  select grouping
)
.Dump();

Query Continuation - Filtered

var files = Directory.GetFiles (Path.GetTempPath()).AsQueryable();

(
  from file in files
  group file.ToUpper() by Path.GetExtension (file) into grouping
  where grouping.Count() < 5
  select grouping
)
.Dump ("Extensions with less then five files");

Group - Aggregation Only

string[] votes = { "Dogs", "Cats", "Cats", "Dogs", "Dogs" };

IEnumerable<string> query = from vote in votes
                            group vote by vote into g
                            orderby g.Count() descending
                            select g.Key;

string winner = query.First();    // Dogs
winner.Dump();

Grouping when querying a database

from p in Purchases
group p.Price by p.Date.Year into salesByYear
select new
{
  Year       = salesByYear.Key,
  TotalValue = salesByYear.Sum()
}

Extra - Filtered Grouping with database

// The following groups purchases by year, then returns only those groups where
// the average purchase across the year was greater than $1000:

from p in Purchases
group p.Price by p.Date.Year into salesByYear
where salesByYear.Average (x => x) > 1000
select new
{
  Year       = salesByYear.Key,
  TotalSales = salesByYear.Count(),
  AvgSale    = salesByYear.Average(),
  TotalValue = salesByYear.Sum()
}

Grouping by Multiple Keys

from n in new[] { "Tom", "Dick", "Harry", "Mary", "Jay" }.AsQueryable()
group n by new
{
  FirstLetter = n[0],
  Length = n.Length
}

Extra - Nested Grouping

// (The following example requires the NORTHWIND database to run. You can
//  download the Northwind database here:
//  http://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases
//
//  Follow the installation instructions at that URL then set your connection in this query window to
//  your Northwind database.)
// 
// Suppose you want to project a nested grouping, for example, producing
// a hierarchical output of countries, cities and post codes as follows:
//
//   Brazil
//      Campinas
//         04876-786
//      Resende
//         08737-363
//      Rio de Janeiro
//         02389-673
//         02389-890
//         05454-876
//
// We'll assume the country, city and post code information is all in one table called Orders 
// (as is it in the NORTHWIND sample database).
//
// To write this query, you need to nest one "group by" within the projection of another:

var query =
  from o in Orders
  group o by o.ShipCountry into countryGroups
  select new
  {
    Country = countryGroups.Key,
    Cities =
      from cg in countryGroups
      group cg.ShipPostalCode by cg.ShipCity into cityGroups
      select new
      {
        City = cityGroups.Key,
        PostCodes = cityGroups.Distinct()
      }
};

query.Dump();

// In this case, at the bottom level we are interested only in a simple list of postal codes (not in
// any further information about those orders) so we can use a simple Distinct to get the desired list.
//
// Here's how to programmatically enumerate the result:

foreach (var countryGroup in query)
{
  Console.WriteLine (countryGroup.Country);
  foreach (var cityGroup in countryGroup.Cities)
  {
    Console.WriteLine ("   " + cityGroup.City);
    foreach (string postCode in cityGroup.PostCodes)
      Console.WriteLine ("      " + postCode);
  }
}

Concat and Union

int[] seq1 = { 1, 2, 3 }, seq2 = { 3, 4, 5 };

seq1.Concat (seq2).Dump ("Concat");
seq1.Union  (seq2).Dump ("Union");

Intersect and Except

int[] seq1 = { 1, 2, 3 }, seq2 = { 3, 4, 5 };

seq1.Intersect (seq2).Dump ("Intersect");
seq1.Except    (seq2).Dump ("seq1.Except (seq2)");
seq2.Except    (seq1).Dump ("seq2.Except (seq1)");

Extra - Concat and Union (database)

IQueryable<string> purchaseDescriptions = Purchases.Select (p => p.Description);
IQueryable<string> itemDescriptions = PurchaseItems.Select (pi => pi.Detail);

purchaseDescriptions.Union (itemDescriptions)
  .Dump ("Purchase and purchase item descriptions flattened with Union (notice the UNION in the SQL view)");
  
purchaseDescriptions.Concat (itemDescriptions)
  .Dump ("Purchase and purchase item descriptions flattened with Concat (notice the UNION ALL in the SQL view)");

Extra - Intersect and Except (database)

IQueryable<string> purchaseDescriptions = Purchases.Select (p => p.Description);
IQueryable<string> itemDescriptions = PurchaseItems.Select (pi => pi.Detail);

purchaseDescriptions.Intersect (itemDescriptions)
  .Dump ("Purchases that have an identical description in PurchaseItem");
  
purchaseDescriptions.Except (itemDescriptions)
  .Dump ("Purchases that have no corresponding description in PurchaseItem");
  
itemDescriptions.Except (purchaseDescriptions)
  .Dump ("PurchaseItems that have no corresponding description in Purchase");

Zip operator

int[] numbers = { 3, 5, 7 };
string[] words = { "three", "five", "seven", "ignored" };
IEnumerable<string> zip = numbers.Zip (words, (n, w) => n + "=" + w);

zip.Dump();

Simple Cast

ArrayList classicList = new ArrayList();
classicList.AddRange ( new int[] { 3, 4, 5 } );

IEnumerable<int> sequence1 = classicList.Cast<int>();

sequence1.Dump ("Because sequence1 implements IEnumerable<int>, we can run queries on it");

Cast versus OfType

ArrayList classicList = new ArrayList();
classicList.AddRange ( new int[] { 3, 4, 5 } );

DateTime offender = DateTime.Now;
classicList.Add (offender);

IEnumerable<int>
  ofTypeSequence = classicList.OfType<int>(),
  castSequence = classicList.Cast<int>();
  
ofTypeSequence.Dump ("Notice that the offending DateTime element is missing");

try
{ 
  castSequence.Dump();
}
catch (InvalidCastException ex)
{
  ex.Message.Dump ("Notice what the offending DateTime element does to the Cast sequence");
}

Cast versus the C# Cast Operator

int[] integers = { 1, 2, 3 };

IEnumerable<long> test1 = integers.OfType<long>();
test1.Dump ("OfType returns no results");

IEnumerable<long> test2 = integers.Cast<long>();
test2.Dump ("Cast returns a sequence of three long integers!"); // Throws an exception. See Chapter 9, "OfType and Cast"

// Here's an alternative approach, using a projection:

integers.Select (s => (long) s).Dump ("The correct approach");

Cast in Query Syntax

// Query syntax provides a shortcut for using the Cast operator on the 
// input sequence. You simply include the type name directly after the from clause:

object[] untyped = { 1, 2, 3 };

var query1 =
  from i in untyped.Cast<int>()      // Without syntactic shortcut
  select i * 10;

var query2 =
  from int i in untyped       // Notice we've slipped in "int"
  select i * 10;

query1.Dump ("Explicitly calling Cast operator");
query2.Dump ("Syntactic shortcut for same query");

ToArray and ToList

IEnumerable<string> query = "one two two three".Split().Distinct();

string[] toArray = query.ToArray();
List<string> toList = query.ToList();

toArray.Dump(); 
toList.Dump();

ToDictionary

Dictionary<int, string> idToName = Customers.ToDictionary (c => c.ID, c => c.Name);

idToName.Dump();

First and Last

int[] numbers  = { 1, 2, 3, 4, 5 };

numbers.First().Dump ("First");
numbers.Last().Dump ("Last");

numbers.First (n => n % 2 == 0).Dump ("First even number");
numbers.Last (n => n % 2 == 0).Dump ("Last even number");

try 
{
  numbers.First (n => n > 10);
}
catch (Exception ex) 
{
  ex.Message.Dump ("The First number > 10");
}

FirstOrDefault

int[] numbers  = { 1, 2, 3, 4, 5 };

numbers.FirstOrDefault (n => n > 10)
  .Dump ("The FirstOrDefault number > 10");

Customers.FirstOrDefault (c => c.Name == "Harry")
  .Dump ("First customer called 'Harry'");
  
Customers.FirstOrDefault (c => c.Purchases.Any (p => p.Price > 1000))
  .Dump ("First customer with a purchase > $1000");
  
Customers.FirstOrDefault (c => c.Name == "Dylan")
  .Dump ("First customer called 'Dylan', or default (null) if no match");

Single

int[] numbers  = { 1, 2, 3, 4, 5 };

numbers.Single (n => n % 3 == 0).Dump ("The Single number divisible by 3");

try
{ 
  numbers.Single (n => n % 2 == 0);
}
catch (Exception ex)
{
  ex.Message.Dump ("The Single number divisible by 2");
}

SingleOrDefault

int[] numbers  = { 1, 2, 3, 4, 5 };

try
{
  numbers.Single (n => n > 10);
}
catch (Exception ex)
{
  ex.Message.Dump ("The Single number > 10");
}

numbers.SingleOrDefault (n => n > 10).Dump ("The SingleOrDefault number > 10");

try
{
  numbers.SingleOrDefault (n => n % 2 == 0);
}
catch (Exception ex)
{
  ex.Message.Dump ("The SingleOrDefault number divisible by two");
}

SingleOrDefault (database)

Customer cust = Customers.Single (c => c.ID == 3);

cust.Dump();

ElementAt

int[] numbers  = { 1, 2, 3, 4, 5 };

numbers.ElementAt (2).Dump ("ElementAt (2)");
try
{
  numbers.ElementAt (9); 
}
catch (Exception ex)
{
  ex.Message.Dump ("ElementAt (9)");
}

numbers.ElementAtOrDefault (9).Dump ("ElementAtOrDefault (9)");

Count

// Note: refer to 'Grouping' for examples on using aggregations in groupby clauses.

new int[] { 5, 6, 7 }.Count()
  .Dump ("Simple Count");

"pa55w0rd".Count (c => char.IsDigit (c))
  .Dump ("Count with predicate");

Min and Max

int[] numbers = { 28, 32, 14 };

numbers.Min().Dump ("Min");
numbers.Max().Dump ("Max");

numbers.Max (n => n % 10).Dump ("Maximum remainder after dividing by ten");

Min and Max (database)

try
{
  Purchases.Min ();
}
catch (Exception ex)
{
  ex.Message.Dump ("Purchases.Min()");
}

Purchases.Min (p => p.Price).Dump ("Lowest price");

Purchases
  .Where (p => p.Price == Purchases.Min (p2 => p2.Price))
  .FirstOrDefault()
  .Dump ("The cheapest purchase");

Sum and Average

decimal[] numbers  = { 3, 4, 8 };

numbers.Sum()      .Dump ("Sum");
numbers.Average()  .Dump ("Average (mean)");

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };
names.Sum (s => s.Length).Dump ("Combined string lengths");

Average Return Types

// The following generates a compile-time error:

int avg = new int[] { 3, 4 }.Average();

Average Upscaling

double avg1 = new int[] { 3, 4 }.Average(); 
double avg2 = new int[] { 3, 4 }.Average (n => (double) n);

avg1.Dump();
avg2.Dump();

Average (database)

from c in Customers
where c.Purchases.Average (p => p.Price) > 500
select c.Name

Aggregate

int[] numbers = { 1, 2, 3 };
numbers.Aggregate (0, (seed, n) => seed + n).Dump();

Contains and Any

new int[] { 2, 3, 4 }.Contains (3)               .Dump ("Contains (3)");
new int[] { 2, 3, 4 }.Any (n => n == 3)          .Dump ("Any (n => n == 3)");
new int[] { 2, 3, 4 }.Any (n => n > 10)          .Dump ("Has a big number");
new int[] { 2, 3, 4 }.Where (n => n > 10).Any()  .Dump ("Has a big number");

All

Customers.Where (c => c.Purchases.All (p => p.Price < 100))

SequenceEqual

var query = "Hello".Distinct();

query.SequenceEqual ("Helo").Dump();

Empty - Use Case

int[][] numbers = 
{
  new int[] { 1, 2, 3 },
  new int[] { 4, 5, 6 },
  null                     // this null makes the query below fail.
};

IEnumerable<int> flat = numbers.SelectMany (innerArray => innerArray);

flat.Dump();          // Throws a NullReferenceException

Empty - Solution

int[][] numbers = 
{
  new int[] { 1, 2, 3 },
  new int[] { 4, 5, 6 },
  null                     // this necessitates the null coalescing operator below
};

IEnumerable<int> flat = numbers
  .SelectMany (innerArray => innerArray ?? Enumerable.Empty <int>() );

flat.Dump();

Range and Repeat

Enumerable.Range (5, 5).Dump ("Range"); 
Enumerable.Repeat (true, 3).Dump ("Repeat");