Chapter 8 - LINQ Queries
Getting Started
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
Introducing Fluent Syntax
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");
Introducing Query Expressions
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
Deferred Execution
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.
Subqueries
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");
Composition Strategies
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");
Projection Strategies
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
Interpreted Queries
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();
EF Core
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.
Building Query Expressions
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");