Chaining Query Operators

In the preceding section, we showed two simple queries, each comprising a single query operator. To build more complex queries, you append additional query operators to the expression, creating a chain. To illustrate, the following query extracts all strings containing the letter “a,” sorts them by length, and then converts the results to uppercase:

using System;
using System.Collections.Generic;
using System.Linq;

class LinqDemo
{
  static void Main()
  {
    string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };

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

    foreach (string name in query) Console.WriteLine (name);
  }
}

OUTPUT:
JAY
MARY
HARRY

void Test()
{
  foreach (char c in "string1") Console.Write (c);
  foreach (char c in "string2") Console.Write (c);
  foreach (char c in "string3") Console.Write (c);
}

Where, OrderBy, and Select are standard query operators that resolve to extension methods in the Enumerable class (if you import the System.Linq namespace).

We already introduced the Where operator, which emits a filtered version of the input sequence. The OrderBy operator emits a sorted version of its input sequence; the Select method emits a sequence in which each input element is transformed or projected with a given lambda expression (n.ToUpper(), in this case). Data flows from left to right through the chain of operators, so the data is first filtered, then sorted, and then projected.

Here are the signatures of each of these extension methods (with the OrderBy signature slightly simplified):

public static IEnumerable<TSource> Where<TSource>
  (this IEnumerable<TSource> source, Func<TSource,bool> predicate)

public static IEnumerable<TSource> OrderBy<TSource,TKey>
  (this IEnumerable<TSource> source, Func<TSource,TKey> keySelector)

public static IEnumerable<TResult> Select<TSource,TResult>
  (this IEnumerable<TSource> source, Func<TSource,TResult> selector)

When query operators are chained as in this example, the output sequence of one operator is the input sequence of the next. The complete query resembles a production line of conveyor belts, as illustrated in Figure 8-1.

Figure 8-1. Chaining query operators

We can construct the identical query progressively, as follows:

// You must import the System.Linq namespace for this to compile:

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());

finalQuery is compositionally identical to the query we constructed previously. Further, each intermediate step also comprises a valid query that we can execute:

foreach (string name in filtered)
  Console.Write (name + "|");        // Harry|Mary|Jay|

Console.WriteLine();
foreach (string name in sorted)
  Console.Write (name + "|");        // Jay|Mary|Harry|

Console.WriteLine();
foreach (string name in finalQuery)
  Console.Write (name + "|");        // JAY|MARY|HARRY|

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

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

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

Composing Lambda Expressions

In previous examples, we fed the following lambda expression to the Where operator:

n => n.Contains ("a")      // Input type = string, return type = bool.

The purpose of the lambda expression depends on the particular query operator. With the Where operator, it indicates whether an element should be included in the output sequence. In the case of the OrderBy operator, the lambda expression maps each element in the input sequence to its sorting key. With the Select operator, the lambda expression determines how each element in the input sequence is transformed before being fed to the output sequence.

The query operator evaluates your lambda expression upon demand, typically once per element in the input sequence. Lambda expressions allow you to feed your own logic into the query operators. This makes the query operators versatile, and simple under the hood. Here’s a complete implementation of Enumerable.Where, exception handling aside:

public static IEnumerable<TSource> Where<TSource>
  (this IEnumerable<TSource> source, Func<TSource,bool> predicate)
{
  foreach (TSource element in source)
    if (predicate (element))
      yield return element;
}

public static IEnumerable<TResult> Select<TSource,TResult>
  (this IEnumerable<TSource> source, Func<TSource,TResult> selector)

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };
IEnumerable<int> query = names.Select (n => n.Length);

foreach (int length in query)
  Console.Write (length + "|");    // 3|4|5|4|3|

public static IEnumerable<TSource> Where<TSource>
  (this IEnumerable<TSource> source, Func<TSource,bool> predicate)

// Slightly simplified:
public static IEnumerable<TSource> OrderBy<TSource,TKey>
  (this IEnumerable<TSource> source, Func<TSource,TKey> keySelector)

string[] names = { "Tom", "Dick", "Harry", "Mary", "Jay" };
IEnumerable<string> sortedByLength, sortedAlphabetically;
sortedByLength       = names.OrderBy (n => n.Length);   // int key
sortedAlphabetically = names.OrderBy (n => n);          // string key

Other Operators

Not all query operators return a sequence. The element operators extract one element from the input sequence; examples are First, Last, and ElementAt:

int[] numbers    = { 10, 9, 8, 7, 6 };
int firstNumber  = numbers.First();                        // 10
int lastNumber   = numbers.Last();                         // 6
int secondNumber = numbers.ElementAt(1);                   // 9
int secondLowest = numbers.OrderBy(n=>n).Skip(1).First();  // 7

Because these operators return a single element, you don’t usually call further query operators on their result unless that element itself is a collection.

The aggregation operators return a scalar value, usually of numeric type:

int count = numbers.Count();          // 5;
int min = numbers.Min();              // 6;

The quantifiers return a bool value:

bool hasTheNumberNine = numbers.Contains (9);          // true
bool hasMoreThanZeroElements = numbers.Any();          // true
bool hasAnOddElement = numbers.Any (n => n % 2 != 0);  // true

Some query operators accept two input sequences. Examples are Concat, which appends one sequence to another, and Union, which does the same but with duplicates removed:

int[] seq1 = { 1, 2, 3 };
int[] seq2 = { 3, 4, 5 };
IEnumerable<int> concat = seq1.Concat (seq2);    //  { 1, 2, 3, 3, 4, 5 }
IEnumerable<int> union  = seq1.Union (seq2);     //  { 1, 2, 3, 4, 5 }

The joining operators also fall into this category. Chapter 9 covers all of the query operators in detail.

Range Variables

The identifier immediately following the from keyword syntax is called the range variable. A range variable refers to the current element in the sequence on which the operation is to be performed.

In our examples, the range variable n appears in every clause in the query. And yet, the variable actually enumerates over a different sequence with each clause:

from    n in names           // n is our range variable
where   n.Contains ("a")     // n = directly from the array
orderby n.Length             // n = subsequent to being filtered
select  n.ToUpper()          // n = subsequent to being sorted

This becomes clear when we examine the compiler’s mechanical translation to fluent syntax:

names.Where   (n => n.Contains ("a"))      // Locally scoped n
     .OrderBy (n => n.Length)              // Locally scoped n
     .Select  (n => n.ToUpper())           // Locally scoped n

As you can see, each instance of n is scoped privately to its own lambda expression.

Query expressions also let you introduce new range variables via the following clauses:

• let

• into

• An additional from clause

• join

We cover these later in this chapter in “Composition Strategies”, as well as in Chapter 9, in “Projecting” and “Joining”.

Query Syntax Versus SQL Syntax

Query expressions look superficially like SQL, yet the two are very different. A LINQ query boils down to a C# expression, and so follows standard C# rules. For example, with LINQ, you cannot use a variable before you declare it. In SQL, you can reference a table alias in the SELECT clause before defining it in a FROM clause.

A subquery in LINQ is just another C# expression and so requires no special syntax. Subqueries in SQL are subject to special rules.

With LINQ, data logically flows from left to right through the query. With SQL, the order is less well structured with regard to data flow.

A LINQ query comprises a conveyor belt or pipeline of operators that accept and emit sequences whose element order can matter. A SQL query comprises a network of clauses that work mostly with unordered sets.

Query Syntax Versus Fluent Syntax

Query and fluent syntax each have advantages.

Query syntax is simpler for queries that involve any of the following:

• A let clause for introducing a new variable alongside the range variable

• SelectMany, Join, or GroupJoin, followed by an outer range variable reference

(We describe the let clause in “Composition Strategies”; we describe SelectMany, Join, and GroupJoin in Chapter 9.)

The middle ground is queries that involve the simple use of Where, OrderBy, and Select. Either syntax works well; the choice here is largely personal.

For queries that comprise a single operator, fluent syntax is shorter and less cluttered.

Finally, there are many operators that have no keyword in query syntax. These require that you use fluent syntax—at least in part. This means any operator outside of the following:

Where, Select, SelectMany
OrderBy, ThenBy, OrderByDescending, ThenByDescending
GroupBy, Join, GroupJoin

Mixed-Syntax Queries

If a query operator has no query-syntax support, you can mix query syntax and fluent syntax. The only restriction is that each query-syntax component must be complete (i.e., start with a from clause and end with a select or group clause).

Assuming this array declaration:

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

the following example counts the number of names containing the letter “a”:

int matches = (from n in names where n.Contains ("a") select n).Count();
// 3

The next query obtains the first name in alphabetical order:

string first = (from n in names orderby n select n).First();   // Dick

The mixed-syntax approach is sometimes beneficial in more complex queries. With these simple examples, however, we could stick to fluent syntax throughout without penalty:

int matches = names.Where (n => n.Contains ("a")).Count();   // 3
string first = names.OrderBy (n => n).First();               // Dick

Where applicable, the remainder of this chapter shows key concepts in both fluent and query syntax.

Reevaluation

Deferred execution has another consequence: a deferred execution query is reevaluated when you reenumerate:

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

IEnumerable<int> query = numbers.Select (n => n * 10);
foreach (int n in query) Console.Write (n + "|");   // 10|20|

numbers.Clear();
foreach (int n in query) Console.Write (n + "|");   // <nothing>

There are a couple of reasons why reevaluation is sometimes disadvantageous:

• Sometimes, you want to “freeze” or cache the results at a certain point in time.

• Some queries are computationally intensive (or rely on querying a remote database), so you don’t want to unnecessarily repeat them.

You can defeat reevaluation by calling a conversion operator such as ToArray or ToList. ToArray copies the output of a query to an array; ToList copies to a generic List<T>:

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

List<int> timesTen = numbers
  .Select (n => n * 10)

  .ToList();                // Executes immediately into a List<int>

numbers.Clear();
Console.WriteLine (timesTen.Count);      // Still 2

Captured Variables

If your query’s lambda expressions capture outer variables, the query will honor the value of those variables at the time the query runs:

int[] numbers = { 1, 2 };

int factor = 10;
IEnumerable<int> query = numbers.Select (n => n * factor);
factor = 20;
foreach (int n in query) Console.Write (n + "|");   // 20|40|

This can be a trap when building up a query within a for loop. For example, suppose that we want to remove all 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');

foreach (char c in query) Console.Write (c);  // Nt wht y mght xpct

Now watch what happens when we refactor this with a for loop:

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

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

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

An IndexOutOfRangeException is thrown upon enumerating the query because, as we saw in Chapter 4 (see “Capturing Outer Variables”), the compiler scopes the iteration variable in the for loop as if it were declared outside the loop. Hence, each closure captures the same variable (i) whose value is 5 when the query is actually enumerated. To solve this, you must assign the loop variable to another variable declared inside the statement block:

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

This forces a fresh local variable to be captured on each loop iteration.

foreach (char vowel in vowels)

  query = query.Where (c => c != vowel);

How Deferred Execution Works

Query operators provide deferred execution by returning decorator sequences.

Unlike a traditional collection class such as an array or linked list, a decorator sequence (in general) has no backing structure of its own to store elements. Instead, it wraps another sequence that you supply at runtime, to which it maintains a permanent dependency. Whenever you request data from a decorator, it in turn must request data from the wrapped input sequence.

Calling Where merely constructs the decorator wrapper sequence, which holds a reference to the input sequence, the lambda expression, and any other arguments supplied. The input sequence is enumerated only when the decorator is enumerated.

Figure 8-3 illustrates the composition of the following query:

IEnumerable<int> lessThanTen = new int[] { 5, 12, 3 }.Where (n => n < 10);

Figure 8-3. Decorator sequence

When you enumerate lessThanTen, you are, in effect, querying the array through the Where decorator.

The good news—should you ever want to write your own query operator—is that implementing a decorator sequence is easy with a C# iterator. Here’s how you can write your own Select method:

public static IEnumerable<TResult> MySelect<TSource,TResult>
  (this IEnumerable<TSource> source, Func<TSource,TResult> selector)
{
  foreach (TSource element in source)
    yield return selector (element);
}

This method is an iterator by virtue of the yield return statement. Functionally, it’s a shortcut for the following:

public static IEnumerable<TResult> MySelect<TSource,TResult>
  (this IEnumerable<TSource> source, Func<TSource,TResult> selector)
{
  return new SelectSequence (source, selector);
}

where SelectSequence is a (compiler-written) class whose enumerator encapsulates the logic in the iterator method.

Hence, when you call an operator such as Select or Where, you’re doing nothing more than instantiating an enumerable class that decorates the input sequence.

Chaining Decorators

Chaining query operators creates a layering of decorators. Consider the following query:

IEnumerable<int> query = new int[] { 5, 12, 3 }.Where   (n => n < 10)
                                               .OrderBy (n => n)
                                               .Select  (n => n * 10);

Each query operator instantiates a new decorator that wraps the previous sequence (rather like a Russian nesting doll). Figure 8-4 illustrates the object model of this query. Note that this object model is fully constructed prior to any enumeration.

Figure 8-4. Layered decorator sequences

When you enumerate query, you’re querying the original array, transformed through a layering or chain of decorators.

Figure 8-5 shows the same object composition in Unified Modeling Language (UML) syntax. Select’s decorator references the OrderBy decorator, which references Where’s decorator, which references the array. A feature of deferred execution is that you build the identical object model if you compose the query progressively:

Figure 8-5. UML decorator composition

IEnumerable<int>
  source    = new int[] { 5, 12, 3 },
  filtered  = source   .Where   (n => n < 10),
  sorted    = filtered .OrderBy (n => n),
  query     = sorted   .Select  (n => n * 10);

How Queries Are Executed

Here are the results of enumerating the preceding query:

foreach (int n in query) Console.WriteLine (n);

OUTPUT:
30
50

Behind the scenes, the foreach calls GetEnumerator on Select’s decorator (the last or outermost operator), which kicks off everything. The result is a chain of enumerators that structurally mirrors the chain of decorator sequences. Figure 8-6 illustrates the flow of execution as enumeration proceeds.

In the first section of this chapter, we depicted a query as a production line of conveyor belts. Extending this analogy, we can say a LINQ query is a lazy production line, where the conveyor belts roll elements only upon demand. Constructing a query constructs a production line—with everything in place—but with nothing rolling. Then, when the consumer requests an element (enumerates over the query), the rightmost conveyor belt activates; this in turn triggers the others to roll—as and when input sequence elements are needed. LINQ follows a demand-driven pull model, rather than a supply-driven push model. This is important—as you’ll see later—in allowing LINQ to scale to querying SQL databases.

Figure 8-6. Execution of a local query

Subqueries and Deferred Execution

An element or aggregation operator such as First or Count in a subquery doesn’t force the outer query into immediate execution—deferred execution still holds for the outer query. This is because subqueries are called indirectly—through a delegate in the case of a local query, or through an expression tree in the case of an interpreted query.

An interesting case arises when you include a subquery within a Select expression. In the case of a local query, you’re actually projecting a sequence of queries—each itself subject to deferred execution. The effect is generally transparent, and it serves to further improve efficiency. We revisit Select subqueries in some detail in Chapter 9.

Object Initializers

So far, all our select clauses have projected scalar element types. With C# object initializers, you can project into more complex types. For example, suppose, as a first step in a query, we want to strip vowels from a list of names while still retaining the original versions alongside, for the benefit of subsequent queries. We can write the following class to assist:

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

We then can project into it with object initializers:

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

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

The result is of type IEnumerable<TempProjectionItem>, which we can subsequently query:

IEnumerable<string> query = from   item in temp
                            where  item.Vowelless.Length > 2
                            select item.Original;
// Dick
// Harry
// Mary

Anonymous Types

Anonymous types allow you to structure your intermediate results without writing special classes. We can eliminate the TempProjectionItem class in our previous example with anonymous types:

var intermediate = from n in names

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

IEnumerable<string> query = from   item in intermediate
                            where  item.Vowelless.Length > 2
                            select item.Original;

This gives the same result as the previous example, but without needing to write a one-off class. The compiler does the job, instead, generating a temporary class with fields that match the structure of our projection. This means, however, that the intermediate query has the following type:

IEnumerable <random-compiler-generated-name>

The only way we can declare a variable of this type is with the var keyword. In this case, var is more than just a clutter reduction device; it’s a necessity.

We can write the entire query more succinctly with the into keyword:

var query = 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;

Query expressions provide a shortcut for writing this kind of query: the let keyword.

The let Keyword

The let keyword introduces a new variable alongside the range variable.

With let, we can write a query extracting strings whose length, excluding vowels, exceeds two characters, as follows:

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

IEnumerable<string> query =
  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.

The compiler resolves a let clause by projecting into a temporary anonymous type that contains both the range variable and the new expression variable. In other words, the compiler translates this query into the preceding example.

let accomplishes two things:

• It projects new elements alongside existing elements.

• It allows an expression to be used repeatedly in a query without being rewritten.

The let approach is particularly advantageous in this example because it allows the select clause to project either the original name (n) or its vowel-removed version (vowelless).

You can have any number of let statements, before or after a where statement (see Figure 8-2). A let statement can reference variables introduced in earlier let statements (subject to the boundaries imposed by an into clause). let reprojects all existing variables transparently.

A let expression need not evaluate to a scalar type: sometimes it’s useful to have it evaluate to a subsequence, for instance.

EF Core Entity Classes

EF Core lets you use any class to represent data, as long as it contains a public property for each column that you want to query.

For instance, we could define the following entity class to query and update a Customers table in the database:

public class Customer
{
  public int ID { get; set; }
  public string Name { get; set; }
}

DbContext

After defining entity classes, the next step is to subclass DbContext. An instance of that class represents your sessions working with the database. Typically, your DbContext subclass will contain one DbSet<T> property for each entity in your model:

public class NutshellContext : DbContext
{
  public DbSet<Customer> Customers { get; set; }
  ... properties for other tables ...

}

A DbContext object does three things:

• It acts as a factory for generating DbSet<> objects that you can query.

• It keeps track of any changes that you make to your entities so that you can write them back (see “Updates”).

• It provides virtual methods that you can override to configure the connection and model.

public class NutshellContext : DbContext
{
  ...
  protected override void OnConfiguring (DbContextOptionsBuilder
                                         optionsBuilder) =>
    optionsBuilder.UseSqlServer
      (@"Server=(local);Database=Nutshell;Trusted_Connection=True");
}

public NutshellContext (DbContextOptions<NutshellContext>
                        options)
  : base(options) { }

protected override void OnConfiguring (
  DbContextOptionsBuilder optionsBuilder)
{
  if (!optionsBuilder.IsConfigured)
  {
    ...
  }
}

protected override void OnModelCreating (ModelBuilder modelBuilder) =>
  modelBuilder.Entity<Customer>()
    .ToTable ("Customer");   // Table is called 'Customer'

public DbSet<Customer> Customers { get; set; }

protected override void OnModelCreating (ModelBuilder
                                        modelBuilder)
{
  foreach (IMutableEntityType entityType in

           modelBuilder.Model.GetEntityTypes())
  {
    modelBuilder.Entity (entityType.Name)
      .ToTable (entityType.ClrType.Name);
  }
}

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

ToTable()

builder
  .Entity<Customer>()
  .ToTable("Customer");

HasColumnName()

builder.Entity<Customer>()
  .Property(c => c.Name)
  .HasColumnName("Full Name");

HasKey(p)

builder.Entity<Customer>()
  .HasKey(c => c.CustomerNr);

IsRequired()

builder.Entity<Customer>()
  .Property(c => c.Name)
  .IsRequired();

HasMaxLength()

builder.Entity<Customer>()
  .Property(c => c.Name)
  .HasMaxLength(60);

HasColumnType()

builder.Entity<Purchase>()
  .Property(p => p.Description)
  .HasColumnType("varchar(80)");

Ignore()

builder.Ignore<Products>();

Ignore()

builder.Entity<Customer>()
  .Ignore(c => c.ChatName);

HasIndex()

// Compound index:
builder.Entity<Purchase>()
  .HasIndex(p =>
     new { p.Date, p.Price });
 
// Unique index on one property
builder
  .Entity<MedicalArticle>()
  .HasIndex(a => a.Topic)
  .IsUnique();

HasOne()

builder.Entity<Purchase>()
  .HasOne(p => p.Customer)
  .WithMany(c => c.Purchases);

HasMany()

builder.Entity<Customer>()
  .HasMany(c => c.Purchases)
  .WithOne(p => p.Customer);

dbContext.Database.EnsureCreated();

Install-Package Microsoft.EntityFrameworkCore.Tools
Add-Migration InitialCreate
Update-Database

using var dbContext = new NutshellContext();
Console.WriteLine (dbContext.Customers.Count());
// Executes "SELECT COUNT(*) FROM [Customer] AS [c]"

using var dbContext = new NutshellContext();
Customer cust = new Customer()
{
  Name = "Sara Wells"
};
dbContext.Customers.Add (cust);
dbContext.SaveChanges();    // Writes changes back to database

using var dbContext = new NutshellContext();
Customer cust = dbContext.Customers
  .Single (c => c.Name == "Sara Wells")

cust.Name = "Dr. Sara Wells";
dbContext.SaveChanges();

Object Tracking

A DbContext instance keeps track of all the entities it instantiates, so it can feed the same ones back to you whenever you request the same rows in a table. In other words, a context in its lifetime will never emit two separate entities that refer to the same row in a table (where a row is identified by primary key). This capability is called object tracking.

To illustrate, suppose the customer whose name is alphabetically first also has the lowest ID. In the following example, a and b will reference the same object:

using var dbContext = new NutshellContext ();

Customer a = dbContext.Customers.OrderBy (c => c.Name).First();
Customer b = dbContext.Customers.OrderBy (c => c.ID).First();

Consider what happens when EF Core encounters the second query. It starts by querying the database—and obtaining a single row. It then reads the primary key of this row and performs a lookup in the context’s entity cache. Seeing a match, it returns the existing object without updating any values. So, if another user had just updated that customer’s Name in the database, the new value would be ignored. This is essential for avoiding unexpected side effects (the Customer object could be in use elsewhere) and also for managing concurrency. If you had altered properties on the Customer object and not yet called SaveChanges, you wouldn’t want your properties automatically overwritten.

To get fresh information from the database, you must either instantiate a new context or call the Reload method, as follows:

dbContext.Entry (myCustomer).Reload();

The best practice is to use a fresh DbContext instance per unit of work so that the need to manually reload an entity is rare.

Change Tracking

When you change a property value in an entity loaded via DbContext, EF Core recognizes the change and updates the database accordingly upon calling SaveChanges. To do that, it creates a snapshot of the state of entities loaded through your DbContext subclass and compares the current state to the original one when SaveChanges is called (or when you manually query change tracking, as you’ll see in a moment). You can enumerate the tracked changes in a DbContext as follows:

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}");
}

When you call SaveChanges, EF Core uses the information in the ChangeTracker to construct SQL statements that will update the database to match the changes in your objects, issuing insert statements to add new rows, update statements to modify data, and delete statements to remove rows that were removed from the object graph in your DbContext subclass. Any TransactionScope is honored; if none is present it wraps all statements in a new transaction.

You can optimize change tracking by implementing INotifyPropertyChanged and, optionally, INotifyPropertyChanging in your entities. The former allows EF Core to avoid the overhead of comparing modified with original entities; the latter allows EF Core to avoid storing the original values altogether. After implementing these interfaces, call the HasChangeTrackingStrategy method on the ModelBuilder when configuring the model in order to activate the optimized change tracking.

Navigation Properties

Navigation properties allow you to do the following:

• Query related tables without having to manually join

• Insert, remove, and update related rows without explicitly updating foreign keys

For example, suppose that a customer can have a number of purchases. We can represent a one-to-many relationship between Customer and Purchase with the following entities:

public class Customer
{
  public int ID { get; set; }
  public string Name { get; set; }

  // Child navigation property, which must be of type ICollection<T>:
  public virtual List<Purchase> Purchases {get;set;} = new List<Purchase>();
}

public class Purchase
{
  public int ID { get; set; }
  public DateTime Date { get; set; }
  public string Description { get; set; }
  public decimal Price { get; set; }
  public int CustomerID? { get; set; }     // Foreign key field

  public Customer Customer { get; set; }   // Parent navigation property
}

EF Core is able to infer from these entities that CustomerID is a foreign key to the Customer table, because the name “CustomerID” follows a popular naming convention. If we were to ask EF Core to create a database from these entities, it would create a foreign key constraint between Purchase.CustomerID and Customer.ID.

modelBuilder.Entity<Purchase>()
  .HasOne (e => e.Customer)
  .WithMany (e => e.Purchases)
  .HasForeignKey (e => e.CustomerID);

With these navigation properties set up, we can write queries such as this:

var customersWithPurchases = Customers.Where (c => c.Purchases.Any());

We cover how to write such queries in detail in Chapter 9.

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

Purchase p1 = new Purchase { Description="Bike",  Price=500 };
Purchase p2 = new Purchase { Description="Tools", Price=100 };

cust.Purchases.Add (p1);
cust.Purchases.Add (p2);

dbContext.SaveChanges();

using var dbContext = new NutshellContext();
var cust = dbContext.Customers.First();
Console.WriteLine (cust.Purchases.Count);    // Always 0

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

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();

dbContext.Entry (cust).Collection (b => b.Purchases).Load();
// cust.Purchases is now populated.

protected override void OnConfiguring (DbContextOptionsBuilder
                                       optionsBuilder)
{
  optionsBuilder
    .UseLazyLoadingProxies()
    ...
}

using var dbContext = new NutshellContext();
var cust = dbContext.Customers.First();
Console.WriteLine (cust.GetType());
// Castle.Proxies.CustomerProxy

Delegates Versus Expression Trees

Recall that:

• Local queries, which use Enumerable operators, take delegates.

• Interpreted queries, which use Queryable operators, take expression trees.

We can see this by comparing the signature of the Where operator in Enumerable and Queryable:

public static IEnumerable<TSource> Where<TSource> (this
  IEnumerable<TSource> source, Func<TSource,bool> predicate)

public static IQueryable<TSource> Where<TSource> (this
  IQueryable<TSource> source, Expression<Func<TSource,bool>> predicate)

When embedded within a query, a lambda expression looks identical whether it binds to Enumerable’s operators or Queryable’s operators:

IEnumerable<Product> q1 = localProducts.Where (p => !p.Discontinued);
IQueryable<Product>  q2 = sqlProducts.Where   (p => !p.Discontinued);

When you assign a lambda expression to an intermediate variable, however, you must be explicit on whether to resolve to a delegate (i.e., Func<>) or an expression tree (i.e., Expression<Func<>>). In the following example, predicate1 and predicate2 are not interchangeable:

Func <Product, bool> predicate1 = p => !p.Discontinued;
IEnumerable<Product> q1 = localProducts.Where (predicate1);

Expression <Func <Product, bool>> predicate2 = p => !p.Discontinued;
IQueryable<Product> q2 = sqlProducts.Where (predicate2);

public class Product
{
  public static Expression<Func<Product, bool>> IsSelling()
  {
    return p => !p.Discontinued && p.LastSale > DateTime.Now.AddDays (-30);
  }
}

void Test()
{
  var dbContext = new NutshellContext();
  Product[] localProducts = dbContext.Products.ToArray();

  IQueryable<Product> sqlQuery =
    dbContext.Products.Where (Product.IsSelling());

  IEnumerable<Product> localQuery =
    localProducts.Where (Product.IsSelling().Compile());
}

IQueryable<Product> FilterSortProducts (IQueryable<Product> input)
{
  return from p in input
         where ...
         orderby ...
         select p;
}

void Test()
{
  var dbContext = new NutshellContext();
  Product[] localProducts = dbContext.Products.ToArray();

  var sqlQuery   = FilterSortProducts (dbContext.Products);
  var localQuery = FilterSortProducts (localProducts.AsQueryable());
  ...
}

Expression Trees

We said previously that an implicit conversion from a lambda expression to Expression<TDelegate> causes the C# compiler to emit code that builds an expression tree. With some programming effort, you can do the same thing manually at runtime—in other words, dynamically build an expression tree from scratch. The result can be cast to an Expression<TDelegate> and used in EF Core queries, or compiled into an ordinary delegate by calling Compile.

The Expression DOM

An expression tree is a miniature code DOM. Each node in the tree is represented by a type in the System.Linq.Expressions namespace. Figure 8-10 illustrates these types.

Figure 8-10. Expression types

The base class for all nodes is the (nongeneric) Expression class. The generic Expression<TDelegate> class actually means typed lambda expression and might have been named LambdaExpression<TDelegate> if it wasn’t for the clumsiness of this:

LambdaExpression<Func<Customer,bool>> f = ...

Expression<T>’s base type is the (nongeneric) LambdaExpression class. LamdbaExpression provides type unification for lambda expression trees: any typed Expression<T> can be cast to a LambdaExpression.

The thing that distinguishes LambdaExpressions from ordinary Expressions is that lambda expressions have parameters.

To create an expression tree, don’t instantiate node types directly; rather, call static methods provided on the Expression class, such as Add, And, Call, Constant, LessThan, and so on.

Figure 8-11 shows the expression tree that the following assignment creates:

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

Figure 8-11. Expression tree

We can demonstrate this as follows:

Console.WriteLine (f.Body.NodeType);                     // LessThan
Console.WriteLine (((BinaryExpression) f.Body).Right);   // 5

Let’s now build this expression from scratch. The principle is that you start from the bottom of the tree and work your way up. The bottommost thing in our tree is a ParameterExpression, the lambda expression parameter called “s” of type string:

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

The next step is to build the MemberExpression and ConstantExpression. In the former case, we need to access the Length property of our parameter, “s”:

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

Next is the LessThan comparison:

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

The final step is to construct the lambda expression, which links an expression Body to a collection of parameters:

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

A convenient way to test our lambda is by compiling it to a delegate:

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

Console.WriteLine (runnable ("kangaroo"));           // False
Console.WriteLine (runnable ("dog"));                // True

Note

The easiest way to determine which expression type to use is to examine an existing lambda expression in the Visual Studio debugger.

We continue this discussion online.