Chapter 8 - LINQ Queries

Simple Filtering

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

IEnumerable<string> filteredNames =
  System.Linq.Enumerable.Where (names, n => n.Length >= 4);
                                    
foreach (string n in filteredNames)
  Console.Write (n + "|");            // Dick|Harry|


// In LINQPad, we can also write query results using Dump:

filteredNames.Dump ("Simple use of 'Where' query operator");

Extension Methods

// "Where" is an extension method in System.Linq.Enumerable:

(new[] {"Tom", "Dick", "Harry"} ).Where (n => n.Length >= 4)
  
// (Notice that the language dropdown above is now 'C# Expression' rather than 'C# Statement').

Basic Query Expression

from n in new[] { "Tom", "Dick", "Harry" }
where n.Contains ("a")
select n

Chaining Query Operators

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

IEnumerable<string> query = names
  .Where   (n => n.Contains ("a"))
  .OrderBy (n => n.Length)
  .Select  (n => n.ToUpper());
  
query.Dump();

// The same query constructed progressively:

IEnumerable<string> filtered   = names.Where      (n => n.Contains ("a"));
IEnumerable<string> sorted     = filtered.OrderBy (n => n.Length);
IEnumerable<string> finalQuery = sorted.Select    (n => n.ToUpper());

filtered.Dump   ("Filtered");
sorted.Dump     ("Sorted");
finalQuery.Dump ("FinalQuery");

Shunning Extension Methods

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

IEnumerable<string> query =
  Enumerable.Select (
    Enumerable.OrderBy (
      Enumerable.Where (
        names, n => n.Contains ("a")
      ), n => n.Length
    ), n => n.ToUpper()
  );
  
query.Dump ("The correct result, but an untidy query!");

Type inference

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

names.Select (n => n.Length).Dump ("Notice result is IEnumerable<Int32>; Int32 is inferred");

IEnumerable<string> sortedByLength, sortedAlphabetically;

names.OrderBy (n => n.Length)  .Dump ("Integer sorting key");
names.OrderBy (n => n)         .Dump ("String sorting key");

Natural Ordering

int[] numbers  = { 10, 9, 8, 7, 6 };

// The natural ordering of numbers is honored, making the following queries possible:

numbers.Take (3)  .Dump ("Take(3) returns the first three numbers in the sequence");
numbers.Skip (3)  .Dump ("Skip(3) returns all but the first three numbers in the sequence");
numbers.Reverse() .Dump ("Reverse does exactly as it says");

Other Operators

int[] numbers = { 10, 9, 8, 7, 6 };

"".Dump ("All of these operators are covered in more detail in Chapter 9.");

// Element operators:

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

numbers.ElementAt (1).Dump ("Second number");
numbers.OrderBy (n => n).First().Dump ("Lowest number");
numbers.OrderBy (n => n).Skip(1).First().Dump ("Second lowest number");

// Aggregation operators:

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

// Quantifiers:

numbers.Contains (9).Dump ("Contains (9)");
numbers.Any().Dump ("Any");
numbers.Any (n => n % 2 != 0).Dump ("Has an odd numbered element");

// Set based operators:

int[] seq1 = { 1, 2, 3 };
int[] seq2 = { 3, 4, 5 };
seq1.Concat (seq2).Dump ("Concat");
seq1.Union (seq2).Dump ("Union");

A Basic Query

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

IEnumerable<string> query =
  from     n in names
  where    n.Contains ("a")   // Filter elements
  orderby  n.Length           // Sort elements
  select   n.ToUpper();       // Translate each element (project)
  
query.Dump();

A Basic Query - Translation

// With AsQueryable() added, you can see the translation to fluent syntax in the λ tab below:

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

IEnumerable<string> query =
  from      n in names
  where     n.Contains ("a")    // Filter elements
  orderby   n.Length            // Sort elements
  select    n.ToUpper();        // Translate each element (project)
  
query.Dump();

Mixing Syntax

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

(from n in names where n.Contains ("a") select n).Count()
  .Dump ("Names containing the letter 'a'");

string first = (from n in names orderby n select n).First()
  .Dump ("First name, alphabetically");

names.Where (n => n.Contains ("a")).Count()
  .Dump ("Original query, entirely in fluent syntax");
  
names.OrderBy (n => n).First()
  .Dump ("Second query, entirely in fluent syntax");

Query Syntax in its Entirety

// The following diagram fully describes query syntax.
//
// http://www.albahari.com/nutshell/linqsyntax.aspx

Introduction

var numbers = new List<int>();
numbers.Add (1);

IEnumerable<int> query = numbers.Select (n => n * 10);    // Build query

numbers.Add (2);                    // Sneak in an extra element

query.Dump ("Notice both elements are returned in the result set");

Reevaluation

var numbers = new List<int>() { 1, 2 };

IEnumerable<int> query = numbers.Select (n => n * 10);

query.Dump ("Both elements are returned");

numbers.Clear();

query.Dump ("All the elements are now gone!");

Defeating Reevaluation

var numbers = new List<int>() { 1, 2 };

List<int> timesTen = numbers
  .Select (n => n * 10) 
  .ToList();                      // Executes immediately into a List<int>

numbers.Clear();
timesTen.Count.Dump ("Still two elements present");

Captured Variables

int[] numbers = { 1, 2 };

int factor = 10;
IEnumerable<int> query = numbers.Select (n => n * factor);

factor = 20;

query.Dump ("Notice both numbers are multiplied by 20, not 10");

Captured Variables in a for-loop

// Suppose we want to build up a query that strips all the vowels from a string.
// The following (although inefficient) gives the correct result:

IEnumerable<char> query = "Not what you might expect";

query = query.Where (c => c != 'a');
query = query.Where (c => c != 'e');
query = query.Where (c => c != 'i');
query = query.Where (c => c != 'o');
query = query.Where (c => c != 'u');

new string (query.ToArray()).Dump ("All vowels are stripped, as you'd expect.");

"Now, let's refactor this. First, with a for-loop:".Dump();

string vowels = "aeiou";

for (int i = 0; i < vowels.Length; i++)
  query = query.Where (c => c != vowels[i]);   // IndexOutOfRangeException

foreach (char c in query) Console.Write (c);

// An IndexOutOfRangeException is thrown! This is because, as we saw in Chapter 4 
// (see "Capturing Outer Variables"), the compiler scopes the iteration variable 
// in the for loop as if it was declared outside the loop. Hence each closure captures
// the same variable (i) whose value is 5 when the query is enumerated.

Solution

// We can make the preceding query work correctly by assigning the loop variable to another
// variable declared inside the statement block:

IEnumerable<char> query = "Not what you might expect";
string vowels = "aeiou";

for (int i = 0; i < vowels.Length; i++)
{
  char vowel = vowels[i];
  query = query.Where (c => c != vowel);
}

foreach (char c in query) Console.Write (c);

Captured Variables and foreach

// Let's now see what happens when you capture the iteration variable of a foreach loop:

IEnumerable<char> query = "Not what you might expect";
string vowels = "aeiou";

foreach (char vowel in vowels)
  query = query.Where (c => c != vowel);

foreach (char c in query) Console.Write (c);

// The output depends on which version of C# you're running! In C# 4.0 and C# 3.0, we
// get the same problem we had with the for-loop: each loop iteration captures the same
// variable, whose final value is 'u'. Hence only the 'u' is stripped. The workaround
// for this is to use a temporary variable (see next example).

// From C# 5.0, they fixed the compiler so that the iteration variable of a foreach loop
// is treated as *local* to each loop iteration. Hence our example strips all vowels
// as expected.

Basic Subquery

string[] musos = { "Roger Waters", "David Gilmour", "Rick Wright", "Nick Mason"  };

musos.OrderBy (m => m.Split().Last())   .Dump ("Sorted by last name");

Reformulating the Subquery

// For more information on subqueries, see Chapter 9, "Projecting"

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

names.Where (n => n.Length == names.OrderBy (n2 => n2.Length)
     .Select (n2 => n2.Length).First())
     .Dump();

var query =
  from   n in names
  where  n.Length == (from n2 in names orderby n2.Length select n2.Length).First()
  select n;

query.Dump ("Same thing as a query expression");

query =
  from   n in names
  where  n.Length == names.OrderBy (n2 => n2.Length).First().Length
  select n;

query.Dump ("Reformulated");

query =
  from   n in names
  where  n.Length == names.Min (n2 => n2.Length)
  select n;

query.Dump ("Same result, using Min aggregation");

Avoiding Subqueries

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

int shortest = names.Min (n => n.Length);
(
  from    n in names
  where   n.Length == shortest
  select  n
)
.Dump ("No subquery");

Progressive Query Building

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

(
  names
  .Select  (n => n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", ""))
  .Where   (n => n.Length > 2)
  .OrderBy (n => n)
)
.Dump ("This query was written in fluent syntax");

(
  from    n in names
  where   n.Length > 2
  orderby n
  select  n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", "")
)
.Dump ("An incorrect translation to query syntax");

IEnumerable<string> query =
  from   n in names
  select n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", "");

query = from n in query where n.Length > 2 orderby n select n;

query.Dump ("A correct translation to query syntax, querying in two steps");

The into Keyword

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

(
  from   n in names
  select n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", "")
  into   noVowel 
  where  noVowel.Length > 2 orderby noVowel select noVowel
)
.Dump ("The preceding query revisited, with the 'into' keyword");

into keyword - Scoping Rules

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

// The following will not compile - "The name 'n1' does not exist in the current context" (try it).

var query =
  from   n1 in names
  select n1.ToUpper()
  into   n2                          // Only n2 is visible from here on.
  where  n1.Contains ("x")           // Illegal: n1 is not in scope.
  select n2;
    
// The equivalent in fluent syntax (you wouldn't expect this to compile!):

var query = names
  .Select (n1 => n1.ToUpper())
  .Where (n2 => n1.Contains ("x"));     // Error: n1 no longer in scope

Wrapping Queries

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

IEnumerable<string> query =
  from n1 in 
  (
    from   n2 in names
    select n2.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", "")
  )
  where n1.Length > 2 orderby n1 select n1;
  
query.Dump ("Here, one query wraps another");

var sameQuery = names
  .Select  (n => n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", ""))
  .Where   (n => n.Length > 2)
  .OrderBy (n => n);
  
sameQuery.Dump ("In fluent syntax, such queries translate to a linear chain of query operators");

Object Initializers

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

  IEnumerable<TempProjectionItem> temp =
    from n in names
    select new TempProjectionItem
    {
      Original  = n,
      Vowelless = n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", "")
    };
  
  temp.Dump();
}

class TempProjectionItem
{
  public string Original;      // Original name
  public string Vowelless;     // Vowel-stripped name
}

Anonymous Types

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

var intermediate = from n in names
select new
{
  Original = n,
  Vowelless = n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", "")
};

(
  from    item in intermediate
  where   item.Vowelless.Length > 2
  select  item.Original
)
.Dump();

// With the into keyword we can do this in one step:

(
  from n in names
  select new
  {
    Original = n,
    Vowelless = n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", "")
  }
  into   temp
  where  temp.Vowelless.Length > 2
  select temp.Original
)
.Dump ("With the 'into' keyword");

The let Keyword

var names = new[] { "Tom", "Dick", "Harry", "Mary", "Jay" }.AsQueryable();
(
  from n in names
  let vowelless = n.Replace ("a", "").Replace ("e", "").Replace ("i", "").Replace ("o", "").Replace ("u", "")
  where vowelless.Length > 2
  orderby vowelless
  select n           // Thanks to let, n is still in scope.
)
.Dump();

More Info

// Refer to Chapter 9 - LINQ Query Operators

Simple EFCore Query

from    c in Customers
where   c.Name.Contains ("a")
orderby c.Name.Length
select  c.Name.ToUpper()

Combining Interpreted and Local Queries

void Main()
{
  // This uses a custom 'Pair' extension method, defined below.
  
  Customers
    .Select (c => c.Name.ToUpper())
    .Pair()                  // Local from this point on.
    .OrderBy (n => n)
    .Dump();  

  // Here's a more substantial example:

  Customers
    .Select (c => c.Name.ToUpper())
    .OrderBy (n => n)
    .Pair()                         // Local from this point on.
    .Select ((n, i) => "Pair " + i.ToString() + " = " + n)
    .Dump();  
}

public static class MyExtensions
{
  public static IEnumerable<string> Pair (this IEnumerable<string> source)
  {
    string firstHalf = null;
    foreach (string element in source)
    if (firstHalf == null)
      firstHalf = element;
    else
    {
      yield return firstHalf + ", " + element;
      firstHalf = null;
    }
  }
}

Regex in database query

Regex wordCounter = new Regex (@"\b(\w|[-'])+\b");

// The following query throws an exception, because Regex has no equivalent in SQL:

var query = MedicalArticles
  .Where (article => article.Topic == "influenza"
      && wordCounter.Matches (article.Abstract).Count < 100);

query.Dump();

AsEnumerable

Regex wordCounter = new Regex (@"\b(\w|[-'])+\b");

// Click the 'SQL' tab below after running this query - notice that only the topic filtering
// predicate executes on SQL Server.

var query = MedicalArticles
  .Where (article => article.Topic == "influenza")
  .AsEnumerable()
  .Where (article => wordCounter.Matches (article.Abstract).Count < 100);

query.Dump();

Introducing DbContext

// In LINQPad we're *inside* the typed DbContext, so don't need to instantiate it:

Customers                          .Dump ("All customers");
Customers.Count()                  .Dump ("Number of rows in customer table");
Customers.Single (c => c.ID == 2)  .Dump ("Customer with ID of 2");

Using DbContext

var dbContext = this;
Console.WriteLine (dbContext.Customers.Count());

// Insert a new customer
Customer cust = new Customer()
{
  ID = 10,
  Name = "Sara Wells"
};

dbContext.Customers.Add (cust);
dbContext.SaveChanges();    // Writes changes back to database

// Query the database for the customer that was just inserted:
using (var anotherContext = new TypedDataContext (this.Database.GetDbConnection().ConnectionString))
  anotherContext.Customers
    .Single (c => c.Name == "Sara Wells")
    .Dump ("Retrieved from database");

// Update the customer's name, and save the changes to the database:
cust.Name = "Dr. Sara Wells";
dbContext.SaveChanges();

// Delete the customer
Customers.Remove (cust);
dbContext.SaveChanges();

// Click the "SQL" tab to see the SQL commands;

Object Tracking

Customer a = Customers.Where (c => c.Name.StartsWith ("T")).First();
Customer b = Customers.OrderBy (c => c.ID).First();

a.Dump();
b.Dump();

(a == b).Dump();

Change Tracking

var dbContext = this;

dbContext.Add (new Customer() { Name = "Dylan" });
ShowChanges (dbContext, "Added to context");
// Added to context
// EfCoreLib.Customer is Added
//     ID: '-2147482643' modified: False
//     Name: 'Dylan' modified: False

dbContext.SaveChanges();
ShowChanges (dbContext, "Added customer was saved.");
// Added customer was saved.
// EfCoreLib.Customer is Unchanged
//     ID: '10' modified: False
//     Name: 'Dylan' modified: False

var dylan = dbContext.Customers.First (c => c.Name == "Dylan");

ShowChanges (dbContext, "Customer loaded");
// Customer loaded
// EfCoreLib.Customer is Unchanged
//     ID: '10' modified: False
//     Name: 'Dylan' modified: False

dylan.Name = "Dylan Modified";
ShowChanges (dbContext, "Modified Name property");
// Modified Name property
// EfCoreLib.Customer is Modified
//     ID: '10' modified: False
//     Name: 'Dylan Modified' modified: True

dbContext.SaveChanges();
dbContext.Customers.Remove (dylan);
ShowChanges (dbContext, "Removed from context");
// Removed from context
// EfCoreLib.Customer is Deleted
//     ID: '10' modified: False
//     Name: 'Dylan Modified' modified: False

dbContext.SaveChanges();
ShowChanges (dbContext, "Saved to DB");
// Saved to DB
// (No changes to show)


void ShowChanges (DbContext dbContext, string title)
{
  Console.WriteLine (title);
  foreach (var e in dbContext.ChangeTracker.Entries())
  {
    Console.WriteLine ($"{e.Entity.GetType().FullName} is {e.State}");
    foreach (var m in e.Members)
      Console.WriteLine (
        $"  {m.Metadata.Name}: '{m.CurrentValue}' modified: {m.IsModified}");
  }
}

Navigation Properties - Querying

Customers.Where (c => c.Purchases.Any())

Navigation Properties - Updating

// Retrieve a customer:
Customer cust = Customers.Single (c => c.ID == 1);

// Create two purchases:
Purchase p1 = new Purchase { ID = 100, Description="Bike",  Price=500, Date = DateTime.Now };
Purchase p2 = new Purchase { ID = 101, Description="Tools", Price=100, Date = DateTime.Now };

// and add them to the customer's Purchases colleciton.
cust.Purchases.Add (p1);
cust.Purchases.Add (p2);

SaveChanges();

Purchases.RemoveRange (p1, p2);
SaveChanges();

Loading Navigation Properties

void Main()
{
  // LINQPad enables lazy loading automatically, so to demonstrate life without
  // lazy loading, we've created our own typed DbContext class below:  
  using var dbContext = new NutshellContext();
  
  var cust = dbContext.Customers.First();
  Console.WriteLine (cust.Purchases?.Count ?? 0);    // Always 0

  cust = dbContext.Customers
    .Include (c => c.Purchases)
    .Where (c => c.ID == 2)
    .First()
    .Dump ("Using Include");

  var custInfo = dbContext.Customers
   .Where (c => c.ID == 2)
   .Select (c => new
   {
     Name = c.Name,
     Purchases = c.Purchases.Select (p => new { p.Description, p.Price })
   })
   .First()
   .Dump ("Using a projection");
   
  // Yet another solution:
  dbContext.Entry (cust).Collection (b => b.Purchases).Load();
  // cust.Purchases is now populated.
}

public class NutshellContext : DbContext
{
  public DbSet<Customer> Customers { get; set; }
  public DbSet<Purchase> Purchases { get; set; }

  protected override void OnConfiguring (DbContextOptionsBuilder optionsBuilder)
  {
    optionsBuilder.UseSqlServer (Util.CurrentCxString);
  }

  protected override void OnModelCreating (ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Customer> (entity =>
     {
       entity.ToTable ("Customer");
       entity.Property (e => e.Name).IsRequired();  // Column is not nullable
     });
    modelBuilder.Entity<Purchase> (entity =>
     {
       entity.ToTable ("Purchase");
       entity.Property (e => e.Date).IsRequired();
       entity.Property (e => e.Description).IsRequired();
     });
  }
}

Extra - Compiled Queries

// EF Core lets you precompile queries so that you pay the cost of translating
// the query from LINQ into SQL only once. In LINQPad the typed DataContext is
// called TypeDataContext, so we proceed as follows:

var cc = EF.CompileQuery ((TypedDataContext dc, decimal minPrice) =>    
  from c in Customers
  where c.Purchases.Any (p => p.Price > minPrice)
  select c
);

cc (this, 100).Dump ("Customers who spend more than $100");
cc (this, 1000).Dump ("Customers who spend more than $1000");

Deferred Execution

var query = from c in Customers
            select
               from p in c.Purchases
               select new { c.Name, p.Price };

foreach (var customerPurchaseResults in query)
  foreach (var namePrice in customerPurchaseResults)
    Console.WriteLine ($"{ namePrice.Name} spent { namePrice.Price}");

var query2 = from c in Customers
             select new { c.Name, c.Purchases };

foreach (var row in query2)
  foreach (Purchase p in row.Purchases)   // No extra round-tripping
    Console.WriteLine (row.Name + " spent " + p.Price);

Deferred Execution - round-tripping

foreach (Customer c in Customers.ToArray())
  foreach (Purchase p in c.Purchases)    // Another SQL round-trip
    Console.WriteLine (c.Name + " spent " + p.Price);

// Take a look at the SQL translation tab below to see all the round-tripping.

Compiling Expression Trees

Products.RemoveRange (Products.Where (p => p.ID == 999));
Products.Add (new Product { ID = 999, Description = "Test", LastSale = DateTime.Now } );
SaveChanges();

Product[] localProducts = Products.ToArray();

Expression<Func<Product, bool>> isSelling = 
  p => !p.Discontinued && p.LastSale > DateTime.Now.AddDays (-30);

IQueryable<Product> sqlQuery = Products.Where (isSelling);
IEnumerable<Product> localQuery = localProducts.Where (isSelling.Compile());

sqlQuery.Dump ("SQL Query");
localQuery.Dump ("Local Query, using same predicate");

AsQueryable

void Main()
{
  FilterSortProducts (Products).Dump ("This query executes on SQL Server");
  
  Product[] localProducts =
  {
    new Product { ID = 1, Description = "Local Product Test", LastSale = new DateTime (2007, 2, 3) }
  };
  
  FilterSortProducts (localProducts.AsQueryable()).Dump ("The same query - executing locally");
}

IQueryable<Product> FilterSortProducts (IQueryable<Product> input)
{
  return 
    from p in input
    where !p.Discontinued && p.LastSale < DateTime.Now.AddDays (-7)
    orderby p.Description
    select p;
}

Examining an Expression Tree

Expression<Func<string, bool>> f = s => s.Length < 5;

f.Body.NodeType.Dump ("Body.NodeType");
(((BinaryExpression) f.Body).Right).Dump ("Body.Right");

f.Dump ("The whole expression tree");

Building an Expression Tree

ParameterExpression p = Expression.Parameter (typeof (string), "s");

MemberExpression stringLength = Expression.Property (p, "Length");
ConstantExpression five = Expression.Constant (5);

BinaryExpression comparison = Expression.LessThan (stringLength, five);

Expression<Func<string, bool>> lambda = Expression.Lambda<Func<string, bool>> (comparison, p);

Func<string, bool> runnable = lambda.Compile();

runnable ("kangaroo")  .Dump ("kangaroo is less than 5 characters");
runnable ("dog")       .Dump ("dog is less than 5 characters");

Extra - Using PredicateBuilder

// Refer to http://www.albahari.com/expressions/ for info on PredicateBuilder.
//
// Note: PredicateBuilder is built into LINQPad. 
//       To enable, press F4 (for query properties) and go to the 'Advanced' tab.

string[] keywords = { "Widget", "Foo", "Bar" };

var predicate = PredicateBuilder.False<Product>();

foreach (string keyword in keywords)
{
  string temp = keyword;
  predicate = predicate.Or (p => p.Description.Contains (temp));
}

var compiledPredicate = predicate.Compile();

Products.Where (compiledPredicate).Dump ("Notice the multiple OR clauses in the SQL pane");

Extra - Dynamic Ordering Sample

// Thanks to Matt Warren, of the Microsoft LINQ to SQL team, for illustrating how this is done.
//
// Suppose you want order a query based on string that you receive at runtime. The string
// indicates a property or field name, such as "Price" or "Description" or "Date".

// For this, you need to dynamically contruct an "OrderBy" MethodCallExpression. This, in turn,
// requires a dynamically constructed LambdaExpression that references the property or field
// upon which to sort. Here's the complete solution:

IQueryable query =            // The original unordered query
  from p in Purchases
  where p.Price > 100
  select p;

string propToOrderBy = "Price";     // Try changing this to "Description" or "Date"

ParameterExpression purchaseParam = Expression.Parameter (typeof (Purchase), "p");
MemberExpression member = Expression.PropertyOrField (purchaseParam, propToOrderBy);
LambdaExpression lambda = Expression.Lambda (member, purchaseParam);

Type[] exprArgTypes = { query.ElementType, lambda.Body.Type };

MethodCallExpression methodCall =
  Expression.Call (typeof (Queryable), "OrderBy", exprArgTypes, query.Expression, lambda);

IQueryable orderedQuery = query.Provider.CreateQuery (methodCall);
orderedQuery.Dump();

Extra - Dynamic Ordering - How it Works

// Let's break down the last example. We started with a simple unordered query (remember
// that the query does not evaluate at this point, thanks to deferred execution):

IQueryable<Purchase> query =            // The original unordered query
  from p in Purchases
  where p.Price > 100
  select p;

// Here's the property or field name upon which we want to order:

string propToOrderBy = "Price";     // Try changing this to "Description" or "Date"

// The aim is to dynamically constuct the following:
// var orderedQuery = query.OrderBy (p => p.Price);

// Starting from the inside out, we start by creating the lambda expression, p => p.Price.
// To dynamically build a LambaExpression, we first create the parameter, in this case, p.
// Our parameter is of type Purchase, and is called "p":

ParameterExpression purchaseParam = Expression.Parameter (typeof (Purchase), "p");
purchaseParam.Dump ("purchaseParam");

// Next, we need to create "p.Price". The static method Expression.PropertyOrField returns
// a MemberExpression that finds a property or field with the given name:

MemberExpression member = Expression.PropertyOrField (purchaseParam, propToOrderBy);
member.Dump ("member");

// With these two things, we build the LambdaExpression:

LambdaExpression lambda = Expression.Lambda (member, purchaseParam);
lambda.Dump ("lambda");
lambda.ToString().Dump ("lambda.ToString");

// We now need to wrap the lambda expression in a MethodCallExpression that
// references the Queryable.OrderBy method. For this, we call the static Expresion.Call
// method, which is overloaded especially to simplify the task of invoking methods
// that accept lambda expressions:

Type[] exprArgTypes = { query.ElementType, lambda.Body.Type };

MethodCallExpression methodCall =
  Expression.Call (
    typeof (Queryable),   // Type defining method we want to call
    "OrderBy",            // Name of method to call
    exprArgTypes,         // Generic argument types
    query.Expression,     // First argument (the query expression body) 
    lambda);              // Second argument (the lambda expression)

methodCall.Dump ("methodCall (notice all the work that Expression.Call does for us)");

// The final step is to create the new query, which calls the expression we've just
// created. For this, we use the Provider property exposed by the IQueryable interface,
// which returns an object upon which we call CreateQuery:

IQueryable orderedQuery = query.Provider.CreateQuery (methodCall);

// (Exactly the same thing happens when you ordinarily call Queryable.OrderBy;
// you can see this by decompiling with ILSpy). 

// Here's the final result: 

orderedQuery.Expression.ToString().Dump ("The final result");